Oracle SQL 調優實戰：執行計劃深度解讀、驅動表判定與 Hint 強制優化

從執行計劃看穿 SQL 效能本質

執行計劃是 CBO 決策的透明化輸出。本文透過兩個實戰案例，系統講解如何閱讀計劃樹結構、識別優化器模式、判定驅動表順序、分析連接方法選擇，並示範 ORDERED Hint 強制最佳路徑。

案例一：全表掃描與優化器模式識別

SQL> SELECT * FROM large_table WHERE username = 'TEST';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14)
   1    TABLE ACCESS FULL LARGE_TABLE [:Q65001] [ANALYZED]

關鍵指標解讀：

• Optimizer=CHOOSE：參數模式，非實際使用模式
• Cost=1234：有 Cost 值 → **實際使用 CBO** (RBO 無 Cost)
• TABLE ACCESS FULL：全表掃描，無索引可用
• [:Q65001]：並行查詢 Slave 進程標識
• [ANALYZED]：物件有統計資訊，CBO 可用

判斷規則：Cost= 顯示數值 → CBO；Cost= 空白或無 Cost 行 → RBO。

案例二：三表連接驅動順序深度分析

SELECT A.col4
FROM   A, B, C
WHERE  B.col3 = 10
AND    A.col1 = B.col1
AND    A.col2 = C.col2
AND    C.col3 = 5;

-- 索引：A(col1, col2) 組合索引，col1 為導引欄位

執行計劃 (去除 INDEX 縮進後)：

| Id | Operation                | Name     |
|----|--------------------------|----------|
|  0 | SELECT STATEMENT         |          |
|  1 |  MERGE JOIN              |          |
|  2 |   SORT JOIN              |          |
|  3 |    NESTED LOOPS          |          |
|  4 |     TABLE ACCESS FULL    | B        |  <-- 最右最上：驅動表 B
|  5 |     TABLE ACCESS BY INDEX ROWID| A  |
|  6 |   SORT JOIN              |          |
|  7 |    TABLE ACCESS FULL     | C        |

驅動表判定邏輯 (縮進法)

1. 移除 INDEX 相關行 (INDEX RANGE SCAN 等)
2. 剩餘樹中，**縮進最深 (最右) 且最上方** 的 TABLE ACCESS = 第一個驅動表
3. NESTED LOOPS 內：外部表 = 驅動表，內部表 = 被驅動表
4. 連接順序遞推：B → A → C

為何 B 為最佳驅動表？

強制最佳連接順序：ORDERED Hint

SELECT /*+ ORDERED */ A.col4
FROM   B, A, C          -- FROM 順序即連接順序
WHERE  B.col3 = 10
AND    A.col1 = B.col1
AND    A.col2 = C.col2
AND    C.col3 = 5;

原理：ORDERED 告訴 CBO 依 FROM 子句順序連接，不再自行排列組合。配合驅動表優先原則，將最佳驅動表放第一位。

常見 Hint 組合

統計資訊對連接順序的影響

• CBO 依賴 NUM_ROWS、DISTINCT_KEYS、CLUSTERING_FACTOR、直方圖預估 Cardinality
• 統計過期 → Cardinality 偏差大 → 連接順序錯誤 → 效能災難
• 定期 DBMS_STATS.GATHER_TABLE_STATS(..., METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO')

調優檢查清單

• ☐ 執行計劃是否顯示 CBO (有 Cost 值)
• ☐ 驅動表是否為高選擇性過濾後的小結果集
• ☐ 連接欄位是否有合適索引 (特別是導引欄位)
• ☐ Cardinality 預估是否接近實際 (執行後 DBMS_XPLAN.DISPLAY_CURSOR 對比)
• ☐ 是否需要 Hint 修正 CBO 選擇 (記錄理由、測試驗證)

深度解讀執行計劃樹結構、掌握驅動表判定縮進法、理解統計資訊對連接順序的決定性影響，是從「看得懂計劃」進化到「精準控制計劃」的關鍵能力躍遷。