Oracle 獲取執行計畫五大方法全攻略

方法一：EXPLAIN PLAN（預估計畫）

-- 1. 產生計畫至計畫表
EXPLAIN PLAN FOR
SELECT * FROM emp WHERE deptno = 10;

-- 2. 顯示計畫
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

限制：不執行 SQL，不考慮綁定變數窺探，環境差異可能導致計畫與實際不符。

方法二：DBMS_XPLAN.DISPLAY_CURSOR（庫快取實際計畫）

-- 1. 取得 SQL_ID 與 CHILD_NUMBER
SELECT sql_id, child_number, sql_text
FROM v$sql
WHERE sql_text LIKE '%關鍵字%';

-- 2. 顯示指定游標計畫
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id', child_number));

-- 3. 顯示上一條執行的計畫（含實際統計）
SET SERVEROUTPUT OFF
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

此法取得 Library Cache 中的真實執行計畫，含實際行數、啟動次數等統計。

方法三：DBMS_XPLAN.DISPLAY_AWR（歷史計畫）

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('sql_id'));

適用於已從 Library Cache 淘汰、但保留於 AWR 儲存庫的歷史計畫。

方法四：SQL*Plus AUTOTRACE（開發即時測試）

SET AUTOTRACE OFF                 -- 關閉（預設）
SET AUTOTRACE ON EXPLAIN          -- 僅顯示計畫
SET AUTOTRACE ON STATISTICS       -- 僅顯示統計
SET AUTOTRACE ON                  -- 計畫 + 統計
SET AUTOTRACE TRACEONLY           -- 不執行，僅預估（同 EXPLAIN PLAN）

僅限 SQL*Plus 會話使用，適合開發階段快速驗證。

方法五：SQL TRACE / 10046 事件（詳細追蹤）

會話級啟用

ALTER SESSION SET SQL_TRACE = TRUE;
-- 執行目標 SQL
ALTER SESSION SET SQL_TRACE = FALSE;

指定會話追蹤（需 DBA 權限）

EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid, serial#, TRUE);
-- 待追蹤完成
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid, serial#, FALSE);

TKPROF 格式化輸出

$ tkprof input.trc output.txt EXPLAIN=user/pass

10046 事件（進階追蹤）

屬 SQL_TRACE 超集，提供更詳細等待事件、綁定變數值等資訊。啟用方式：

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

方法選擇指南