SQL Plan Directive

Disabled directive

dbms_spd.alter_sql_plan_directive(14130932452940503769,'ENABLED','NO');




Retention weeks

exec dbms_spd.drop_sql_plan_directive(null);




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



SELECT TO_CHAR(d.directive_id) dir_id, o.owner, o.object_name,
       o.subobject_name col_name, o.object_type, d.type, d.state, d.reason
FROM   dba_sql_plan_directives d, dba_sql_plan_dir_objects o
WHERE  d.directive_id=o.directive_id
and owner='R3_REPUSER'

ORDER BY 1,2,3,4,5;

DBMS_SPD Package

The DBMS_SPD packages gives some level of control over SQL plan directives.

Basic SQL plan directive management is performed using the following routines.

• FLUSH_SQL_PLAN_DIRECTIVE : Persists the SQL plan directives in the SGA to te SYSAUX tablespace.
• ALTER_SQL_PLAN_DIRECTIVE : Alters the ENABLED and AUTO_DROP attributes of a specified SQL plan directive.
• DROP_SQL_PLAN_DIRECTIVE : Drops the specified SQL plan directive.

The length of time an unused SQL plan directive is retained for is determined by the SPD_RETENTION_WEEKS preference, default 53 weeks. This is displayed and modified using the following routines.

• GET_PREFS : Displays the specified preference. Only SPD_RETENTION_WEEKS is currently supported.
• SET_PREFS : Sets the specified preference. Only SPD_RETENTION_WEEKS is currently supported.

Long term storage and transfer of SQL plan directives is possible using the following routines.

• CREATE_STGTAB_DIRECTIVE : Create a staging table to export the SQL plan directives to.
• PACK_STGTAB_DIRECTIVE : Export the SQL plan directives to a staging table.
• UNPACK_STGTAB_DIRECTIVE : Import SQL plan directives from a staging table.