Oracle 執行計劃查看與解讀完整指南：從 EXPLAIN PLAN 到 10046 Trace

執行計劃是 SQL 調優的「X 光片」。本文系統整理 Oracle 查看執行計劃的 7 大方法、關鍵欄位解讀、存取路徑分類、動態取樣陷阱，助你從計劃中精準定位效能瓶頸。

一、什麼是執行計劃

執行計劃描述一條 SQL 在 Oracle 內部的訪問路徑與操作步驟順序，由優化器（CBO/RBO）根據統計資訊、索引、約束、參數等生成。

二、七大查看方法

1. 圖形化工具（PL/SQL Developer、TOAD、SQL Developer）

• PL/SQL Developer：F5 或工具列「Explain Plan」
• 可在「Tools → Preferences → Explain Plan」自訂顯示欄位（Cost、Cardinality、Bytes、Partition、Predicate 等）

2. EXPLAIN PLAN + DBMS_XPLAN.DISPLAY（最通用）

EXPLAIN PLAN FOR
SELECT * FROM scott.emp WHERE deptno = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

支援 FORMAT => 'ALL +OUTLINE +PEEKED_BINDS' 顯示綁定變數窺探、Outline 提示等進階資訊。

3. AUTOTRACE（SQL*Plus 專用）

SET AUTOTRACE ON EXPLAIN      -- 計劃 + 結果
SET AUTOTRACE TRACEONLY       -- 僅計劃 + 統計，不輸出資料
SET AUTOTRACE TRACEONLY STAT  -- 僅統計
SET TIMING ON                 -- 顯示執行耗時

注意：PL/SQL Developer **不支援** SET AUTOTRACE，會報「Cannot SET AUTOTRACE」

4. SQL_TRACE + TKPROF（會話級完整追蹤）

-- 開啟
ALTER SESSION SET SQL_TRACE = TRUE;
-- 執行目標 SQL
SELECT * FROM scott.emp;
-- 關閉
ALTER SESSION SET SQL_TRACE = FALSE;

-- 定位 Trace 檔
SELECT value || '/' || LOWER(instance) || '_ora_' || spid || '.trc'
FROM v$parameter, v$thread, v$process, v$session
WHERE name = 'user_dump_dest' AND addr = paddr AND sid = m.sid ...;

-- 格式化
tkprof trace.trc output.txt EXPLAIN=user/pass SYS=NO SORT=PRSela,EXEela,FCHela

5. DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION（追蹤他人 Session）

SELECT sid, serial#, username FROM v$session WHERE username = 'SCOTT';
EXEC dbms_system.set_sql_trace_in_session(sid, serial#, TRUE);
-- 執行目標操作後
EXEC dbms_system.set_sql_trace_in_session(sid, serial#, FALSE);

6. 10046 事件（等待事件級追蹤，Level 8 含綁定變數）

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'mytrace';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
-- 執行 SQL
ALTER SESSION SET EVENTS '10046 trace name context off';

產生的 trace 檔名含 _10046.trc 後綴，配合 tkprof 或 TRCSESS 分析等待事件（db file sequential read、log file sync 等）。

7. 系統視圖即時查詢（無需開啟 Trace）

SELECT * FROM v$sql_plan WHERE sql_id = '...';
SELECT * FROM v$sql_plan_statistics_all WHERE sql_id = '...';  -- 含實際執行統計（A-Rows、A-Time）

三、執行計劃閱讀核心規則

1. 執行順序： 「由上至下，從右向左」—— 縮進最深、最靠右的操作最先執行
2. 關鍵欄位：
   • Id：操作編號（非執行順序）
   • Operation / Options：操作類型（TABLE ACCESS FULL、INDEX RANGE SCAN 等）
   • Name：物件名稱
   • Rows (Cardinality)：優化器預估回傳列數，**核對 Actual Rows 判斷統計資訊準確度**
   • Cost：相對成本值（無單位），同一計劃內比較有意義
   • Time：預估耗時
3. Predicate Information：
   • ACCESS：影響存取路徑（索引範圍、分區剪枝），**重點檢查是否用上索引**
   • FILTER：事後過濾，不影響存取路徑
4. Note 區塊： 出現 dynamic sampling used 表示表無統計資訊或統計過舊，CBO 採動態取樣，可能產生不穩定計劃

四、表存取路徑完全分類

路徑	Operation	適用場景	Hint
全表掃描	TABLE ACCESS FULL	小表、無索引、高選擇率、並行查詢	FULL(t)
索引唯一掃描	INDEX UNIQUE SCAN	PK/UK 等值查詢，回傳 0/1 列	INDEX(t idx)
索引範圍掃描	INDEX RANGE SCAN	前導欄位有範圍條件（>、<、BETWEEN、LIKE 'abc%'）	INDEX(t idx)
索引全掃描	INDEX FULL SCAN	需按索引順序輸出、避免排序、選擇率中等	INDEX_FFS(t idx)
索引快速全掃描	INDEX FAST FULL SCAN	全掃描索引不排序、多塊 I/O、可並行	INDEX_FFS(t idx)
索引跳躍掃描	INDEX SKIP SCAN	前導欄位值少、非前導欄位有條件	INDEX_SS(t idx)
ROWID 存取	TABLE ACCESS BY USER ROWID	已知 ROWID 直接定位，**最快單塊存取**	—

五、常見調優檢查清單

• Plan Hash Value 變化： 相同 SQL 出現多個 Plan Hash，檢查綁定變數窺探、統計資訊變化、參數 _OPTIM_PEEK_USER_BINDS
• Cardinality 嚴重偏離： 收集統計資訊 DBMS_STATS.GATHER_TABLE_STATS、建立直方圖 METHOD_OPT=>'FOR COLUMNS SIZE AUTO col'
• 未預期的 FULL TABLE SCAN： 檢查索引是否存在、前導欄位是否在 WHERE、隱式轉換（TO_CHAR/TO_NUMBER）導致索引失效
• 排序溢寫磁碟： SORT (DISK) 出現，調大 PGA_AGGREGATE_TARGET 或 SORT_AREA_SIZE
• 動態取樣誤導： 定期收集統計資訊，關鍵表設 LOCK_STATS 防止自動收集破壞

執行計劃不是靜態圖騰，而是隨統計資訊、參數、資料分布動態變化的決策快照。持續監控、定期驗證，才是調優常態。