By Franck Pachot . 12c has introduced character strings that can can go above 4000 bytes. In the previous versions, in PL/SQL only we were allowed to have VARCHAR2 up to 32k. In SQL the VARCHAR2 datatype was limited to 4000 and CHAR was limited to 2000. That became a bit small especially when lot of applications needed to store…
In this post, I will continue my experiments with Oracle policy managed databases with Grid Infrastructure, and present one of the new features introduced with Grid Infrastructure 12c last June: the cluster configuration policies and policy sets. It allows the administrator to dynamically reconfigure all server pools at a time, with a single command, according to the business workload. To…
By Franck Pachot . I have recently posted on the way I like to extract execution plans. When we have Tuning pack, I suggest to get them with SQL Real-Time Monitoring as an active report. However, Martin Preiss said in a comment that he prefers the text format – easier to search, copy, and paste. And that’s a very good…
By Franck Pachot . At dbi services, we really like Lighty for Oracle, a tool which helps exploring performance statistics whether they originate from AWR (available only in Enterprise Edition with Diagnostic Pack option) or from Statspack (available via Standard Edition). As a matter of fact, we like it so much that we have became Orachrome Partner. If you want…
Policy-managed databases appeared with Oracle 11g R2 but are not very common yet. I never had the opportunity to administer these kind of databases. Since Oracle now presents policy-databases as the default installation mode and as a best practice, I wanted to know more about this feature which fully automatizes the databases repartition within an Oracle cluster. In this post,…
By Franck Pachot . Prior to Oracle 12c, there were two ways to do ‘top-n’ queries: use rownum after sorting rows with “order by” use row_number() over (order by) Top-n queries are usually required for result pagination. The application shows the first 10 rows, then the next 10 on the following page, etc. In these queries, user interactions are often…
By Franck Pachot . Warning: this is only geek stuff about internals on multitenant database dictionary, metadata, and object links. It has nothing to do with the operations that you can do on your database. Don’t try that in production or you can corrupt the whole dictionary. In 12 multitenant database (aka CDB) we know that each pluggable database is…
Chacun d’entre nous à une façon bien différente d’appréhender le rapport AWR (Oracle Automatic Workload Repository). Nous utilisons nos propres processus de lecture et notre propre appoche. Dans ce blog je vous propose de découvrir AWR Formatter, un outil qui permet de concentrer les informations en quelques clics. Il s’agit d’un plug-in (AWR-Format.crx) pour Google Chrome fort sympatique, qui permet…
By Franck Pachot . The Oracle 12c Adaptive Plan feature was already presented by Nicolas Jardot in OOW 2013: Solving customer issues with the 12c Optimizer. I recently had to answer several questions about its behavior at execution time. Maybe the term ‘adaptive’ is misleading. It’s not that a join will stop and restart to another join method. Even with…
By Franck Pachot . You have a query that takes too long and you want help to analyze the execution plan? Then you need to get it with relevant information, and correctly formatted. Autotrace is not a good option as it does not bind the variables in the same way as your application. Explain plan only shows estimations, but if…
By Franck Pachot . Did you ever encounter unexplained Oracle lock issues? They may be coming from unindexed foreign keys (which is worse in 11g). It’s not easy to monitor. Of course you can check Oracle locks from V$LOCKED_OBJECT, but that is a solution only for locks that remain. Some Oracle locks are there only for a short duration. How do you…
By Franck Pachot . Oracle has announced a big feature that should come in the next 12c patch set: the In-Memory option. It will soon be in beta version, so nothing will be published about it until it comes to production. Before that phase, I’m going to explain what we can expect from that feature, besides the ‘In-Memory’,’columnar storage’, and…
