Infrastructure at your Service

Franck Pachot

How to disable a SQL Plan Directive permanently

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.

 

3 Comments

  • Advait Deo says:

    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/ […]

     

Leave a Reply


4 + two =

Franck Pachot
Franck Pachot

Technology Leader