Oracle 11g 新特性：SQL Plan Management (SPM) 执行计划管理

什麼是 SQL Plan Management (SPM)？

SPM 是 Oracle 11g 引入的執行計劃管理機制，旨在解決優化器升級、統計資訊變更、參數調整等因素導致的執行計劃突變（Plan Regression）問題，確保 SQL 效能穩定。

核心組件

1. SQL Plan Baseline (SQL 計劃基準)：儲存已驗證的高效能執行計劃
2. 計劃捕獲：自動或手動將執行計劃載入基準
3. 計劃選擇：優化器優先使用基準計劃，新計劃需驗證通過才採用
4. 計劃演進：新計劃經效能驗證優於基準時，自動或手動納入基準

運作流程

1. SQL 首次執行，優化器產生計劃
2. 若啟用自動捕獲（OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE），計劃自動存入基準
3. 後續執行時，優化器優先尋找匹配的基準計劃
4. 若發現新計劃，標記為非接受，放入計劃歷史
5. 透過 DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE 驗證新計劃效能
6. 驗證通過（優於基準）則標記為接受，納入基準

關鍵參數與視圖

• OPTIMIZER_USE_SQL_PLAN_BASELINES：啟用/停用基準使用（預設 TRUE）
• OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES：啟用/停用自動捕獲（預設 FALSE）
• DBA_SQL_PLAN_BASELINES：檢視所有基準計劃
• DBA_SQL_PLAN_BASELINES：查看計劃歷史與驗證狀態

常用管理操作

手動載入計劃

DECLARE
  pls PLS_INTEGER;
BEGIN
  pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'abc123');
END;

演進驗證計劃

SET SERVEROUTPUT ON
DECLARE
  rpt CLOB;
BEGIN
  rpt := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
    sql_handle => 'SYS_SQL_12345',
    verify     => 'YES',
    commit     => 'YES');
  DBMS_OUTPUT.PUT_LINE(rpt);
END;

刪除基準計劃

EXEC DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => 'SYS_SQL_12345');

適用場景與效益

• 資料庫升級：防止優化器版本變更導致計劃退化
• 統計資訊收集：避免統計資訊波動引發計劃突變
• 參數調整：緩衝優化器參數變更的衝擊
• 應用程式變更：新 SQL 計劃經驗證後再投入使用

SPM 讓 DBA 從「救火式」效能調優轉向「預防式」計劃管理，大幅降低生產環境效能事故風險。