Oracle 優化器選擇指南：RULE、COST、CHOOSE 三模式深度解析與最佳實務

Oracle 提供三種優化器模式：RULE（規則基礎）、COST/CHOOSE（成本基礎）。正確選擇與統計資訊管理，直接決定 SQL 執行計畫品質。本文深度解析三模式運作機制、OPTIMIZER_MODE 參數、統計資訊策略、存取路徑選擇、共享游標條件，助您事半功倍。

三大優化器模式

參數設定層級與優先序

-- 1. 執行個體層級 (init.ora / spfile)
OPTIMIZER_MODE = ALL_ROWS        -- 全表最佳吞吐量（批次、報表）
OPTIMIZER_MODE = FIRST_ROWS_10   -- 前 10 列最快回應（OLTP、分頁）

-- 2. Session 層級
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_1;

-- 3. Statement 層級 (Hint)
SELECT /*+ ALL_ROWS */ * FROM big_table;
SELECT /*+ FIRST_ROWS(10) */ * FROM orders WHERE cust_id = :1;

建議：實例層級設 ALL_ROWS，OLTP 關鍵查詢用 FIRST_ROWS_n Hint 微調。嚴禁使用 CHOOSE。

統計資訊：CBO 的生命線

CBO 依賴 DBMS_STATS 收集的統計資訊：

• 表統計：列數、區塊數、平均列長、樣本數。
• 欄位統計：NDV（不重複值數）、密度、直方圖、Null 比例、最小/最大值。
• 索引統計：層級、葉區塊數、聚簇因子、不重複鍵值數。
• 系統統計：CPU 速度、單區塊/多區塊讀取時間、最大吞吐量（DBMS_STATS.GATHER_SYSTEM_STATS）。

-- 標準收集作業（排程每日/每週執行）
BEGIN
  DBMS_STATS.GATHER_DATABASE_STATS(
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    cascade          => TRUE,
    degree           => DBMS_STATS.AUTO_DEGREE,
    options          => 'GATHER AUTO'  -- 11g+ 智慧收集
  );
END;
/

關鍵：STALE_PERCENT（預設 10%）超過即標記過期，自動收集作業（AUTO_TASK）會處理。大表建議啟用增量統計（INCREMENTAL=TRUE + 分區表）。

資料存取路徑：全表掃描 vs 索引存取

ROWID 含資料物理位置（物件號、檔案號、區塊號、列號），索引即「鍵值 → ROWID」映射。

共享游標（Shared Cursor / Library Cache）

相同 SQL 文字（字元級完全相符，含大小寫、空白、換行）可共用解析樹與執行計畫，減少 Hard Parse。

共用條件（三要素）

1. 字元級比對：SQL 文字逐字元相同。
2. 物件解析相同：參考相同物件（同 Schema、同名稱）、相同權限。
3. 優化器環境相同：OPTIMIZER_MODE、NLS 參數、_optimizer_* 隱藏參數等一致。

最佳實務

• 應用程式統一使用繫結變數（PreparedStatement / bind variables），避免字面值導致 Hard Parse 暴增。
• CURSOR_SHARING = FORCE 僅作過渡方案，可能產生次優計畫，長期應修正程式碼。
• 調大 SHARED_POOL_SIZE / RESULT_CACHE 容納更多游標。

避免不必要的全表掃描檢核清單

• ☐ 關鍵表、欄位統計資訊最新（LAST_ANALYZED 近期）
• ☐ 索引存在且可用（STATUS=VALID、VISIBLE）
• ☐ 查詢條件未對索引欄位做函數運算/隱含轉換（WHERE TO_CHAR(date_col) = '2024' → 失效）
• ☐ 複合索引欄位順序符合查詢條件（前導欄位在 WHERE 中）
• ☐ OPTIMIZER_MODE 非 CHOOSE / RULE
• ☐ 繫結變數使用一致，避免 Bind Peeking 導致計畫翻轉（11g+ ADAPTIVE_CURSOR_SHARING 緩解）

掌握優化器模式、統計資訊、存取路徑、共享游標四大支柱，即可從根源解決 80% 的 SQL 效能問題，實現「事半功倍」。