Oracle 8i 效能調優實戰：成本基礎優化器 (CBO) 核心機制與執行計畫穩定性

優化器選擇：CBO vs RBO

Oracle 8i 提供兩種優化器：

• Rule-Based Optimizer (RBO)：固定規則排序，不依賴統計資訊
• Cost-Based Optimizer (CBO)：計算各執行路徑成本（I/O、CPU、網路），選擇最低成本路徑

CBO 為 8i 預設且唯一持續增強方向，RBO 已不再更新。

啟用 CBO 的三種方式

1. 實例級：init.ora 設定 OPTIMIZER_MODE = CHOOSE
   • 有統計資訊 → CBO
   • 無統計資訊 → RBO
2. Session 級：ALTER SESSION SET OPTIMIZER_GOAL = FIRST_ROWS | ALL_ROWS
   • FIRST_ROWS：優化首筆回應時間（OLTP）
   • ALL_ROWS：優化整體吞吐量（DSS/批次）
3. Statement 級 Hint：

   SELECT /*+ CHOOSE */ ...
   SELECT /*+ FIRST_ROWS */ ...
   SELECT /*+ ALL_ROWS */ ...

為何執行計畫非最優？

CBO 完全依賴統計資訊準確性。統計過期、缺失、採樣不足會導致錯誤成本估算。必須定期執行：

ANALYZE TABLE table_name COMPUTE STATISTICS;      -- 完整統計
ANALYZE TABLE table_name ESTIMATE STATISTICS SAMPLE 20 PERCENT;  -- 採樣統計
-- 9i+ 建議使用 DBMS_STATS 套件
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'TABLE', CASCADE=>TRUE);

執行計畫變更的五大常見原因

執行計畫穩定化最佳實踐

1. 統計資訊管理策略：
   • 建立固定排程收集統計（非高峰期）
   • 使用 DBMS_STATS 而非 ANALYZE
   • 鎖定關鍵表統計 DBMS_STATS.LOCK_TABLE_STATS 防止意外變更
   • 保留統計歷史 DBMS_STATS.EXPORT_SCHEMA_STATS 供回滾
2. SQL Plan Management (SPM) / Stored Outlines：
   • 捕獲驗證過的優良計畫
   • 防止統計波動導致計畫退化
   • 8i 使用 CREATE OUTLINE，11g+ 使用 SPM
3. Hint 策略性使用：
   • 僅在經過驗證的關鍵 SQL 使用
   • 記錄 Hint 理由與測試數據
   • 定期複核 Hint 仍有效
4. 監控計畫變更：
   • 定期比對 V$SQL_PLAN / DBA_HIST_SQL_PLAN
   • 建立計畫變更告警機制

實戰檢查清單

-- 1. 確認優化器模式
SHOW PARAMETER OPTIMIZER_MODE;

-- 2. 檢查表統計狀態
SELECT table_name, last_analyzed, num_rows, blocks, sample_size
FROM user_tables WHERE table_name = 'YOUR_TABLE';

-- 3. 查看執行計畫
EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 4. 比較 Hint 前後計畫
EXPLAIN PLAN FOR SELECT /*+ FIRST_ROWS */ ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

總結

CBO 是智慧引擎，統計資訊是其燃料。穩定的統計資訊 + 受控的參數變更 + 計畫基線管理 = 穩定的執行計畫 = 可預測的效能。調優非一次性動作，而是持續的統計治理與計畫監控流程。