Infrastructure at your Service

Category Archives: Oracle

Franck Pachot

Oracle 12c Adaptive Plan & inflection point

By | Database management, Oracle | One Comment

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 adaptive plan there will only be one join method to be applied. The feature only defers a decision that…

 
Read More
Franck Pachot

Best practice for the sending of an Oracle execution plan

By | Database Administration & Monitoring, Oracle | 5 Comments

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 we have a performance issue, this probably means that the estimation is wrong. I prefer SQL Monitoring when we…

 
Read More
Franck Pachot

The consequences of NOLOGGING in Oracle

By | Database management, Oracle | No Comments

While answering to a question on Oracle forum about NOLOGGING consequences, I provided a test case that deserves a bit more explanation. Nologging operations are good to generate minimal redo on bulk operations (direct-path inserts, index creation/rebuild). But in case we have to restore a backup that was made before the nologging operation, we loose data. And even if we can accept that, we have some manual operations to do. Here is the full testcase….

 
Read More
Franck Pachot

Investigating Oracle lock issues with event 10704

By | Database management, Oracle | 3 Comments

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 check which lock is acquired by a statement? Event 10704 is the solution. I’ll show some examples in order…

 
Read More
Franck Pachot

Oracle 12c In-Memory option: waiting for 12.1.0.2

By | Database management, Oracle | 2 Comments

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 ‘hybrid’ buzzwords. First, it is a redundant storage that is aimed to improve the query performance. But this is…

 
Read More
Franck Pachot

Oracle Partitioned Sequences – a future new feature in 12c?

By | Database management, Oracle | 4 Comments

Disclaimer: I’ll talk about an undocumented feature that appeared in Oracle 12c – undocumented except if you consider that being exposed in DBA_SEQUENCES as the PARTITION_COUNT is a kind of documentation. So, as the syntax is not documented (I got it only by guessing), you should not use it in production: there are probably some reasons why Oracle did not expose an interresting feature like that.  

 
Read More
Franck Pachot

Oracle et Dbvisit Replicate pour migrer sans arrêt de service… et sans stress

By | Hardware & Storage, Oracle | No Comments

Je viens d’assister au Webinar Zero Downtime Migration pour Oracle, présenté par Chris Lawless qui est récemment passé de Product Manager Golden Gate à Product Manager Dbvisit.  Je vais détailler ici un point très important évoqué par Chris Lawless. La migration par réplication n’est pas seulement envisagée pour éviter un arrêt de service. Arrêt nécéssaire lors de la migration proprement dite (qui peut aller de quelques minutes à plusieurs heures en fonction de la taille…

 
Read More
Franck Pachot

Oracle is hanging? Don’t forget hanganalyze and systemstate!

By | Database management, Oracle | 11 Comments

sqlplus / as sysdba oradebug setmypid oradebug unlimit oradebug hanganalyze 3 oradebug dump ashdumpseconds 30 oradebug dump systemstate 266 oradebug tracefile_name Your Oracle database – production DB, of course – is hanging. All users are blocked. You quickly check the obvious suspects (archivelog destination full, system swapping, etc.) but it’s something else. Even you, the Oracle DBA, cannot do anything: any select is hanging. And maybe you’re even not able to connect with a simple…

 
Read More
Franck Pachot

Archivelog deletion policy for Standby Database in Oracle Data Guard

By | Database Administration & Monitoring, Oracle | 41 Comments

Do you use ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY for your Oracle databases in Data Guard? Maybe you also use the Fast Recovery Area as archive log destination. That’s good practice! But did you ever check that it works as expected? What I mean is this: The archived logs that you don’t need are reclaimable by the FRA when space is needed And the archived logs that are required for availability (standby or…

 
Read More
Franck Pachot

Sockets, Cores, Virtual CPU, Logical CPU, Hyper-Threading: What is a CPU nowadays?

By | Application integration & Middleware, Oracle | 3 Comments

Because people know that I really like Oracle AWR reports, they send them to me from time to time. Here is one I have received for Christmas with the following question: ‘Since our AIX server is virtualized, the response times are 4x longer. I feel that we are CPU bound, but cpu usage is mostly idle. Is there something hidden?’ It is a question that is frequently raised with virtualized environments. Here, we will see…

 
Read More