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

三大優化器模式

設定層級與優先序

1. Instance 級：init.ora / spfile 設定 OPTIMIZER_MODE = RULE | CHOOSE | FIRST_ROWS | ALL_ROWS。
2. Session 級：ALTER SESSION SET OPTIMIZER_MODE = ...。
3. Statement 級：Hint /*+ RULE */、/*+ FIRST_ROWS(10) */、/*+ ALL_ROWS */。

優先序：Statement Hint > Session > Instance。

CBO 前提：統計資訊收集

-- 表級
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMP', CASCADE => TRUE);

-- Schema 級
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT', ESTIMATE_PERCENT => 20);

-- 系統統計 (CPU/IO 速度)
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('START'); -- 工作負載期間
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('STOP');

**無統計資訊 CBO 會退化為 RBO 或使用預設值，導致災難性計劃。**

資料存取路徑

共用 SQL (Shared Pool) 機制

• SQL 文本**完全相同** (含空白、換行、大小寫) 才能共用游標。
• 三大共用條件：
  1. 字元級逐字匹配。
  2. 引用物件解析至同一實體 (Synonym/權限影響)。
  3. Bind 變數名稱相同 (位置無關，名稱必須同)。
• CURSOR_SHARING = FORCE 可將常值強制轉 Bind 變數提高共用率 (可能產生計劃不穩定)。

避坑指南

1. 拒絕 CHOOSE：明確設定 OPTIMIZER_MODE = ALL_ROWS (批次/報表) 或 FIRST_ROWS_n (OLTP)。
2. 定時收集統計：建立排程作業，變更超過 10% 即收集。
3. 善用 Bind 變數：減少 Hard Parse，提升共用池效率。
4. 監控 V$SQL_PLAN / V$SQLSTATS：發現計劃變更立即分析統計資訊是否過期。

總結

CBO + 完整統計資訊 + Bind 變數 = 穩定高效執行計劃。RBO 僅作為舊系統相容保留，CHOOSE 已棄用。