Oracle 優化器模式決定 SQL 執行計畫生成邏輯。OPTIMIZER_MODE 可設為 RULE、COST、CHOOSE、ALL_ROWS、FIRST_ROWS。本文解析三大經典模式差異、CHOOSE 陷阱、現代最佳實務。
| 模式 | 全名 | 決策依據 | 適用版本 | 現狀 |
|---|---|---|---|---|
| RULE | Rule-Based Optimizer (RBO) | 固定規則排序（索引類型、UNIQUE、單表存取路徑優先級） | Oracle 7-10g | 10g 棄用，11g 移除 |
| COST | Cost-Based Optimizer (CBO) | 統計資訊（表/索引/欄位/系統）、CPU/I/O 成本模型 | Oracle 7+ | 唯一現代優化器 |
| CHOOSE | Choose Optimizer | 若表有統計資訊 → CBO；無統計 → RBO | Oracle 8-10g | 10g 棄用，11g 移除 |
早期版本預設 OPTIMIZER_MODE=CHOOSE。若開發/測試環境未收集統計資訊，優化器退回 RBO，依規則選擇索引；上線後統計資訊齊全，切換為 CBO，可能因統計偏差選擇全表掃描，導致效能劇烈波動。
ALTER SYSTEM SET OPTIMIZER_MODE=ALL_ROWS SCOPE=BOTH;（全表最佳吞吐）或 FIRST_ROWS_n（前 n 列最快回應）。EXEC DBMS_STATS.GATHER_DATABASE_STATS(options=>'GATHER AUTO');
-- 或針對表
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'EMP', cascade=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE AUTO');AUTO_STATS_TARGET，維護窗口每日執行，勿手動關閉。/*+ ALL_ROWS */、/*+ FIRST_ROWS(10) */、/*+ RULE */（僅相容舊碼，勿新用）。ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS_10;NUM_ROWS、BLOCKS、AVG_ROW_LEN、SAMPLE_SIZE、LAST_ANALYZEDBLEVEL、LEAF_BLOCKS、DISTINCT_KEYS、CLUSTERING_FACTORNUM_DISTINCT、LOW_VALUE/HIGH_VALUE、DENSITY、HISTOGRAM（頻率/高度平衡）CPUSPEEDNW、IOSEEKTIM、IOTFRSPEED、MAXTHR、SLAVETHR（DBMS_STATS.GATHER_SYSTEM_STATS）| 誤區 | 後果 | 正確做法 |
|---|---|---|
| 以為 RBO 更穩定 | 忽略資料分佈、硬體差異，新版不支援 | 全面拥抱 CBO，投資統計資訊品質 |
| 只收集表統計，忽略欄位直方圖 | 傾斜欄位選擇性預估嚴重偏差 | METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO' 或 SKEWONLY |
| 統計資訊過期不更新 | CBO 基於舊數據產生劣化計畫 | 啟用自動任務、大表增量統計 INCREMENTAL=TRUE |
| 生產環境直接用 CHOOSE | 統計收集前後計畫劇變，極難排查 | 明確設定 ALL_ROWS/FIRST_ROWS |
SHOW PARAMETER OPTIMIZER_MODE;
SELECT name, value FROM v$parameter WHERE name LIKE 'optimizer_%';拋棄 RBO/CHOOSE 歷史包袱，建立「CBO + 完整統計 + 自動收集 + 系統統計」四位一體優化體系，才是 Oracle 效能穩定的基石。
