【DBA】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,  --> 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

參考文件

White paper: SQL Plan Management in Oracle Database 11g

How to Use SQL Plan Management (SPM) - Example Usage (Doc ID 456518.1)
Plan Stability Features (Including SPM) Start Point (Doc ID 1359841.1)  
HOW TO LOAD SQL PLANS INTO SPM FROM AWR (Doc ID 789888.1)
Sql Plan Baseline Not always created (Doc ID 788853.1)
Transporting SQL PLAN Baselines from one database to another. (Doc ID 880485.1)