核心架構：Optimizer (優化器)
Oracle 優化器負責將 SQL 轉換為執行計畫。歷經兩代演進：
| 代別 | 名稱 | 決策依據 | 狀態 |
|---|
| 第一代 | RBO (Rule-Based Optimizer) | 固定規則集 (索引存在→用索引、全表掃描排序→用索引...) | **10g 移除**，僅相容性保留 |
| 第二代 | CBO (Cost-Based Optimizer) | 統計資訊 (表/索引/欄位/系統) + CPU/IO 成本模型 | **唯一現代優化器** (10g+ 強制) |
優化器模式參數 OPTIMIZER_MODE
| 模式值 | 行為 | 適用場景 |
|---|
RULE | 強制 RBO (已廢棄) | 極舊系統相容 |
CHOOSE | 有統計→CBO，無統計→RBO (9i 預設，**10g 移除**) | 9i 過渡期 |
FIRST_ROWS | CBO，優化**首批行回應時間** (OLTP 分頁) | 互動式查詢、Web 分頁 |
FIRST_ROWS_n | CBO，優化前 n 行 (n=1,10,100,1000) | 精細控制首屏速度 |
ALL_ROWS | CBO，優化**總吞吐量/總成本最小** (預設 10g+) | 批次報表、資料倉庫、ETL |
三層控制優化器行為
- Instance 級：
ALTER SYSTEM SET OPTIMIZER_MODE=ALL_ROWS SCOPE=BOTH;
- Session 級：
ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS_10;
- Statement 級 (Hint)：
SELECT /*+ FIRST_ROWS(10) */ * FROM orders WHERE cust_id = :1;
SELECT /*+ ALL_ROWS */ * FROM big_table;
SELECT /*+ RULE */ * FROM legacy_table; -- 僅 9i- 有效
統計資訊：CBO 的生命線
**統計過舊/缺失 = CBO 盲目亂猜 = 執行計畫災難**
收集統計標準流程
-- 1. 表級 (含索引、欄位直方圖)
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'TABLE_NAME', METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO');
-- 2. Schema 級
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA', OPTIONS=>'GATHER AUTO');
-- 3. 自動任務 (10g+ 預設啟用，維護窗口每晚執行)
SELECT * FROM DBA_AUTOTASK_CLIENT WHERE CLIENT_NAME = 'auto optimizer stats collection';
自動化腳本：批量產生 ANALYZE (舊版相容) / DBMS_STATS (現代)
-- 現代版：產生 DBMS_STATS 呼叫腳本
SET PAGES 0 FEED OFF HEAD OFF
SPOOL gather_stats.sql
SELECT 'EXEC DBMS_STATS.GATHER_TABLE_STATS('''||owner||''','''||table_name||''', METHOD_OPT=>''FOR ALL COLUMNS SIZE AUTO'');'
FROM dba_tables WHERE owner = 'MY_SCHEMA';
SPOOL OFF
@gather_stats.sql
統計失效常見徵兆與對策
| 徵兆 | 可能原因 | 對策 |
|---|
| 突然走全表掃描 | 統計顯示表很小 (實際大)、或索引統計缺失 | 重收統計、檢查 NUM_ROWS vs 實際 |
| 索引不被使用 | 欄位直方圖缺失導致選擇度錯估、或 CLUSTERING_FACTOR 惡化 | 收集直方圖 SIZE SKEWONLY、重建索引 |
| 執行計畫震盪 | 統計收集頻率過高/過低、Bind Variable Peeking | 鎖定統計 DBMS_STATS.LOCK_TABLE_STATS、使用 OPTIMIZER_DYNAMIC_SAMPLING |
進階：SQL Plan Management (SPM) 鎖定優質計畫 (11g+)
-- 1. 捕捉基線
ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
-- 執行目標 SQL...
-- 2. 查看/固定基線
SELECT sql_handle, plan_name, enabled, accepted, fixed FROM dba_sql_plan_baselines;
EXEC DBMS_SPM.ALTER_SQL_PLAN_BASELINE(sql_handle=>'SYS_SQL_...', plan_name=>'...', attribute_name=>'FIXED', attribute_value=>'YES');
-- 3. 演進驗證 (自動驗證新計畫優於基線才接受)
EXEC DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle=>'SYS_SQL_...');
結語
**RBO 是歷史，CBO 是現在與未來**。掌握 OPTIMIZER_MODE 三層控制、**統計資訊自動化收集與品質監控**、以及 **SPM 執行計畫穩定機制**，是現代 Oracle DBA/開發的核心童子功。切記：**沒有統計資訊的 CBO 比 RBO 更危險**。
