SQL Plan Directives的一些相关知识点

继上次聊SQL Plan Directives（SPD）可能会造成过量的动态采样之后，我再小结一些关于SPD的使用方法。

（1）SPD的信息，有MMON每隔15分钟刷进数据字典，如果要在15分钟不到前，查询dba_sql_plan_dir_objects和dba_sql_plan_directives，可以先flush强制刷出，再查询：

exec dbms_spd.FLUSH_SQL_PLAN_DIRECTIVE;

（2）如何关联sql id和directive id，可以在display_cursor时使用format=>’+METRICS’

conn test/test
explain plan for
select
 cust_id, channel_id, product
  from big_table
 where product = 'Nokia'
   and channel_id = 1
 order by product;
 
select* from table(dbms_xplan.display(null,null,'+METRICS'));
sqlid_dirid

（3）ALTER_SQL_PLAN_DIRECTIVE可以改变ENABLED和AUTO_DROP的属性。

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

（4）SPD默认保留时间是53周。可以如下来看：

SELECT DBMS_SPD.GET_PREFS('SPD_RETENTION_WEEKS') FROM DUAL;

53周之后，可以使用dbms_spd.drop_sql_plan_directive，带null参数，删除所有已经过期的directives。如果要删除某个固定的directive_id,将具体的directive_id替换null即可。

exec dbms_spd.drop_sql_plan_directive(null);

注，auto drop为YES的话，是不理会enable是YES还是NO。
所以可能会出现：enable是设置成NO，一段时间内是不走SPD，不适用动态采样。但是等53周过了之后，即使enable是no，但是auto drop是yes，在清理时被清理掉了。那么，悲剧又来了，被drop掉之后，SPD重新生成，且生成的是enable为YES的。
所以如果要彻底禁用某个SPD，请将enable设置为no的同时，再将auto drop设置为no。

（5）SPD也可以打包传输，见CREATE_STGTAB_DIRECTIVE，PACK_STGTAB_DIRECTIVE和UNPACK_STGTAB_DIRECTIVE。用法和sqlset类似。