All posts by Franck Pachot
12c SQL Plan Directives, a side effect, a bad application design, a bug… and the occasion to show how to quickly troubleshoot. An application has long response time since 12c migration. No blind guesses, no reason to compare with previous version, let’s just troubleshoot performance with methodical approach.
Yesterday I was giving my ‘Interpreting AWR Reports’ Prezi at Oracle Midlands. There is a point where I explain that I always check the ‘Captured SQL account for …% of Total DB Time (s)’ in order to know whether I’ll get all details in the report or not. There are two main reasons why the most important statements are not captured: report covering a time window too short, or lot of non shareable SQL statements…
Do you know the optimizer_features_enable parameter? What do you think about it? Good or bad to user it? If I tell you to set optimizer_features_enable parameter=22.214.171.124 when you upgrade to 12c, do you think it’s a very safe decision, or totally insane to upgrade and set behavior to previous version? It’s not an underscore parameter, you are allowed to use it.
Here is a query I use when I’m on a system that has Diagnostic Pack (ASH) but no tuning Pack (SQL Monitor). It displays the execution plan with dbms_xplan.display_cursor and adds the % of ASH samples in front on each plan operation. Here is a small output example. Usual dbms_xplan output but showing the most active operation:
You’ve started a long DataPump and see it stuck on TABLE/STATISTICS/TABLE_STATISTICS, but you don’t expect that step to take a long time. Let’s see if we can rely on that message.
When presenting ‘Interpreting AWR Reports – Straight to the Goal’ at UKOUG TECH15 I had a very good question about the Statspack report I read which had log file sync much smaller than user commits. I realized that this needs a longer explanation, and that my slide is very misleading because I divided log file sync wait time per user commits, which probably make no sense here.
FLASHBACK TABLE restores the latest version that is available in recycle bin. If you did multiple drop / create you may want to restore oldest versions. Of course it’s documented – everything is in the doc. But an example may be useful to understand it before you need it.
In the previous post I’ve explained how to use ‘snapshot copy’ PDB creation for thin provisioning and quick restore of initial state for continuous integration tests. If you don’t have the multitenant option, you need a remote CDB to do it. Here is a way to do the same on only one single-tenant CDB.
How do you manage your continuous integration tests on the database? You need to restart at the same state for probably a hundred of tests. Recreating the schema and test data takes too long. Oracle is not optimized for DDL. Restoring the database takes too long. Even if you data is small, there is those SYSTEM, SYSAUX tablespaces. Flashback database can help, but it still takes time because it requires to restart the instance. Let’s…