Oracle 啟用 AUTOTRACE 查看 SQL 執行計畫與效能統計完整步驟

AUTOTRACE 是 SQL*Plus 內建的輕量級效能分析工具，無需建立追蹤檔即可顯示執行計畫與統計資訊。本文提供完整的權限授與腳本與使用範例。

前置作業：建立 PLAN_TABLE 與 PLUSTRACE 角色

-- 以 SYSDBA 登入
CONNECT sys AS sysdba;

-- 1. 建立 PLAN_TABLE（若不存在）
@?/rdbms/admin/utlxplan.sql

-- 2. 建立公用同義詞並授權
CREATE PUBLIC SYNONYM plan_table FOR plan_table;
GRANT ALL ON plan_table TO PUBLIC;

-- 3. 執行 plustrce 腳本建立角色與視圖授權
@?/sqlplus/admin/plustrce.sql

-- 4. 明確建立角色並授與必要視圖權限（plustrce.sql 內已含，若報錯可手動補齊）
CREATE ROLE plustrace;
GRANT SELECT ON v_$sesstat TO plustrace;
GRANT SELECT ON v_$statname TO plustrace;
GRANT SELECT ON v_$mystat TO plustrace;
-- 視版本可能尚需 v_$session, v_$process 等

-- 5. 將角色授予目標使用者（或 PUBLIC）
GRANT plustrace TO scott;  -- 或 GRANT plustrace TO PUBLIC;

使用 AUTOTRACE

-- 開啟 AUTOTRACE（僅顯示計畫）
SET AUTOTRACE TRACEONLY EXPLAIN

-- 開啟 AUTOTRACE（顯示計畫 + 統計）
SET AUTOTRACE TRACEONLY STATISTICS

-- 完整模式（查詢結果 + 計畫 + 統計）
SET AUTOTRACE ON

-- 關閉
SET AUTOTRACE OFF

輸出範例

SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL> SELECT * FROM emp WHERE deptno = 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 1234567890

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0| SELECT STATEMENT  |      |    3 |   117 |     3   (0)| 00:00:01 |
|*  1|  TABLE ACCESS FULL| EMP  |    3 |   117 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPTNO"=10)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
        564  bytes sent via SQL*Net to client
        432  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

常見問題排查

• SP2-0618: Cannot find the Session Identifier → 未授 plustrace 角色或 v_$sesstat 權限不足。
• PLAN_TABLE does not exist → 執行 utlxplan.sql 並建立同義詞。
• 權限最小化：生產環境建議僅授予特定使用者，避免 GRANT TO PUBLIC。

進階替代方案

• DBMS_XPLAN.DISPLAY_CURSOR：更詳細的計畫資訊（含實際 Cardinality、A-Time）。
• SQL_TRACE / 10046 EVENT：完整追蹤，適合深度分析。
• Enterprise Manager / SQL Developer 圖形化計畫。