Oracle 優化器選擇指南：RBO、CBO、統計資訊收集、Bind Variable 與 SQL Plan Management

三種優化器模式

CBO 核心依賴：統計資訊

必收集統計項目

• 表：行數、區塊數、平均行長
• 欄位：NDV (非重複值數)、NULL 數、密度、直方圖
• 索引：層級、葉區塊、叢集因子、非重複鍵值
• 系統：CPU 速度、I/O 吞吐 (DBMS_STATS.GATHER_SYSTEM_STATS)

收集指令

-- 基本收集
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMP', CASCADE => TRUE);

-- 生產環境建議參數
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'SCOTT', tabname => 'EMP',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO',
  cascade => TRUE, degree => 4, granularity => 'AUTO');

-- 系統統計 (關鍵！)
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('INTERVAL', 60);  -- 工作負載模式
-- 或
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('NOWORKLOAD');    -- 模擬模式

Bind Variable 與游標共享

硬解析 vs 軟解析

• Hard Parse：Library Cache Miss → 語法/語義檢查 → 優化 → 存入 Cache
• Soft Parse：Library Cache Hit → 直接執行

CURSOR_SHARING 參數

最佳實踐：應用層使用 Bind Variable，資料庫保持 EXACT。

SQL Plan Management (SPM) - 11g+ 計畫穩定

運作流程

1. Capture：自動/手動載入計畫至 SQL Plan Baseline
2. Select：優化器僅選擇 Accepted 的計畫
3. Evolve：新計畫驗證通過後標記 Accepted

關鍵操作

-- 啟用自動捕獲
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;

-- 手動載入 (從 Cursor Cache)
DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => 'abc123', fixed => 'YES');
END;
/

-- 查詢 Baseline
SELECT sql_handle, plan_name, enabled, accepted, fixed, origin
FROM dba_sql_plan_baselines;

-- 演進驗證
SET SERVEROUTPUT ON
DECLARE
  rpt CLOB;
BEGIN
  rpt := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
    sql_handle => 'SYS_SQL_123', verify => 'YES', commit => 'YES');
  DBMS_OUTPUT.PUT_LINE(rpt);
END;
/

-- 固定計畫 (不再演進)
EXEC DBMS_SPM.ALTER_SQL_PLAN_BASELINE(plan_name => 'PLAN_1', attribute_name => 'FIXED', attribute_value => 'YES');

存取路徑選擇原則

常見計畫錯誤排查步驟

1. 確認統計資訊新鮮度：SELECT last_analyzed FROM dba_tables WHERE table_name='...'
2. 檢查直方圖：SELECT column_name, histogram FROM dba_tab_col_statistics WHERE table_name='...'
3. 查看實際執行計畫：SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id, child_number, 'ALL'))
4. 比較估算 vs 實際行數 (E-Rows vs A-Rows)
5. 若估算嚴重偏差 → 收集直方圖、延伸統計、動態採樣
6. 若仍錯誤 → SQL Profile / SPM Baseline / Hint (最後手段)