oracle11g中SQL最佳化（SQL TUNING）新特性之SQL Plan Management(SPM)

1.   簡介

Oracle Database11gR1引進了SQL PlanManagement（簡稱SPM），一套允許DBA捕獲和保持任意SQL語句執行計劃最優的新工具，這樣，限制了重新整理最佳化器統計資料，已有應用改變，甚至資料庫版本升級帶來的影響。本文幫助對SPM原理基本瞭解，並對其效能最佳化能力進行簡要的說明。

2.   SPM原理和機制

Oracle 11g透過一個簡單而優雅的方法實施瞭解決SQL計劃意外惡化的一套稱為SQL Plan Management(SPM)的新特點。只要使用者會話開啟了自動SQL Plan Baseline捕獲，CBO就會在SQL Management Base(SMB)內記錄該會話內執行的任何SQL，把SQL語句文字，梗概（Outline）,繫結變數，及其編譯環境等儲存為一個SQL Plan Baseline。

由於這是語句第一次執行，Oracle11g會把當時的執行計劃當成最優的。正是在相同SQL語句第二次執行時，SPM的優雅才體現的更加明顯。在語句的第二次執行期間，CBO會比較語句的執行計劃和儲存在SMB中的計劃，新計劃被評估看它是否比SMB中的計劃更高效。

如果新計劃會改善語句的效能，那麼，SPM會把新計劃標記為該語句最好的計劃。只要DBA沒修改OPTIMIZER_USE_SQL_PLAN_BASELINES引數的預設設定（true）,那麼，CBO就會在當前的語句執行中採用新的計劃。否則，如果新計劃降低了語句的效能，那麼，CBO會從SMB中所有可接受計劃中選擇一個成本最低的計劃，並且，SPM會把那個新計劃儲存到SMB中，因為在不久的未來，該新計劃也許成為不錯的選擇。

2.1.  捕獲SQL Plan Basebline

Oracle11g中，捕獲SQLPlan Baseline並儲存到SMB中是非常容易的事情。首先，OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES引數控制SQL Plan Baselines自動捕獲是否開啟，該引數的預設設定為FALSE，這意味著SQL Plan Baselines預設不會被自動捕獲。然而，DBA在會話或系統級將它設定為TRUE，SPM就開始記錄SQL語句的執行，當一條SQL語句被執行多於一次時，該SQL語句就被認為SQL Plan Baselines捕獲的候選。 

其次，Oracle11g有個新的包DBMS_SPM，透過從以下幾個來源手工“種植”計劃,可以預先捕獲和引進大量SQL語句：

? 資料庫Library Cache中一個或多個SQL語句能被用來建立SQL PlanBaselines。過程LOAD_PLANS_FROM_CURSOR_CACHE能被用來在Library Cache中捕獲任何語句的子集作為潛在SMB的候選。 

? 儲存於SQL Tuning Set或一個AWR快照中的SQL語句能被過程LOAD_PLANS_FROM_SQLSET捕獲和被轉換進SQL Plan Baselines。

? 最後，SQL Plan Baselines能來自一個匯入儲存表。這意味著可以從不同的資料庫捕獲語句。

3.  檢視SQL Plan Baseline資訊

被捕獲和儲存在SMB中的SQL Plan Baseline後設資料包含SPM和CBO用來控制計劃的屬性。當新計劃進入SMB時，它被標為ENABLED,但還不能標記ACCEPTED，直到：

1)   CBO已經評估了該計劃並判斷它為最好的計劃；

2)   計劃已被演化為ACCEPTED模式。在CBO考慮採用一個計劃前，該計劃必須被標記為ENABLED和ACCEPTED。

檢視這些後設資料最簡單的方法就是查詢DBA_SQL_PLAN_BASELINES字典檢視。下面是一個控制執行計劃的最有價值資訊的總結： 

Table 1.1. SQL Plan Baseline Plan Control Metadata
Attribute	Description
SQL_HANDLE	A unique SQL identifier in string form; it can be used as a search key
PLAN_NAME	A unique SQL plan identifier in string form; it can be used as a search key
SQL_TEXT	The SQL statement’s unnormalized, actual text
ORIGIN	Tells if the SQL Plan was either: AUTO-CAPTURE: Automatically captured MANUAL-LOAD: Manually evolved MANUAL-SQLTUNE: Automatically evolved by SQL Tuning Advisor AUTO-SQLTUNE: Automatically evolved by Automatic SQL Tuning
ENABLED	Indicates that the SQL Plan is enabled (YES) for CBO utilization or not (NO). Disabled plans are ignored by the CBO
ACCEPTED	Indicates that the SQL Plan is validated as a good plan, either because Oracle 11g has: Automatically accepted it, or The DBA has forced its manual acceptance by changing its status to ACCEPTED via procedure DBMS_SPM.ALTER_SQL_PLAN_BASELINE()
FIXED	SQL Plans whose FIXED attribute is set to YES will be considered by the CBO. If multiple plans are marked as FIXED, the CBO will only select the best execution plan from those so marked
OPTIMIZER_COST	The total cost estimated by the CBO to execute the SQL statement using this execution plan

檢視已存在SQL Plan Baselines中，對一條SQL語句執行有潛在影響的另一個方法是透過DBMS_XPLAN的新過程DISPLAY_SQL_PLAN_BASELINE。例如：能用這個過程來檢視SMB中和SQL語控制程式碼匹配的所有SQLPlan Baselines；如果提供了SQL語句的計劃名，也可以顯示該語句的執行計劃等。

4.   自動捕獲的實現和過程

下面，我們分析自動捕獲SQL Plan Baselines的過程。首先，我們設定OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES引數為TRUE（預設為FALSE）以開啟SQL Plan Baselines自動捕獲；我們還需要把OPTIMIZER_USE_SQL_PLAN_BASELINES引數設定為TRUE (預設值)。該引數控制CBO是否檢查SQL語句重複執行產生的計劃是否將被評估為一個好的計劃。

接著，我們執行同樣的一個SQL兩次。第一次執行時，SQL語句被記錄，第二次執行時，計劃自動被捕獲進SMB並被標記為該語句ACCEPTED的SQLPlan Baseline。

當今後該SQL語句再次被執行，併產生了一個不同的新的計劃時，該計劃也會被自動捕獲進SMB，但並不被標為ACCEPTED，所以，SPM只會把第一個計劃標記為ENABLED和ACCEPTED。

5.  SQL Plan Baseline的演化

SPB中未被標示為ACCEPTED的SQL Plan Baselines，需要進一步被演化為標示ACCEPTED狀態，才可以被今後再次執行的SQL語句採用，對SPB進行演化的方法，主要有如下兩種：

5.1.  手工方法

? 呼叫 dbms_spm包的evolve_sql_plan_baseline()函式

SQL> var report clob;

SQL> exec :report := dbms_spm.evolve_sql_plan_baseline();

SQL> print :report

SQL> select sql_text, plan_name, enabled, accepted fromdba_sql_plan_baselines;

? 呼叫SQL Tuning Advisor工具包

SQL> var tname varchar2(30);

SQL> exec :tname :=dbms_sqltune.create_tuning_task(sql_id => 'bfbr3zrg9d5cc');

SQL> execdbms_sqltune.execute_tuning_task(task_name => :tname);

SQL> selectdbms_sqltune.report_tuning_task(:tname, 'TEXT', 'BASIC') FROM dual;

SQL> exec dbms_sqltune.accept_sql_profile(task_name=> :tname);

SQL> select sql_text, plan_name, enabled,accepted from dba_sql_plan_baselines;

5.2.  自動方法

?  定期排程dbms_spm包的evolve_sql_plan_baseline()

?  配置SQL TUNING ADVISOR，使其在自動任務視窗自動執行

6.   具體操作命令

?  開啟自動捕獲和採用SPM

ALTER SESSION SET optimizer_capture_sql_plan_baselines=TRUE;

ALTER SESSION SET optimizer_use_sql_plan_baselines=TRUE;

?  檢視SPM後設資料

COL creator         FORMAT A08      HEADING 'Creator'

COL hndle           FORMAT A08      HEADING 'SQL|Handle'

COL plnme           FORMAT A08      HEADING 'Plan|Name'

COL sql_hdr         FORMAT A25      HEADING 'SQL Text' WRAP

COL origin          FORMAT A12      HEADING 'Origin'

COL optimizer_cost  FORMAT 9999999  HEADING 'CBO|Cost'

COL enabled         FORMAT A04      HEADING 'Ena-|bled'

COL accepted        FORMAT A04      HEADING 'Acpt'

COL fixed           FORMAT A04      HEADING 'Fixd'

COL autopurge       FORMAT A04      HEADING 'Auto|Purg'

COL create_dt       FORMAT A11      HEADING 'Created|On' WRAP

COL lst_exc_dt      FORMAT A11      HEADING 'Last|Executed' WRAP

SELECT

     creator

    ,SUBSTR(sql_handle, -8, 8) hndle

    ,SUBSTR(plan_name, -8, 8)  plnme

    ,SUBSTR(sql_text, 1, 75) sql_hdr

    ,origin

    ,optimizer_cost

    ,enabled

    ,accepted

    ,fixed

    ,autopurge

    ,TO_CHAR(created, 'yyyy-mm-dd hh24:mi:ss') create_dt

    ,TO_CHAR(last_executed, 'yyyy-mm-dd hh24:mi:ss') lst_exc_dt

  FROM dba_sql_plan_baselines

  WHERE (sql_text LIKE '%SPM%')

 ORDER BY 1,2,3;

?  透過DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE顯示已保留的包含特定文字的SQL Plan Baselines

SET LINESIZE 150

SET PAGESIZE 2000

SELECT PT.*

   FROM (SELECT

             DISTINCT sql_handle

           FROM dba_sql_plan_baselines

          WHERE sql_text like '%SPM%') SPB,

        TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(SPB.sql_handle, NULL,'TYPICAL +NOTE')) PT;