SQL 調優實戰（三）：三大獲取執行計劃的方法與實戰技巧

方法一：AUTOTRACE（最簡單）

SET AUTOTRACE ON              -- 顯示計劃 + 統計 + 結果集
SET AUTOTRACE TRACEONLY       -- 顯示計劃 + 統計，不顯示結果集（仍執行語句）
SET AUTOTRACE TRACEONLY EXPLAIN -- 僅產生計劃，不執行 SELECT（DML 仍執行）

優缺點：

• ON / TRACEONLY 需等待語句執行完成，長 SQL 優化週期極長
• TRACEONLY EXPLAIN 秒級產生計劃，但無 Statistics（物理 I/O 次數），難以判斷效率優劣

常見錯誤處理： 若報權限錯誤，依序執行：

@?/rdbms/admin/utlxplan.sql      -- 建立 PLAN_TABLE
@?/sqlplus/admin/plustrce.sql    -- 建立 PLUSTRACE role
GRANT PLUSTRACE TO username;

方法二：EXPLAIN PLAN 命令

@?/rdbms/admin/utlxplan.sql
EXPLAIN PLAN SET STATEMENT_ID='test1' FOR SELECT ...

• 不執行 SQL，僅寫入 PLAN_TABLE
• 需自行格式化輸出（層級縮排、Operation、Options、Object Name 等），可用性較低

方法三：DBMS_XPLAN 套件（推薦）

雖原文未詳述，但現代最佳實踐是：

EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

或針對已執行游標：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id, child_number, 'ALL'));

優點：自動格式化、顯示 Predicate、Cost、Bytes、Rows、Partition、Parallel 等豐富資訊，支援 AWR/ASH 歷史計劃查詢。

實戰建議

1. 日常快速檢視：SET AUTOTRACE TRACEONLY EXPLAIN
2. 深度分析：DBMS_XPLAN.DISPLAY_CURSOR 結合 ALL 格式
3. 生產庫無侵入：從 V$SQL_PLAN / DBA_HIST_SQL_PLAN 歷史視圖提取
4. 務必對比 E-Rows（預估）與 A-Rows（實際）找出誤估根因