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;