继上次聊SQL Plan Directives（SPD）可能会造成过量的动态采样之后，我再小结一些关于SPD的使用方法。
（1）SPD的信息，有MMON每隔15分钟刷进数据字典，如果要在15分钟不到前，查询dba_sql_plan_dir_objects和dba_sql_plan_directives，可以先flush强制刷出，再查询：
|
1 |
exec dbms_spd.FLUSH_SQL_PLAN_DIRECTIVE; |
（2）如何关联sql id和directive id，可以在display_cursor时使用format=>’+METRICS’
|
1 2 3 4 5 6 7 8 9 10 |
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')); |
（3）ALTER_SQL_PLAN_DIRECTIVE可以改变ENABLED和AUTO_DROP的属性。
|
1 2 |
dbms_spd.alter_sql_plan_directive(14130932452940503769,'ENABLED','NO'); exec dbms_spd.alter_sql_plan_directive(14130932452940503769,'AUTO_DROP','NO'); |
（4）SPD默认保留时间是53周。可以如下来看：
|
1 |
SELECT DBMS_SPD.GET_PREFS('SPD_RETENTION_WEEKS') FROM DUAL; |
53周之后，可以使用dbms_spd.drop_sql_plan_directive，带null参数，删除所有已经过期的directives。如果要删除某个固定的directive_id,将具体的directive_id替换null即可。
|
1 |
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类似。