Oracle SQL 效能調優兩大核心步驟：優化器模式選擇與表存取路徑決策

SQL 調優千頭萬緒，但 **90% 的收益來自 2 個核心決策**：**選對優化器模式**、**選對表存取路徑**。本文直擊本質，拒絕碎片化技巧，建立系統性調優決策框架。

步驟一：選用適合的優化器模式

Oracle 提供三種優化器模式 (10g 後 RULE 已棄用，但理解歷史有助於遷移)：**RBO (Rule-Based)、CBO (Cost-Based)、CHOOSE (相容模式)**。

模式對比與設定

CBO 核心前提：統計資訊準確性

-- 必須定期收集 (含直方圖)
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'SCOTT', 
  tabname => 'EMP', 
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO', -- 自動直方圖
  cascade => TRUE,                           -- 同步索引統計
  degree => 8                                -- 平行收集
);

-- 系統級自動收集 (10g+ 預設啟用)
-- 維護窗口: WEEKNIGHT_WINDOW, WEEKEND_WINDOW
-- 查詢自動任務狀態
SELECT client_name, status, window_name FROM dba_autotask_client;

鐵律：**CBO 無統計資訊 = 瞎子摸象**。新表、大量變更表、分區表、斜欄位**必須**手動/排程收集統計資訊。

目標模式選擇：ALL_ROWS vs FIRST_ROWS_n

• ALL_ROWS (預設)：**最小化總資源消耗**，適合批次、報表、資料倉庫、API 後端。
• FIRST_ROWS_1/10/100/1000：**最小化首筆/前 N 筆回應時間**，適合互動式查詢、分頁首頁、即時搜尋。會傾向 Nested Loop + Index Access Path。

步驟二：掌握表存取路徑 —— 只有兩條路

Oracle 存取表中資料，**只有兩種物理路徑**：

路徑 A：全表掃描 (Full Table Scan, FTS)

• 機制：多區塊讀取 (`DB_FILE_MULTIBLOCK_READ_COUNT`)，順序掃描 High Water Mark 以下所有區塊。
• 優勢：大比例資料存取 (>5-10%)、無索引、並行查詢、小表 (放入 Keep Pool)。
• 執行計畫關鍵字：`TABLE ACCESS FULL`

路徑 B：ROWID 存取 (Index Access → ROWID → Table Access by ROWID)

• 機制：索引掃描 (Range/Unique/Skip/Full) 取得 ROWID → 單區塊讀取 (`db file sequential read`) 回表。
• 優勢：高選擇性 (<1-5%)、精確查詢、排序避免 (Index Ordered)。
• 執行計畫關鍵字：`INDEX RANGE SCAN` → `TABLE ACCESS BY INDEX ROWID`

關鍵判斷：選擇率與聚簇因子

實戰決策流程圖 (文字版)

SQL 進入
   │
   ├─ 是否有統計資訊？ → 無 → 收集統計資訊 (DBMS_STATS) → 重試
   │
   ├─ 優化器模式？ → CHOOSE/RULE → 改 ALL_ROWS 或 FIRST_ROWS_n
   │
   ├─ 執行計畫 → 看存取路徑
   │     ├─ FTS → 選擇率高/聚簇因子差/無索引/並行 → **合理** → 結束
   │     │         → 不合理 → 檢查統計資訊/直方圖/索引定義/Hint
   │     │
   │     └─ Index Access → 選擇率低/聚簇因子好 → **合理** → 結束
   │               → 不合理 → 強制 FTS (FULL Hint) / 重建索引 / 改寫 SQL
   │
   └─ 效能達標？ → 否 → 進階調優 (Join 方法/順序、子查詢轉換、分區剪枝、並行、Hint、SQL Profile、Baseline)

常見誤區與避坑

1. 迷信索引：「有索引就快」→ 聚簇因子差時，Index Access 比 FTS 慢 100 倍。
2. 忽略統計資訊：「表沒變」→ 插入 10% 資料即可導致選擇率估算偏差 10 倍。
3. 濫用 Hint：「加 Hint 就快」→ 環境變更 (統計資訊、版本升級) 導致 Hint 產生災難計畫。**優先 SQL Profile / Baseline 固化計畫**。
4. 混淆 FIRST_ROWS 與 ALL_ROWS：批次報表用 FIRST_ROWS → 選 Nested Loop 慢死；互動查詢用 ALL_ROWS → 選 Hash Join 首筆等待過久。

總結：調優兩步走

1. 奠基：**CBO + 準確統計資訊 + 正確目標模式 (ALL_ROWS/FIRST_ROWS_n)**。
2. 決策：**讀懂執行計畫存取路徑 (FTS vs Index)**，判斷選擇率與聚簇因子是否匹配預期。

掌握這兩步，您已超越 80% 的「索引迷信者」與「Hint 依賴者」。後續進階 (Join、子查詢、分區、並行、Baseline) 皆建立在此基石之上。