This was first published on https://blog.dbi-services.com/how-to-disable-a-sql-plan-directive-permanently (2015-06-12)
Republishing here for new followers. The content is related to the the versions available at the publication date
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.
A directive has been created which triggers too expensive dynamic sampling. You don’t want that and you have disabled it one year ago with:
SQL> dbms_spd.alter_sql_plan_directive(14130932452940503769,'ENABLED','NO');
and everything is good. You’re happy with that. Here is the directive:
SQL> select directive_id,state,last_used,auto_drop,enabled,extract(notes,'/spd_note/spd_text/text()') spd_text,extract(notes,'/spd_note/internal_state/text()') internal_state from dba_sql_plan_directives where directive_id in(select directive_id from dba_sql_plan_dir_objects where owner='DEMO' ); DIRECTIVE_ID STATE LAST_USED AUTO_DROP ENABLED SPD_TEXT INTERNAL_S -------------------- ---------- --------- --------- ------- -------------------------------- ---------- 14130932452940503769 SUPERSEDED 28-APR-14 YES NO {E(DEMO.DEMO_TABLE)[A, B, C, D]} HAS_STATS
The directive has not been used since April 2014 thanks to the ‘enabled’ set to NO.
If I run a query with a filter on those columns:
SQL> select count(*) Q1 from DEMO_TABLE where a+b=c+d; Q1 -------------------- 10000 23:10:32 SQL> select * from table(dbms_xplan.display_cursor(format=>'basic +note')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------ EXPLAINED SQL STATEMENT: ------------------------ select count(*) Q1 from DEMO_TABLE where a+b=c+d Plan hash value: 1839825454 ----------------------------------------- | Id | Operation | Name | ----------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL| DEMO_TABLE | -----------------------------------------
there is no dynamic sampling that this is exactly what I want.
My retention is the default: 53 weeks. Let’s see what happens after 53 weeks. I can call the ‘auto drop’ job with dbms_spd.drop_sql_plan_directive passing a null instead of a directive_id:
SQL> exec dbms_spd.drop_sql_plan_directive(null); PL/SQL procedure successfully completed.
Then let’s have a few queries on those table columns:
SQL> select count(*) Q2 from DEMO_TABLE where a+b=c+d; Q2 -------------------- 10000 SQL> select count(*) Q3 from DEMO_TABLE where a+b=c+d; Q3 -------------------- 10000
and check the execution plan:
SQL> select * from table(dbms_xplan.display_cursor(format=>'basic +note')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------- EXPLAINED SQL STATEMENT: ------------------------ select count(*) Q3 from DEMO_TABLE where a+b=c+d Plan hash value: 1839825454 ----------------------------------------- | Id | Operation | Name | ----------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL| DEMO_TABLE | ----------------------------------------- Note ----- - dynamic statistics used: dynamic sampling (level=2) - 1 Sql Plan Directive used for this statement
A directive has been used:
SQL> select directive_id,state,last_used,auto_drop,enabled,extract(notes,'/spd_note/spd_text/text()') spd_text,extract(notes,'/spd_note/internal_state/text()') internal_state from dba_sql_plan_directives where directive_id in(select directive_id from dba_sql_plan_dir_objects where owner='DEMO' ); DIRECTIVE_ID STATE LAST_USED AUTO_DROP ENABLED SPD_TEXT INTERNAL_S -------------------- ---------- --------- --------- ------- -------------------------------- ---------- 14130932452940503769 SUPERSEDED 15-MAY-15 YES YES {E(DEMO.DEMO_TABLE)[A, B, C, D]} HAS_STATS
Oh! The directive is back and enabled !
Here are the criteria for auto-drop. SPD are considered to be dropped when AUTO_DROP is YES and either:
Do you see? Nothing about the ENABLE YES/NO there…
If you want to disable a SPD and be sure that it will never reappear then you have to do both of following:
SQL> exec dbms_spd.alter_sql_plan_directive(14130932452940503769,'ENABLED','NO'); SQL> exec dbms_spd.alter_sql_plan_directive(14130932452940503769,'AUTO_DROP','NO');
then because the AUTO DROP is disabled, the directive will never be deleted automatically.
Thanks for the article. But in your case SPD LAST_USAGE was before retention window. Then why didn’t it dropped that SPD ? Is it a bug ?
Regards,
Advait Deo
Hi Advait, The directive last usage was 28-APR-14 when I’ve disabled it. Then you wonder why I had to call the dbms_spd.drop_sql_plan_directive myself. Well I don’t know what is the frequency of that automatic purge job. We can probably trace SQL_Plan_Directive component and look for qosdCleanupSlave occurence. Because retention is in week, there is no need to run it frequently. However, this is only guesses. If you want all the story I didn’t wait 53 weeks to write my blog post, so I’ve updated sys.opt_directive$ after having disabled it in order to simulate the situation… But in my optinion, this is exactly what would have happened after 53 weeks. Thanks for your question. I like when people check details and this is why I publish things as blog posts ; Regards, Franck.
[…] Franck Pachot: http://blog.dbi-services.com/how-to-disable-a-sql-plan-directive-permanently/ […]