Oracle 11g SQL效能的新特性（三）- SQL Plan Management

SQL的執行效率，取決於它的執行計劃是否高效。 最佳化器的演算法是一個平衡，需要收集儘量少的資訊，用盡量快的速度試圖去得到一個最優的執行計劃，這也決定了它不是萬能的。 所以Oracle提供了一些輔助手段來“修復”最佳化器可能產生的錯誤，並不斷改進這些方法。 

Oracle 8： hint
Oracle 8i&9: stored outline
Oracle 10: sql profile
Oracle 11: sql plan manangement

簡介

在Oracle 11g之前，執行計劃一直是作為“執行時”生成的物件存在。雖然oracle提供了一些方法去指導它的生成，但Oracle一直沒有試圖去儲存完整的執行計劃。 從11g開始，執行計劃就可以作為一類資源被儲存下來，允許特定SQL語句只能選擇“已知”的執行計劃。 

同其他方法相比，SPM更加的靈活。如我們所熟知的，一條帶有繫結變數的SQL語句，最好的執行計劃會根據繫結變數的值而不同，11g以前的方法都無法解決這個問題。在11g中，與adaptive cursor sharing配合，SPM允許你同時接受多個執行計劃。執行時，根據不同的變數值，SPM會花費很少的運算從中選擇一條最合適的。 

概念

SQL Plan Management SPM：oracle 11g 中提供的新特性，用來更好地控制執行計劃。 
Plan History： 最佳化器生成的所有執行計劃的總稱
SQL Plan Baseline： Plan History裡那些被標記為“ACCEPTED”的執行計劃的總稱
Plan Evolution： 把一條執行計劃從Plan History裡標記為“ACCEPTED”的過程
SQL Management Base SMB： 字典表裡儲存的執行計劃的總稱，包括Plan History，SQL Plan Baseline和SQL profile。

SPM的特點

o 與profile和outline相比，更加靈活的控制手段
  + 可以有很多的計劃被儲存下來，只有"ENABLED"並且"ACCEPTED"的執行計劃才可以被選擇。 
  + 允許有多個"ACCEPTED"的執行計劃，根據實際情況進行選擇。 
  + 可以用手工或者自動的方式，把執行計劃演化(evolve)為"ACCEPTED"。 還可以控制只讓效能更好的計劃被接受。
  + 允許設定"FIXED"的計劃。這樣其他的計劃將不會被選擇。

o SPM使計劃真正的穩定。 outline的缺點是太過死板，當資料量大幅度變化時無法做出相應的改變。 SQL proifle的缺點是，當資料量變化時，STA(SQL Tuning Advisor)會不可預知地去更改執行計劃。 而SPM則會提供幾個完整的plan供選擇。 

SPM的控制方式

SPM透過幾個標記來實現對執行計劃的控制：

o Enabled (控制活動)
  + YES (活動的，但不一定會被使用)
  + NO (可以理解為被標記刪除)
o Accepted (控制使用)
  + YES (只有 “Enabled” 並且 “Accepted” 的計劃才會被選擇使用)
  + NO (如果是“Enabled” 那麼只有被evolve成“Accepted”才有可能被執行)
o Fixed (控制優先順序)
  + YES (如果是“Enabled”並且“Accepted”，會優先選擇這個計劃，這個計劃會被視為不需要改變的)
  + NO (普通的計劃，無需優先)

另有一個被動的標記：
o Reproduced (有效性)
  + YES (最佳化器可以使用這個計劃)
  + NO (計劃無效，比如索引被刪除)

SPM如何捕捉執行計劃

o 自動捕捉
  1. 首先把OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES設定成TRUE
  2. 從這個時刻開始，所有執行兩次以上的SQL語句會被觀測，執行計劃會進入Plan History。有個別例外的，參見note 788853.1
  3. 生成的第一個執行計劃被標記為ENABLED並且是ACCEPTED，後續的執行計劃會被標記為ENABLED但不是ACCEPTED。
  4. 這時把OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES設定會FALSE，新的語句將不會建立Baseline。
  5. 需要注意的是，即使關閉了自動捕捉，針對存在baseline的SQL，由於ACS的作用，仍舊會有新的PLAN生成，新的Plan仍會進入Plan History，標記為ENABLED但不是ACCEPTED。參見“執行計劃的選擇”。 

o 批次匯入 (這些匯入的baseline都會被自動標記為ACCEPTED) 
   Oralce提供四種方式把計劃匯入到sql plan baseline中。 
   + 從 SQL Tuning Set STS 匯入
      DBMS_SPM.LOAD_PLANS_FROM_SQLSET
   + 從Stored Outlines 中匯入 
      DBMS_SPM.MIGRATE_STORED_OUTLINE
   + 從記憶體中存在的計劃中匯入
      DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE;
   + 透過staging table從另外一個系統中移植
      DBMS_SPM.CREATE_STGTAB_BASELINE
      DBMS_SPM.PACK_STGTAB_BASELINE
      DBMS_SPM.UNPACK_STGTAB_BASELINE

執行計劃的選擇過程

在OPTIMIZER_USE_SQL_PLAN_BASELINES被設定成預設值TRUE，SQl Plan Baseline就會起作用。 

1. 首先，無論是否存在baseline，oracle都會正常進行硬解析或者軟解析，為SQL生成一個執行計劃。 由於ACS和bind peeking的作用，存在baseline的SQL有可能在這時生成一個不同於baseline的執行計劃。
2. 如果baseline不存在，就按生成的計劃執行。如果baseline存在，那麼要檢視history裡是否有這個計劃，如果沒有，就將這個計劃插入，並標記為ENABLED,NON-ACCEPTED. 
3. 在baseline中檢視是否有FIXED的計劃存在，如果存在，執行FIXED的計劃，如果存在多個FIXED的計劃，根據統計資訊重新計算cost，選擇cost小的那個。
4. 如果FIXED的計劃不存在，就選擇ACCEPTED的計劃執行。 如果存在多個ACCEPTED的計劃，根據統計資訊重新計算cost，選擇cost小的那個。

* 注意這裡每次重新計算cost的代價不大，因為執行計劃是已知的，最佳化器不必遍歷所有的可能，只需根據演算法計算出已知計劃的cost便可

執行計劃的演化(evolution)

執行計劃的演化指Plan History裡的執行計劃從NON-ACCEPTED，變成ACCEPTED的過程。 如果上所述，由於ACS和Bind Peeking的作用，存在baseline的SQL有可能生成新的執行計劃，被儲存到Plan History中。 Oracle提供了API，透過自動或手工的方式，將一個計劃標記為ACCEPTED，這個計劃就會被後續的執行所選擇。 

使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE這個API來控制執行計劃的演化。語法：
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (
  sql_handle IN VARCHAR2 := NULL, --&gt NULL 表示針對所有SQL
  plan_name  IN VARCHAR2 := NULL,
  time_limit IN INTEGER  := DBMS_SPM.AUTO_LIMIT,
  verify     IN VARCHAR2 := 'YES',
  commit     IN VARCHAR2 := 'YES' )
RETURN CLOB;

這裡由兩個標記控制：
o Verify 
  + YES (只有效能更好的計劃才會被演化)
  + NO (演化所有的計劃)
o Commit
  + YES (直接演化)
  + NO (只生成報告)

這裡可以透過不同的排列組合，達到不同的效果：
o 自動接收所有效能更好的執行計劃 (Verify->YES, Commit->YES)
o 自動接收所有新的執行計劃 (Verify->NO, Commit->YES)
o 比較效能，生成報告，人工確認是否演化 (Verify->NO, Commit->NO)

* 對於效能的驗證的方式，oracle會去實際執行來比較buffer gets

修改已有的Baseline

透過DBMS_SPM.ALTER_SQL_PLAN_BASELINE來完成。 

DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
  sql_handle      IN VARCHAR2 := NULL,
  plan_name       IN VARCHAR2 := NULL,
  attribute_name  IN VARCHAR2,
  attribute_value IN VARCHAR2 )
RETURN PLS_INTEGER;

比如，把某個baseline 標記為FIXED，更多屬性請參見官方文件。

SET SERVEROUT ON;
DECLARE
  x NUMBER;
BEGIN
  x := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
    sql_handle      => '&&sql_handle',
    plan_name       => '&&plan_name',
    attribute_name  => 'FIXED',
    attribute_value => 'YES' );
END;
/

常見應用

o 我們常見的一個場景是，一條SQL在使用hint時會生成一個好的計劃，我們需要以此在原SQL上建立一個baseline。 具體方法請參加note 787692.1

注意

o 當您使用多種方式控制執行計劃時：
  + Stored Outline存在時，它具有最高的優先順序。
  + 已經實施的SQL profile會被自動加入到SQL plan baseline中
  + STA(SQL Tuning Advisor) 會自動接收新的profile，意味著它會生成新的baseline
o 如果可能話，儘量移植到SPM，混合多種方式會變得複雜

相關引數

optimizer_capture_sql_plan_baselines
optimizer_use_sql_plan_baselines
create_stored_outline
use_stored_outlines