Oracle SQL 執行效率提升三大核心技術：ORDERED、ORDERED_PREDICATES、OPTIMIZER_SEARCH_LIMIT

從剖析優化到執行計畫控制的進階調優三板斧

複雜 SQL 剖析階段耗時往往超過執行階段。本文深度解析三大核心技術：ORDERED 提示跳過連接順序排列組合、ORDERED_PREDICATES 控制 WHERE 條件評估順序、OPTIMIZER_SEARCH_LIMIT 限制連接順序搜尋空間，附實戰案例與適用場景判斷。

技術一：ORDERED 提示 — 固定連接順序，跳過排列組合爆炸

問題背景

• N 表連接有 N! 種連接順序 (7 表 = 5040 種，8 表 = 40320 種)
• CBO 評估所有順序耗時可達數十分鐘

解決方案

SELECT /*+ ORDERED USE_NL(b) PARALLEL(e,4) */
       e.ename, e.hiredate, b.comm
FROM   emp e, bonus b
WHERE  e.ename = b.ename;

核心規則

1. FROM 子句中第一張表為驅動表，應選擇過濾後行數最少的表
2. 後續表按連接順序排列
3. 常搭配 USE_NL (巢狀迴圈)、USE_HASH (雜湊連接) 指定連接方法
4. 跳過連接順序排列組合評估，大幅縮短剖析時間

適用場景

• 7+ 表連接的複雜報表查詢
• 連接順序明確且穩定的固定查詢 (如 ETL 固定 SQL)
• 統計資訊不準導致 CBO 選錯順序時的緊急干預

技術二：ORDERED_PREDICATES 提示 — 控制布林條件評估順序

預設評估順序 (無提示時)

1. 子查詢優先於外層 WHERE 條件
2. 無函數/子查詢的條件：從後往前 (最後寫的最先評估)
3. 含內建函數的條件：按預估評估成本遞增

ORDERED_PREDICATES 效果

強制按 WHERE 子句書寫順序從前往後評估條件。

適用場景

• WHERE 條件中含 PL/SQL 函數調用，需控制呼叫順序避免例外
• 明知某條件過濾力最強 (如高選擇性索引欄位)，希望最先評估
• 注意：不保證索引鍵評估順序，不影響存取路徑選擇

技術三：OPTIMIZER_SEARCH_LIMIT 參數 — 限制連接順序搜尋上限

參數作用

• 控制 CBO 評估連接順序組合的最大數量
• 預設值 5
• 當 FROM 表數 < OPTIMIZER_SEARCH_LIMIT 時，自動啟用 Star Transformation 提示

調整策略

-- Session 級調整 (測試用)
ALTER SESSION SET optimizer_search_limit = 10;

-- 系統級調整 (謹慎)
ALTER SYSTEM SET optimizer_search_limit = 10 SCOPE=BOTH;

效果與風險

綜合實戰案例：月結報表 12 表連接優化

-- 原始：剖析 45 分鐘
SELECT /*+ ORDERED 
           USE_HASH(fact) 
           USE_NL(dim1) USE_NL(dim2) USE_NL(dim3)
           ORDERED_PREDICATES */
       ...
FROM   small_filter_table sft,      -- 驅動表：過濾後 10 行
       large_fact_table fact,       -- 事實表：億級
       dim_table1 dim1,             -- 維表 1
       dim_table2 dim2,             -- 維表 2
       dim_table3 dim3              -- 維表 3
WHERE  sft.filter_col = :b1
  AND  fact.fk1 = dim1.pk
  AND  fact.fk2 = dim2.pk
  AND  fact.fk3 = dim3.pk
  AND  fact.dt BETWEEN :start_dt AND :end_dt
  AND  dim1.attr = 'ACTIVE'        -- 選擇性高，ORDERED_PREDICATES 使最先評估
  AND  dim2.type = 'SALES';

優化結果

• 剖析時間：45 分鐘 → 3 秒
• 執行時間：2 小時 → 15 分鐘 (配合平行度、分區修剪)
• 共用池壓力：大幅降低

技術選擇決策樹

複雜 SQL 剖析慢?
    ↓
表數 ≥ 7? → 是 → ORDERED + 明確驅動表
    ↓ 否
WHERE 條件含 PL/SQL 函數/明確最強過濾條件? → 是 → ORDERED_PREDICATES
    ↓ 否
統計資訊不穩導致計畫抖動? → 是 → 降低 OPTIMIZER_SEARCH_LIMIT (5~10)
    ↓ 否
收集/鎖定統計資訊 + SQL Plan Baseline 固定計畫

避坑指南

• ORDERED 需深度理解資料分布，錯誤驅動表會導致災難性計畫
• ORDERED_PREDICATES 不影響存取路徑，僅影響過濾順序
• OPTIMIZER_SEARCH_LIMIT 系統級修改影響全庫，建議 Session 級測試後再推廣
• 長期解決方案：收集完整統計資訊 + SQL Plan Management (SPM) 固定優質計畫

掌握這三大技術，能在剖析優化層面精準干預複雜 SQL 效能問題，配合 SPM 構建穩定高效的執行環境。