Oracle 執行計畫機制與優化：Optimizer Search Limit、Ordered Hint、Stored Outline 實戰

SQL 預備階段五步驟

1. 語法檢查
2. 語義分析（字典驗證表/欄位）
3. 輪廓檢查（Library Cache 是否已有可重用計畫）
4. 生成執行計畫（CBO 統計資訊、成本模型、連接順序排列組合）
5. 建立二進位執行代碼

第 4 步最耗時，特別是多表連接。

連接順序排列組合爆炸

控制參數

兩者**同時生效**：優化器在達到任一上限即停止搜尋，選擇當前最低成本計畫。

Ordered Hint 強制連接順序

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/USE_HASH/USE_MERGE 指定連接方法。
• 適用：數據倉庫 4+ 表連接、已知最佳順序的固定報表查詢。

實務最佳策略：Stored Outline 固化最佳計畫

1. 臨時設定 OPTIMIZER_SEARCH_LIMIT = 表數，讓優化器窮舉找到最佳順序。
2. 調整 WHERE 子句順序、加入 ORDERED Hint，驗證執行計畫最優。
3. 建立 Stored Outline：

   CREATE OUTLINE ol_emp_bonus FOR CATEGORY monthly_rpt
   ON SELECT /*+ ORDERED USE_NL(b) */ ...;

4. 生產環境設定 USE_STORED_OUTLINES = TRUE，該查詢自動套用固化計畫，跳過優化器搜尋。

10g+ 替代方案：SQL Plan Baseline (SPM)

• DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE 將優秀計畫載入 Baseline。
• 自動演化：新計畫優於 Baseline 才採納，避免效能倒退。
• 優於 Stored Outline：支援計畫歷史、自動演化、與 AWR 整合。

總結

• 新 SQL 首次解析延遲主要來自連接順序搜尋。
• 調整 OPTIMIZER_SEARCH_LIMIT/MAX_PERMUTATIONS 可限制搜尋空間。
• ORDERED Hint 手工指定順序，配合 Stored Outline/SPM 固化，徹底消除解析期開銷。