Oracle EXPLAIN PLAN 與 AUTOTRACE 實戰：四種獲取執行計劃方式深度對比

四種獲取執行計劃的方式

方式一：EXPLAIN PLAN FOR (標準、安全)

-- 1. 創建計劃表 (每用戶一次)
@?/rdbms/admin/utlxplan.sql
-- 或手工
CREATE TABLE plan_table (...) ;  -- 見 utlxplan.sql

-- 2. 生成計劃
EXPLAIN PLAN SET STATEMENT_ID = 'test_001' FOR
SELECT /*+ LEADING(e) */ e.ename, d.dname
FROM   emp e, dept d
WHERE  e.deptno = d.deptno
AND    e.sal > 2000;

-- 3. 顯示計劃
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'test_001', 'ALL'));
-- 或簡化
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

方式二：AUTOTRACE (SQL*Plus 專用)

-- 一次性設置 (需 SYS 執行)
@?/rdbms/admin/utlxplan.sql
@?/sqlplus/admin/plustrce.sql
GRANT PLUSTRACE TO PUBLIC;

-- 使用
SET AUTOTRACE TRACEONLY EXPLAIN
SELECT * FROM emp WHERE deptno = 10;
-- 輸出計劃 + 統計 (不顯示數據)

-- 關閉
SET AUTOTRACE OFF

⚠️ 警告：AUTOTRACE 會真實執行 SQL，嚴禁對 UPDATE/DELETE/MERGE 使用，否則修改數據！

方式三：DBMS_XPLAN.DISPLAY_CURSOR (實際執行計劃金標準)

-- 1. 開啟統計收集
ALTER SESSION SET STATISTICS_LEVEL = ALL;

-- 2. 運行目標 SQL (可帶綁定變量)
VAR b_deptno NUMBER;
EXEC :b_deptno := 10;
SELECT /*+ GATHER_PLAN_STATISTICS */ e.ename
FROM   emp e
WHERE  e.deptno = :b_deptno;

-- 3. 顯示最後一條游標的實際計劃
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST'));

關鍵列：E-Rows (估算行) vs A-Rows (實際行) — 偏差大 = 統計信息失準/綁定窺探問題。

方式四：第三方工具 (PL/SQL Developer / TOAD / DBeaver / SQL Developer)

• PL/SQL Developer: F5 或 Tools → Explain Plan
• TOAD: SQL 編輯器下方 Explain Plan 標籤
• DBeaver: Ctrl+Shift+E 或右鍵 Explain Plan
• 優勢：圖形化樹、高亮全表掃描、計劃對比、歷史保存。

進階：解讀計劃關鍵操作

限制與避坑

• EXPLAIN PLAN 不能解釋其他用戶對象 (無所有者權限)，即使有 SELECT 權限也會報錯。
• 綁定變量窺探：EXPLAIN PLAN 不窺探，AUTOTRACE/DISPLAY_CURSOR 會 — 計劃可能不同。
• 適應性計劃 (12c+)：DISPLAY_CURSOR 顯示 + 標記的備選操作，EXPLAIN PLAN 只顯示默認。