Oracle 執行計畫深度解析：概念、實例、優化器模式、OUTLINES 固定計畫與統計資訊管理策略

什麼是執行計畫？

執行計畫是 Oracle 優化器為 SQL 語句生成的資料存取路徑藍圖。如同「從珠海去英國：經香港/北京/廣州，哪條路徑成本最低」，優化器依據統計資訊、約束、參數在眾多可能路徑中選擇一條「看似最優」的計畫。

兩個經典執行計畫對比：全表掃描 vs 索引全掃描

案例一：全表掃描

SQL> SET AUTOTRACE TRACEONLY
SQL> SELECT COUNT(*) FROM t;

Execution Plan
----------------------------------------------------------
0  SELECT STATEMENT Optimizer=CHOOSE
1    0  SORT (AGGREGATE)
2      1  TABLE ACCESS (FULL) OF 'T'

路徑：讀取表 T 所有區塊 → 記憶體累加計數。

案例二：索引全掃描

SQL> SELECT COUNT(*) FROM t;  -- 同一張表，但有索引 t_index

Execution Plan
----------------------------------------------------------
0  SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=1)
1    0  SORT (AGGREGATE)
2      1  INDEX (FULL SCAN) OF 'T_INDEX' (NON-UNIQUE) (Cost=26 Card=28180)

路徑：讀取索引 T_INDEX 所有葉區塊 → 累加計數（無需回表）。

哪個更快？ 通常索引全掃描較快（索引較小），但非絕對。取決於：表/索引大小、Clustering Factor、緩存狀態、多區塊讀設定。**關鍵在於優化器是否有準確統計資訊做正確選擇**。

Oracle 優化器模式演進與選擇

Session 級切換

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10;  -- 首 10 列最快
ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS;       -- 總成本最低

為什麼執行計畫會「變壞」？

1. 統計資訊過期/缺失：CBO 依賴 DBMS_STATS 收集的表/索引/欄位/系統統計。資料變動 > 10% 統計即失真。
2. 統計收集資源大、耗時久：大表全掃描收集統計影響業務，只能維護窗口執行 → 統計與實際資料有時間差。
3. 優化器成本模型不完美：成本計算公式內部黑盒，無法完美反映真實 I/O/CPU 情況。
4. 環境差異：測試/生產環境硬體、參數、資料分佈不同 → 同一 SQL 產生不同計畫。

OUTLINES / SQL Plan Baselines：固定「好」計畫

HINT 引導優化器

SELECT /*+ FIRST_ROWS(10) LEADING(e d) USE_NL(d) INDEX(d PK_DEPT) */ 
       e.ename, d.dname 
FROM emp e, dept d 
WHERE e.deptno = d.deptno;

OUTLINES（10g/11g 主流）

-- 1. 啟用 OUTLN 使用者與表空間
-- 2. 建立 Outline
CREATE OUTLINE ol_emp_dept_nl FOR CATEGORY ol_cat
ON SELECT /*+ FIRST_ROWS(10) ... */ ...;

-- 3. 啟用類別
ALTER SESSION SET USE_STORED_OUTLINES = ol_cat;
-- 或實例級
ALTER SYSTEM SET USE_STORED_OUTLINES = ol_cat SCOPE=BOTH;

SQL Plan Baselines (SPB, 11g+ 正式取代 Outline)

-- 1. 從游標快取載入基線
DECLARE
  l_plans PLS_INTEGER;
BEGIN
  l_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => 'abc123def456',  -- 目標 SQL_ID
    plan_hash_value => 1234567890,  -- 期望的計畫
    fixed => 'YES',  -- 固定不演化
    enabled => 'YES');
END;
/

-- 2. 查看基線
SELECT sql_handle, plan_name, enabled, accepted, fixed, reproduced
FROM dba_sql_plan_baselines;

-- 3. 演化控制：新統計產生新計畫時，SPB 可自動/手動驗證是否採用

Outline vs SPB：Outline 靜態存 HINT 文本；SPB 存完整計畫操作樹，支援演化（新計畫經驗證優於基線可自動採用），是現代標準。

統計資訊管理進階策略

1. 分級收集策略

2. 統計資訊匯出/匯入：生產→測試環境驗證

-- 生產環境：匯出統計到統計表
EXEC DBMS_STATS.CREATE_STAT_TABLE('STATS_OWNER', 'STAT_TAB');
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('PROD_SCHEMA', 'STAT_TAB', 'STATS_OWNER');
-- 用 Data Pump 匯出 STAT_TAB 表

-- 測試環境：匯入統計
EXEC DBMS_STATS.IMPORT_SCHEMA_STATS('TEST_SCHEMA', 'STAT_TAB', 'STATS_OWNER');
-- 測試環境跑回歸測試，觀察計畫變化

3. 延遲發布統計：收集不立即生效

-- 收集到私有統計表，不發布
EXEC DBMS_STATS.SET_GLOBAL_PREFS('PUBLISH', 'FALSE');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMP');
-- 驗證私有統計下的計畫
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(...));
-- 確認無誤後發布
EXEC DBMS_STATS.PUBLISH_PENDING_STATS();

綜合策略：統計 + SPB 雙重保險

生產環境穩定運行
    │
    ├─ 定期收集統計（增量/分區/直方圖）
    │
    ├─ 關鍵 SQL 建立 SPB 固定核心計畫
    │
    ├─ 新統計收集後：
    │    ├─ 自動驗證 SPB 演化（新計畫更優 → 採用）
    │    └─ 人工干預：回歸測試發現退化 → 禁用新計畫、保留 SPB
    │
    └─ 重大變更（升級/遷移/分區）：
         ├─ 預先匯出統計
         ├─ 測試環境匯入統計 + 跑回歸
         ├─ 比對計畫變化，提前建立 SPB
         └─ 生產環境同步統計 + SPB，平滑過渡

總結

執行計畫穩定性是生產系統可靠性的基石。**CBO 依賴統計資訊，統計有滯後與誤差，優化器模型有盲區**。「統計資訊分級管理 + SPB 固定核心計畫 + 測試環境預驗證」三位一體，才能在資料增長、版本升級、硬體變更的長期演進中，保證關鍵 SQL 執行計畫不退化、業務 SLA 不違約。