【Oracle優化器】SQL計劃指令（SQL Plan Directives）

2020 年 3 月 26 日
筆記

概述

我們知道統計回饋（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表空間內相關的字典表中。當然，你也可以通過手動調用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）和擴展統計資訊（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

Tags: