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 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.
Retention weeks
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.
Run a few queries
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 !
Auto Drop
Here are the criteria for auto-drop. SPD are considered to be dropped when AUTO_DROP is YES and either:
- SPD is flagged as redundant
- One of the tables has been dropped (in recycle_bin means dropped)
- LAST_USAGE is from before the retention window
- State is NEW (LAST_USED is null) and CREATED is before retention window
Do you see? Nothing about the ENABLE YES/NO there…
Conclusion
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/ […]
how about
Script to Bulk Drop SQL Plan Directives (Doc ID 2323144.1)
dbms_spd.drop_sql_plan_directive(dir_id);