Infrastructure at your Service

Category

Oracle

Franck Pachot

Flame Graph for quick identification of Oracle bug

By | Database management, Oracle | No Comments

By Franck Pachot . Most of my performance stores start with a screenshot of Orachrome Lighty my preferred tool to have a graphical view of the database performance, in Standard and Enterprise Edition without any options: I’m investigating a long running query. That query obeys to the most important rule in Oracle performance: parce once (CPU) – execute many (IO+CPU) … except that the parse time is a bit too long here (2 hours) How to…

Read More
Franck Pachot

An alternative to DBA_EXTENTS optimized for LMT

By | Database management, Oracle | No Comments

By Franck Pachot . This is a script I have for several years, when tablespaces became locally managed. When we want to know to which segment a block (identified by file id, block id) belongs to, the DBA_EXTENTS view can be very long when you have lot of datafiles and lot of segments. This view using the underlying X$ tables and constrained by hints is faster when queried for one FILE_ID/BLOCK_ID. I did that in…

Read More
Franck Pachot

Oracle memory advisors: how relevant ?

By | Database management, Oracle | 2 Comments

By Franck Pachot . Do you look at memory advisors? I usually don’t but I still show them during the performance tuning workshop because there is no easy way to get an idea about memory sizing. This morning, while giving the training, I’ve shown on our workshop environment what the problem is: they are based on statistics cumulated from instance startup, which can cover months of heterogeneous activity, and then the result is probably meaningless….

Read More
Franck Pachot

How to disable a SQL Plan Directive permanently

By | Database management, Oracle | 4 Comments

By Franck Pachot . In 12c you will see a lot of SQL Plan Directives. Some are useful to get better execution plans, but some will trigger too much Adaptive Dynamic Sampling and that can become a big overhead, especially in Standard Edition. Then you don’t want to drop them – or they will reappear. You can disable them, but what will happen after the retention weeks? Let’s test it. Disabled directive A directive has…

Read More
Franck Pachot

Convert a snapshot standby to a new database

By | Database management, Oracle | One Comment

By Franck Pachot . A snapshot standby database is a nice solution when you want a temporary copy of the primary where you can do whatever you want (test a bug fix for example) and then convert it back to physical standby. But you don’t want to stay in that state definitely because you will accumulate archived logs from the changes done to the primary, and flashback logs for the changes made to the snapshot…

Read More
Franck Pachot

12c Dynamic Sampling and Standard Edition

By | Database management, Oracle | 3 Comments

By Franck Pachot . 12c is coming with more dynamic sampling, now called dynamic statistics and using the new Adaptive Dynamic Sampling algorithm. The goal is to have better estimations and better estimations gives better execution plans. However, this new approach will increase parse time because dynamic sampling kicks in more often, reads more blocs, and run more queries. It’s probably not a problem for applications that are well designed, using bind variables to avoid…

Read More
Franck Pachot

How to import SQL Plan Directives

By | Database management, Oracle | No Comments

By Franck Pachot . Today I’ve presented SQL Plan Directives at the SOUG Romandie event. I had a question about the import/export of directives by Data Pump. The idea is that a lot of testing has been done on QA in order to validate the upgrade to 12c. A few directives had bad consequences (see Ludovico Caldara blog post for an example), then directives have been disabled. When going to production, they want to start…

Read More
Franck Pachot

DBA_SQL_PLAN_DIRECTIVE.LAST_USED

By | Database management, Oracle | No Comments

By Franck Pachot . If you have read Matching SQL Plan Directives and queries using it then you know how to use the ‘+metrics’ format of dbms_xplan. 21:49:54 SQL> explain plan for select distinct * from DEMO_TABLE where a=0 and b=0 and c=0 and d=0; Explained. 21:50:01 SQL> select * from table(dbms_xplan.display(format=>’basic +rows +note +metrics’)); PLAN_TABLE_OUTPUT ———————————————————————————- Plan hash value: 3823449216 ————————————————- | Id | Operation | Name | Rows | ————————————————- | 0 |…

Read More
Franck Pachot

List all RMAN backups that are needed to recover

By | Database management, Oracle | 32 Comments

By Franck Pachot . This blog post is something I had in draft and Laurent Schneider blog post reminds me to publish it. With the right RMAN configuration you should not have to managed backup files yourself. The RMAN catalog knows them and RMAN should be able to access them. If you want to keep a backup for a long time, you just tell RMAN to keep it. But sometimes, RMAN is not connected to…

Read More
Franck Pachot

Variations on 1M insert (6): CPU Flame Graph

By | Database management, Oracle | No Comments

By Franck Pachot . If you followed the 1M rows insert variations, you have seen how something simple brought me into an investigation with the latest tools available to see where the time is spent. When the time is CPU time, there is no wait event to get the detail of DB Time. I’ve used perf events in the previous post and I’ll now visualize them with CPU Flame Graph. My goal is to understand why…

Read More