Oracle SQL 執行計畫查看三大經典方法：EXPLAIN PLAN、TKPROF、AUTOTRACE 實戰指南

前言

無論開發 DBA 或維運 DBA，SQL 效能調優核心技能即是「看懂執行計畫」。本文整理三種最常用、最實用的查看方法。

方法一：EXPLAIN PLAN FOR + DBMS_XPLAN.DISPLAY

基本語法

EXPLAIN PLAN FOR
SELECT sysdate FROM dual;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

實際輸出範例

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
|  0 | SELECT STATEMENT |      |      |       |      |
|  1 | TABLE ACCESS FULL| DUAL |      |       |      |
--------------------------------------------------------------------
Note: rule based optimization

進階用法

• DBMS_XPLAN.DISPLAY('PLAN_TABLE', NULL, 'ADVANCED')：顯示 Outline、Projection、Predicate 等詳細資訊
• DBMS_XPLAN.DISPLAY_CURSOR(sql_id, child_number, 'ALLSTATS LAST')：查看**實際執行統計** (A-Rows、A-Time、Buffers)，需啟用 STATISTICS_LEVEL=ALL 或 Hint /*+ GATHER_PLAN_STATISTICS */
• 指定 Plan Table：EXPLAIN PLAN SET STATEMENT_ID = 'my_test' FOR ...

方法二：TKPROF 格式化 Trace 檔案

適用場景

系統整體效能不佳時，追蹤 Session 產生 Trace 檔，用 TKPROF 排序找出問題 SQL。

標準操作流程 (5 步驟)

1. Session 級開啟 Trace：

   ALTER SESSION SET SQL_TRACE = TRUE;
   -- 或指定 Session：EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid, serial#, TRUE);
   

2. 設定 Trace 檔識別碼 (方便尋找)：

   ALTER SESSION SET TRACEFILE_IDENTIFIER = 'yourname';
   

3. 執行目標 SQL 陳述式
4. 執行 TKPROF 格式化：

   tkprof /oracle/admin/orcl/udump/orcl_ora_10266_yourname.trc \
     /oracle/yourname.txt \
     EXPLAIN=user/pwd \
     AGGREGATE=YES \
     SYS=NO \
     WAITS=YES \
     SORT=FCHELA
   

   關鍵參數：SORT=FCHELA 依 Fetch/CPU/Elapsed 排序，快速定位高耗 SQL

5. 關閉 Trace：ALTER SESSION SET SQL_TRACE = FALSE;

TKPROF 輸出關鍵欄位

方法三：AUTOTRACE (SQL*Plus 內建)

快速啟用

SET AUTOTRACE ON EXPLAIN STATISTICS
-- 或
SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS  -- 不顯示查詢結果

輸出包含

• Execution Plan (同 EXPLAIN PLAN)
• Statistics：Recursive calls、DB block gets、Consistent gets、Physical reads、Redo size、Bytes sent/received、SQL*Net roundtrips、Sorts (memory/disk)、Rows processed

三大方法比較與選擇建議

實戰建議

1. 開發階段：EXPLAIN PLAN + GATHER_PLAN_STATISTICS 迭代調優
2. 上線前：TKPROF 對核心業務流程全鏈路 Trace 建立基線
3. 生產事故：ASH/AWR/ADDM (Enterprise Edition) 為首選，TKPROF 為輔
4. 養成習慣：每個關鍵 SQL 必須能說出 **Access Path、Join Method、Join Order、Cardinality Estimation** 是否合理