Oracle Database as our consultants live it every day: installation and patching, upgrades, RMAN backup and recovery, Data Guard, RAC, performance tuning, multitenant, licensing, and engineered systems such as ODA and Exadata.
By Franck Pachot . Want to display a specific section from the latest AWR report? I’ll share the script I use to get something like that: SQL> @ LASTAWRSEC.sql ‘Top 10 Foreground Events by Total Wait Time’ ” AWR_REPORT_TEXT ——————————————————————————– Top 10 Foreground Events by Total Wait Time ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Total Wait Wait % DB Wait Event Waits Time (sec) Avg(ms)…
By Franck Pachot . Following a question from Randolf Geist (who can imagine that there is something about parallel query that Randolf didn’t know?), I get back to some notes I’ve taken when 12c was out and I’ve tested them again on 12.1.0.2 – it’s about the ability to enable parallel DML at query level. Test case In 12.1.0.2 I…
By Franck Pachot . Your system statistics seems to be wrong and you want to gather or set more relevant ones. But you don’t want to see all your application execution plans changing between nested loops and hash joins. For object statistics, we can gather statistics in a pending mode, test them in a few sessions, and publish them when…
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) ……
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…
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…
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…
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…
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…
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…
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:…
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…
