Oracle 提供三種優化器模式,正確選擇與配置對 SQL 執行效能影響深遠。本文解析三種模式特性、配置方式及最佳實踐。
| 模式 | 全稱 | 特性 |
|---|---|---|
| RULE | Rule-Based Optimizer (RBO) | 基於固定規則集,不依賴統計資訊,行為可預測但無法適應資料分佈變化。 |
| COST | Cost-Based Optimizer (CBO) | 基於統計資訊計算執行計畫成本,選擇最低成本路徑,需定期執行 ANALYZE 更新統計資訊。 |
| CHOOSE | Choose | 預設模式。若表有統計資訊則用 CBO,否則退回 RBO。易導致非預期的全表掃描。 |
在 init.ora 設定 OPTIMIZER_MODE 參數:RULE、COST、CHOOSE、ALL_ROWS、FIRST_ROWS。亦可在 SQL 或 Session 層級覆寫:
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;
SELECT /*+ FIRST_ROWS */ * FROM emp;
預設 CHOOSE 模式下,若統計資訊缺失或過舊,優化器會退回 RBO,導致全表掃描。建議:
COST 或 RULE,避免隱性切換;ANALYZE / DBMS_STATS 排程,確保統計資訊準確;ALL_ROWS 全表最優、FIRST_ROWS 快速回應首筆)。循序讀取所有區塊,Oracle 以多區塊讀取優化。適用於小表或需讀取大部分資料的查詢。
透過 ROWID 直接定位資料物理位置。索引提供快速取得 ROWID 的路徑,基於索引欄位的查詢可大幅提升效能。
Oracle 將解析後的 SQL 存放於 SGA 共用池,相同語句可重用執行計畫,減少解析開銷與記憶體佔用。
SELECT * FROM EMP 與 select * from emp 視為不同。:blk1.pin 與 :blk1.ot_ind 雖執行時值相同,但名稱不同,無法共享。在 init.ora 設定 SHARED_POOL_SIZE,池越大可快取越多語句,命中率越高。