Oracle SQL 調優三板斧：ORDERED 提示、謂詞順序控制、優化器搜索限制

Oracle 複雜查詢剖析階段耗時可達數十分鐘（如 8 表連接 40,000+ 連接順序排列）。本文介紹三種減少剖析開銷、加速執行的實戰技巧：ORDERED 提示固定連接順序、ORDERED_PREDICATES 控制謂詞求值順序、optimizer_search_limit 限制優化器搜索空間。

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

痛點

Oracle 優化器需評估所有表連接順序排列，表數越多排列數呈階乘增長（8 表 = 40,320 種），剖析時間可達 30 分鐘+。

用法

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

• ORDERED：強制按 FROM 子句順序連接，第一張表為驅動表。
• 驅動表選擇原則：返回行數最少、過濾後結果集最小 的表置前。
• 配合 USE_NL（嵌套循環）、PARALLEL（並行掃描）等提示進一步鎖定計畫。

效果

• 完全跳過連接順序枚舉，剖析時間從分鐘級降至毫秒級。
• 需 DBA 根據統計資料/業務經驗確定最優順序，統計失效時可能產生劣計畫。

技巧二：ORDERED_PREDICATES 提示 —— 指定 WHERE 條件求值順序

默認求值規則（無提示時）

1. 子查詢優先於外層 WHERE 條件。
2. 無函數/子查詢的布爾條件，按 WHERE 子句逆序求值（最後寫的最先算）。
3. 含內置函數的條件，按預估代價遞增求值。

用法

SELECT /*+ ORDERED_PREDICATES */
       *
FROM   large_table
WHERE  expensive_plsql_func(col1) = 'Y'  -- 想最後算
   AND col2 = 100                        -- 想最先算（高選擇性）
   AND col3 > SYSDATE - 30;              -- 中等選擇性

• 強制按 WHERE 子句書寫順序求值。
• 適用場景：WHERE 中調用昂貴 PL/SQL 函數，需先用高選擇性列過濾掉大部分行，再調用函數。
• 限制：無法改變索引鍵存取路徑的求值順序（索引範圍掃描由優化器決定）。

技巧三：optimizer_search_limit 參數 —— 截斷優化器搜索空間

機制

該隱含參數控制優化器評估的最大連接順序排列數。默認值 5，意味著表數 ≤ 5 時全量枚舉，>5 時啟發式搜索（贪婪/動態規劃）。

調整策略

• 增大值（如 10-12）：針對 6-12 表的關鍵報表，允許更充分枚舉，獲取更優計畫。代價：剖析時間線性增長。
• 減小值（如 3-4）：極高併發 OLTP 系統，強制優化器快速決策，犧牲部分計畫質量換取剖析穩定性。

副作用：控制 STAR 提示觸發閾值

當查詢表數 < optimizer_search_limit 時，STAR 提示（星型查詢轉換）生效。調整此參數會間接影響星型查詢優化決策。

實戰組合拳

ALTER SESSION SET optimizer_search_limit = 8;  -- 會話級調整，不影響全局

SELECT /*+ ORDERED ORDERED_PREDICATES USE_HASH(f) PARALLEL(s, 4) */
       s.store_name, SUM(f.sales_amt)
FROM   stores s, facts f, dates d, products p
WHERE  s.store_id = f.store_id
   AND f.date_id = d.date_id
   AND f.prod_id = p.prod_id
   AND d.year = 2024
   AND p.category = 'Electronics'
   AND s.region = 'East'
GROUP  BY s.store_name;

• 會話級調整 optimizer_search_limit，避免全局副作用。
• ORDERED 鎖定連接順序（驅動表：stores → dates → products → facts）。
• ORDERED_PREDICATES 確保高選擇性過濾（year、category、region）優先求值。
• USE_HASH/PARALLEL 鎖定連接方式與並行度。

避坑指南

1. 統計資料是前提：提示建立在統計準確基礎上，統計過期/缺失時提示可能導致災難性計畫。
2. 測試驗證：生產前用 EXPLAIN PLAN / SQL Trace / DBMS_XPLAN 對比提示前後代價、行數、操作。
3. 文檔化：每個提示註明「業務場景、統計版本、預期效果、負責人」，便於升級/統計重收後複核。
4. 優先考慮 SQL Profile / SPM：11g+ 推薦用 SQL Plan Management 固化優秀計畫，而非硬編碼提示。