Oracle

How to import SQL Plan Directives

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(…)

DBA_SQL_PLAN_DIRECTIVE.LAST_USED

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(…)

List all RMAN backups that are needed to recover

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(…)

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

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(…)

Variations on 1M rows insert (5): using ‘perf’

By Franck Pachot . In previous post I explained that I’ve observed a 2x CPU usage when running my insert test case in 11.2.0.4 vs. 11.2.0.2 I discussed that with @martinberx @OracleSK @TanelPoder @xtner @Ycolin @fritshoogland @lleturgez @LucaCanaliDB on(…)

Variations on 1M rows insert (4): IN MEMORY

By Franck Pachot . Oracle In-Memory option is for reporting. Oracle has chosen an hybrid approach: maintain the In-Memory Column Store in addition of the buffer cache. Dont’ forget our In-Memory event, but for the moment, let’s try(…)

Matching SQL Plan Directives and queries using it

By Franck Pachot . This is another blog post I’m writing while reviewing the presentation I’m doing next week forSOUG and next month for DOAG. You have SQL Plan Directives used by several queries that have same(…)

Matching SQL Plan Directives and extended stats

By Franck Pachot . This year is the year of migration to 12c. Each Oracle version had its CBO feature that make it challenging. The most famous was the bind variable peeking in 9iR2. Cardinality feedback in 11g also(…)

Variations on 1M rows insert (4): APPEND_VALUES

By Franck Pachot . In the first variation I did a bulk insert with the PL/SQL FORALL statement. A comment suggests to add the APPEND_VALUES hint that appeared in 11gR2. APPEND_VALUES hint does the same than APPEND but(…)

Rebuilt index on increasing values after deletes?

By Franck Pachot . Yesterday while giving our Oracle tuning workshop I discussed with the customer about a job they have that rebuilds indexes every Sunday. Except in very rare cases Oracle indexes are maintained so that free(…)