oracle 12c SQL计划指令

郑全

概述
我们知道统计反馈（SFB）收集的SQL运行时的统计信息会保存在相应的共享游标中，但却不能够持久化，当数据库重启或者被优化的SQL文从内存中Age-out后，保存的信息就会丢失。下一次执行时还要重新进行一遍自动重新优化。
为了缓解这个问题Oracle 12c推出了SQL指令计划（SQL Plan Directives 以后简称SPD）功能，保存为了以后生成最优执行计划的一些指令和附加信息到字典表中，达到持久化的目的。
虽然到目前为止，只有一种SQL计划指令就是动态采样指令（DYNAMIC_SAMPLING），用于指示优化器使用动态统计信息，但相信以后该功能会进一步得到强化。
另外，SQL计划指令（SQL Plan Directives）是基于查询表达式，而不是SQL语句，所以同一个SQL计划指令（SQL Plan Directives），可以被优化器应用多个SQL文中。
※注意：
12c中SQL计划指令（SQL Plan Directives）只能通过12c自动重新优化（Automatic Reoptimization）功能由优化器自己判断自动生成，无法通过手动生成。
1

SQL计划指令（SPD）的保存
Oracle数据库会自动地维护SQL计划指令（SPD）相关信息。

由12c自动重新优化（Automatic Reoptimization）相关功能，生成相关SQL计划指令（SPD）后，会暂时把信息保存在内存的共享池中。
Oracle的后台进程会每隔15分钟把SQL计划指令（SPD）相关信息从共享池中保存到SYSAUX表空间内相关的字典表中。
当然，你也可以通过手动调用DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE函数，实时地把相关信息从共享池中保存到相关的字典表中。
例：
--实时地把相关信息从共享池中保存到相关的字典表中
SQL> EXEC DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE;
PL/SQL procedure successfully completed.
1234
在SYSAUX表空间内相关字典表中的SQL计划指令（SPD）信息，默认情况会保存53个星期，即如果没有被使用过会在53个星期后被删除；
例：
--查看SQL计划指令（SPD）的保存期间
SQL> SELECT DBMS_SPD.GET_PREFS('SPD_RETENTION_WEEKS') FROM dual;
DBMS_SPD.GET_PREFS('SPD_RETENTION_WEEKS')
-----------------------------------------
53
-- ↑默认53个星期
-- 变更保存期间
SQL> exec DBMS_SPD.SET_PREFS('SPD_RETENTION_WEEKS',10)
-- 查看变更后的保存期间
SQL> SELECT DBMS_SPD.GET_PREFS('SPD_RETENTION_WEEKS') FROM dual;
DBMS_SPD.GET_PREFS('SPD_RETENTION_WEEKS')
-----------------------------------------
10
123456789101112131415161718
你也可以根据需要，通过手动调用DBMS_SPD.DROP_SQL_PLAN_DIRECTIVE函数，进行手动的删除相关信息。
关于SQL计划指令（SPD）相关的信息，可以通过DBA_SQL_PLAN_DIRECTIVES和DBA_SQL_PLAN_DIR_OBJECTS视图来查看。

SQL计划指令（SPD）和扩展统计信息（Extended Statistics）
SQL计划指令（SPD）也用于判断扩展统计信息（特别是列组信息）是否缺失。
当由于扩展统计信息（特别是列组信息）缺失，SQL计划指令（SPD）被使用并且能够解决基数不准确预估的问题的情况时，在下一次统计信息收集时（自动或者手动收集），Oracle会在相应的表上自动创建列组信息。
如果表上创建了相关列组信息，以后的解析中将使用该列组信息，而其相应的SQL计划指令（SPD）会被标记为[SUPERSEDED]不再被使用，并且根据SQL计划指令（SPD）的保存期间进行自动删除。
关于相关的使用例子，大家可以参考12c [Database SQL Tuning Guide]在线文档。
Database SQL Tuning Guide
>How the Optimizer Uses SQL Plan Directives: Example
>How the Optimizer Uses Extensions and SQL Plan Directives: Example