我们知道统计反馈(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)功能
由优化器自己判断自动生成,无法通过手动生成。
Oracle数据库会自动地维护SQL计划指令(SPD)相关信息。
由12c自动重新优化(Automatic Reoptimization)相关功能,生成相关SQL计划指令(SPD)后,会暂时把信息保存在内存的共享池中。
Oracle的后台进程会每隔15分钟把SQL计划指令(SPD)相关信息从共享池中保存到SYSAUX表空间内相关的字典表中。 当然,你也可以通过手动调用DBMSSPD.FLUSHSQLPLANDIRECTIVE函数,实时地把相关信息从共享池中保存到相关的字典表中。
例:
--实时地把相关信息从共享池中保存到相关的字典表中
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
你也可以根据需要,通过手动调用DBMSSPD.DROPSQLPLANDIRECTIVE函数,进行手动的删除相关信息。
关于SQL计划指令(SPD)相关的信息,可以通过DBASQLPLANDIRECTIVES和DBASQLPLANDIR_OBJECTS视图来查看。
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
版权声明:本文为订阅号TeacherWhat原创文章,转载必须注明出处,作者保留一切相关权力!