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.