Oracle 執行計畫一鍵查看：AUTOTRACE 與 DBMS_XPLAN 完全指南

最簡單的指令：SET AUTOTRACE ON

在 SQL*Plus 或 SQLcl 中輸入：

SET AUTOTRACE ON

後續執行的每一條 SQL 都會自動顯示執行計畫與統計資訊。若只想看計畫不想執行，可用：

SET AUTOTRACE TRACEONLY EXPLAIN

AUTOTRACE 完整選項

進階工具：DBMS_XPLAN.DISPLAY_CURSOR

適用於已執行的 SQL，提供更豐富的格式與資訊：

-- 1. 先執行目標 SQL（可加上 /*+ GATHER_PLAN_STATISTICS */ 取得實際行數）
SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM employees WHERE department_id = 10;

-- 2. 查看該游標的詳細執行計畫
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST'));

常用 FORMAT 參數：

• BASIC：基本資訊
• TYPICAL：預設，含成本、位元組、基數
• ALL：完整資訊含存取條件、投影資訊
• ALLSTATS LAST：含實際執行統計（A-Rows、A-Time、Buffers 等），效能調校首選
• ADVANCED：含 Outline、Peeked Binds 等進階資訊

歷史 SQL 查詢：DBA_HIST_SQL_PLAN (需 Diagnostic Pack)

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

圖形化介面：SQL Developer / Oracle Enterprise Manager

• SQL Developer：按 F10 或點擊「執行計畫」圖示
• OEM：Performance Hub → SQL Monitoring → Plan 頁籤

常見陷阱與提醒

1. AUTOTRACE 需建立 PLAN_TABLE：舊版本需執行 @$ORACLE_HOME/rdbms/admin/utlxplan.sql，新版自動建立全域暫存表。
2. TRACEONLY EXPLAIN 不會執行 DML，但 DDL 仍會執行。
3. 綁定變數窺探 可能導致顯示計畫與實際執行計畫不同，建議搭配 GATHER_PLAN_STATISTICS。

總結

SET AUTOTRACE ON 是快速檢視計畫的入門指令；DBMS_XPLAN.DISPLAY_CURSOR 則是深度調校的專業工具。熟練掌握兩者，讓 SQL 調優事半功倍。