【Oracle 优化器】SQL计划指令（SQL Plan Directives）

概述

我们知道统计反馈（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）功能由优化器自己判断自动生成，无法通过手动生成。

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.

在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

你也可以根据需要，通过手动调用DBMS_SPD.DROP_SQL_PLAN_DIRECTIVE函数，进行手动的删除相关信息。

关于SQL计划指令（SPD）相关的信息，可以通过DBA_SQL_PLAN_DIRECTIVES和DBA_SQL_PLAN_DIR_OBJECTS视图来查看。