Oracle SQL 計畫管理（SPM）深度解析：穩定執行計畫、防止效能倒退的核心機制

痛點：執行計畫不穩定導致效能災難

資料庫管理員最怕的情況：

• 「這支 SQL 昨天跑 0.5 秒，今天怎麼跑 2 小時？」
• 「我只是收集了統計資訊 / 調整了參數，怎麼反而變慢了？」
• 「資料分布改變了（如某州客戶暴增），優化器還用舊的索引掃描」

傳統手段（OUTLINE、SQL PROFILE、HINT 強制）皆有侷限：維護困難、版本升級失效、無法自適應資料變化。

SQL Plan Management (SPM) 核心價值

Oracle 11g 引入 SPM，提供 「驗證後才採用」 的演進機制：

1. 捕捉：自動/手動記錄 SQL 的執行計畫
2. 演進：新計畫不直接採用，先存入「計畫歷史」
3. 驗證：背景比較新舊計畫效能（可設定容忍閾值）
4. 採用：新計畫優於舊計畫才升級為「已接受」

SPM 三大核心元件

完整生命週期操作指南

階段 1：建立 Baseline（捕捉）

方法 A：自動捕捉（參數控制）

ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = TRUE SCOPE=BOTH;

風險：可能捕捉大量非核心 SQL，佔用 SYSAUX 空間。建議改用手動/載入方式。

方法 B：從 Cursor Cache 載入（推薦）

-- 載入特定 SQL_ID 的計畫
DECLARE
  v_count PLS_INTEGER;
BEGIN
  v_count := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => 'abc123def456',
    fixed  => 'NO'  -- YES=固定不演進，NO=允許演進
  );
  DBMS_OUTPUT.PUT_LINE('Loaded ' || v_count || ' plans');
END;
/

方法 C：從 AWR/STS 載入（歷史優良計畫）

-- 從 AWR 載入
DECLARE
  v_count PLS_INTEGER;
BEGIN
  v_count := DBMS_SPM.LOAD_PLANS_FROM_AWR(
    begin_snap => 1000,
    end_snap   => 1010,
    basic_filter => 'sql_text LIKE ''%critical_table%'''
  );
  DBMS_OUTPUT.PUT_LINE('Loaded ' || v_count || ' plans');
END;
/

方法 D：從 SQL Tuning Set (STS) 載入

-- 建立 STS 並載入
EXEC DBMS_SQLTUNE.CREATE_SQLSET('MY_STS');
-- ... 將 SQL 加入 STS ...
DECLARE
  v_count PLS_INTEGER;
BEGIN
  v_count := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name => 'MY_STS',
    basic_filter => 'parsing_schema_name = ''SCOTT'''
  );
END;
/

階段 2：檢視與管理 Baseline

-- 查看所有 Baseline
SELECT sql_handle, plan_name, enabled, accepted, fixed, reproduced, 
       elapsed_time/1e6 AS avg_elapsed_sec, executions
FROM dba_sql_plan_baselines
ORDER BY last_modified DESC;

-- 查看特定 SQL 的計畫細節
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
  sql_handle => 'SYS_SQL_abc123', format => 'ADVANCED'));

階段 3：演進驗證

手動觸發演進：

SET SERVEROUTPUT ON
DECLARE
  v_report CLOB;
BEGIN
  v_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
    sql_handle => 'SYS_SQL_abc123',
    time_limit => 300,  -- 秒
    verify     => 'YES' -- YES=執行比較，NO=僅檢查可行性
  );
  DBMS_OUTPUT.PUT_LINE(v_report);
END;
/

自動演進**：維護視窗（預設每天 22:00-06:00）自動執行 SYS_AUTO_SPM_EVOLVE_TASK。

-- 檢查自動任務狀態
SELECT client_name, status, window_group FROM dba_autotask_client
WHERE client_name = 'auto spatial optimizer'; -- 11g
-- 12c+ 改為
SELECT client_name, status FROM dba_autotask_client WHERE client_name LIKE '%spm%';

階段 4：固定/停用/移除

-- 固定計畫（不再演進，強制使用）
EXEC DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
  sql_handle => 'SYS_SQL_abc123',
  plan_name  => 'SYS_SQL_PLAN_xyz789',
  attribute_name => 'FIXED', attribute_value => 'YES');

-- 停用計畫
EXEC DBMS_SPM.ALTER_SQL_PLAN_BASELINE(..., 'ENABLED', 'NO');

-- 移除計畫
EXEC DBMS_SPM.DROP_SQL_PLAN_BASELINE(
  sql_handle => 'SYS_SQL_abc123', plan_name => 'SYS_SQL_PLAN_xyz789');

進階應用場景

場景 1：應用升級前預先載入 Baseline

1. 舊版環境：DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE 匯出 STS
2. 匯入新版環境：DBMS_SPM.LOAD_PLANS_FROM_SQLSET
3. 新版上線後，優化器優先使用 Baseline，避免計畫突變

場景 2：參數變更風險控管

-- 變更參數前先建立 Baseline
EXEC DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'critical_sql');

-- 變更參數
ALTER SYSTEM SET optimizer_index_cost_adj = 10 SCOPE=BOTH;

-- 觀察是否有新計畫產生並演進
SELECT sql_handle, plan_name, origin, created, last_verified
FROM dba_sql_plan_baselines WHERE sql_handle = 'SYS_SQL_...';

場景 3：資料分布變化自適應

當資料傾斜改變（如州別分布從 NY 主導變為 CT 主導），優化器會產生新計畫（全表掃描取代索引）。SPM 會：

1. 發現新計畫 → 存入計畫歷史（UNACCEPTED）
2. 自動/手動演進 → 實際執行比較
3. 新計畫較優 → 升級為 ACCEPTED
4. 後續執行自動使用新計畫

SPM vs 傳統工具對照

最佳實務檢核表

• [ ] 核心業務 SQL（Top 50）皆已建立 Baseline
• [ ] optimizer_capture_sql_plan_baselines = FALSE（避免雜訊）
• [ ] 定期檢視 DBA_SQL_PLAN_BASELINES 中 REPRODUCED='NO' 的計畫（無法重現，需調查）
• [ ] SYSAUX 表空間監控（SMB 佔用空間）
• [ ] 升級/遷移前匯出 STS + Baseline，升級後載入
• [ ] 關鍵報表 SQL 設定 FIXED='YES' 防止意外演進
• [ ] 結合 SQL Tuning Advisor：Tuning 產生的 Profile 可轉為 Baseline

常見誤區澄清

1. 誤區：SPM 會讓 SQL 永遠用同一個計畫。
   事實：SPM 允許「多個已接受計畫」共存，優化器依當前綁定變量值選擇最佳者（自適應游標共享配合）。
2. 誤區：建立 Baseline 後就不用管了。
   事實：需定期檢視演進報告、清理無效計畫、監控 SYSAUX 空間。
3. 誤區：SPM 解決所有效能問題。
   事實：SPM 只防止「計畫倒退」。索引缺失、統計資訊過舊、SQL 寫法不佳等根因仍需解決。