| 參數 | 預設值 | 作用 |
|---|
| OPTIMIZER_SEARCH_LIMIT | 5 | 表數 ≤ 此值時，**窮舉所有排列**；表數 > 此值時，改用啟發式搜尋 (貪婪/動態規劃)，不保證最優。 |
| OPTIMIZER_MAX_PERMUTATIONS | 80,000 | **最大評估排列數上限** (11g 後已隱性/棄用，由動態規劃內部控制)。若達上限則停止搜尋，選擇目前最低 Cost 順序。 |
關係：優化器先檢查 SEARCH_LIMIT，再受 MAX_PERMUTATIONS 總上限約束。
手工指定連接順序：ORDERED & LEADING Hint
ORDERED
強制依 **FROM 子句順序** 連接，第一張表為驅動表 (Outer Table)。
SELECT /*+ ORDERED USE_NL(b) */ e.ename, b.comm
FROM emp e, bonus b
WHERE e.ename = b.ename;
- 常搭配
USE_NL, USE_HASH, USE_MERGE 指定連接方式。
- 適用於 **數據倉庫大查詢 (4+ 表)**，消除優化器搜尋開銷。
LEADING (更靈活，10g+ 推薦)
指定**連接順序清單**，不綁定 FROM 子句順序。
SELECT /*+ LEADING(e d s b) USE_HASH(e d) USE_NL(s b) */
...
FROM emp e, dept d, sal s, bonus b
WHERE ...;
LEADING(e d s b) 表示先連 e-d，再連 s，最後連 b。- 可部分指定：
LEADING(e d) 只固定前兩表，其餘交給優化器。
實戰調優流程
- **收集完整統計資訊** (含直方圖) → 確保優化器基數估算準確。
- **暫時調大 SEARCH_LIMIT** (Session 級) 讓優化器窮舉找出最佳順序：
ALTER SESSION SET OPTIMIZER_SEARCH_LIMIT = 10; -- 表數
- 執行查詢，用
DBMS_XPLAN.DISPLAY_CURSOR 觀察最佳順序。
- 將最佳順序固化為
LEADING Hint 寫入 SQL，或建立 **SQL Plan Baseline / Stored Outline** 持久化。
- 恢復參數預設值，避免影響其他查詢解析時間。
進階：SQL Plan Management (SPM) 持久化執行計畫
-- 1. 捕捉當前最佳計畫為 Baseline
DECLARE
l_plans PLS_INTEGER;
BEGIN
l_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => 'abc123xyz',
plan_hash_value => 1234567890,
fixed => 'YES' -- 固定不演化
);
END;
/
總結
「統計資訊是基石，Hint 是手術刀，Baseline 是保險箱。優先修正統計，再用 LEADING 微調，最後用 SPM 固化。」
- 上一篇文章： Oracle 遞迴查詢 CONNECT BY 處理父子層級資料完整教學
- 下一篇文章： Oracle 查詢使用者所有表、欄位與註解的實用 SQL 集合
Copyright ©
程式碼之家 All Rights Reserved
