Oracle EXPLAIN PLAN 實戰技巧全解析：四大獲取方式、AUTOTRACE 進階用法與工具輔助

為什麼需要執行計劃

Oracle RDBMS 執行每條 SQL 都經過優化器評估。掌握優化器如何選擇存取路徑、索引如何被使用，是 SQL 調優的前提。EXPLAIN PLAN 可快速查出給定 SQL 的資料存取路徑，助你選擇最優查詢方式。

方式一：EXPLAIN PLAN 命令（不執行 SQL，僅解析）

1.1 環境準備：建立 PLAN_TABLE

-- Unix/Linux
@$ORACLE_HOME/rdbms/admin/utlxplan.sql

-- Windows
@%ORACLE_HOME%\rdbms\admin\utlxplan.sql

-- 建立公用同義詞與授權（供所有用戶使用）
CREATE PUBLIC SYNONYM PLAN_TABLE FOR SYS.PLAN_TABLE;
GRANT ALL ON PLAN_TABLE TO PUBLIC;

1.2 基本語法

EXPLAIN PLAN [SET STATEMENT_ID = '唯一標識'] [INTO table_name] 
FOR ;

• STATEMENT_ID：區分同一 PLAN_TABLE 中多筆計劃，建議每次指定
• INTO：指定目標表，預設為 PLAN_TABLE

1.3 實戰範例

EXPLAIN PLAN SET STATEMENT_ID = 'T_TEST' FOR SELECT * FROM T_TEST;

1.4 查看計劃的兩種方式

方式 A：手工查詢 PLAN_TABLE（舊版相容）

SELECT OPERATION, OPTIONS, OBJECT_NAME, OBJECT_TYPE, ID, PARENT_ID
FROM PLAN_TABLE
WHERE STATEMENT_ID = 'T_TEST'
ORDER BY ID;

方式 B：DBMS_XPLAN.DISPLAY（推薦，格式化輸出）

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'T_TEST', 'TYPICAL'));

• 格式參數：BASIC、TYPICAL（預設）、ALL、ADVANCED
• 可顯示 Cost、Cardinality、Bytes、Predicate、Access/Filter Predicates 等關鍵資訊

方式二：SQL*Plus AUTOTRACE（執行 SQL 並顯示計劃/統計）

2.1 環境準備

-- 1. 建立 PLAN_TABLE（同方式一）
-- 2. 建立 PLUSTRACE 角色並授權
@$ORACLE_HOME/sqlplus/admin/plustrce.sql
GRANT PLUSTRACE TO PUBLIC;  -- 或特定用戶

2.2 核心指令

SET AUTOTRACE ON;              -- 顯示執行計劃 + 統計 + 結果集
SET AUTOTRACE ON EXPLAIN;      -- 僅顯示執行計劃（仍執行 SQL）
SET AUTOTRACE ON STATISTICS;   -- 僅顯示統計資訊（仍執行 SQL）
SET AUTOTRACE TRACEONLY;       -- 顯示計劃+統計，不顯示結果集（仍執行）
SET AUTOTRACE TRACEONLY EXPLAIN; -- 僅顯示計劃，不顯示結果/統計（仍執行！）
SET AUTOTRACE OFF;             -- 關閉

2.3 關鍵警示 ⚠️

AUTOTRACE 所有模式都會實際執行 SQL！
即使 SET AUTOTRACE TRACEONLY EXPLAIN，UPDATE/DELETE/MERGE 依然會修改資料。
調優 DML 時嚴禁使用 AUTOTRACE，請改用 EXPLAIN PLAN FOR 或第三方工具。

2.4 搭配 TIMING 觀察執行時間

SET TIMING ON
SET AUTOTRACE TRACEONLY EXPLAIN
SELECT COUNT(*) FROM BIG_TABLE;

方式三：第三方 GUI 工具（不執行 SQL，最安全）

方式四：V$SQL_PLAN / DBMS_XPLAN.DISPLAY_CURSOR（生產環境實際執行計劃）

前三種顯示的是預估計劃，實際執行可能不同（Bind Peeking、Adaptive Plan、Cardinality Feedback）。生產環境調優必須看實際計劃：

-- 1. 先執行目標 SQL（或找到其 SQL_ID）
SELECT sql_id, sql_text FROM v$sql WHERE sql_text LIKE '%KEYWORD%';

-- 2. 顯示實際執行計劃（含實際行數 A-Rows、啟動次數 Starts）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id_這裡', NULL, 'ALLSTATS LAST'));

• ALLSTATS LAST：顯示最後一次執行的實際統計（A-Rows、A-Time、Buffers、Reads）
• ADAPTIVE：顯示自適應計劃資訊（12c+）
• 需 SELECT ANY DICTIONARY 或 DBA 權限

EXPLAIN PLAN 限制與注意事項

• 權限限制：使用者必須是被解釋物件（表、視圖、索引）的擁有者。僅有 SELECT 權限無法使用 EXPLAIN PLAN，會報錯
• Bind Variable 偷窺：EXPLAIN PLAN 不進行 Bind Peeking，預估計劃可能與實際不同
• 動態採樣：無統計資訊的表，EXPLAIN PLAN 會觸發動態採樣，結果可能每次不同
• Adaptive Plan：12c+ 執行中可能切換計劃，EXPLAIN PLAN 只顯示初始計劃

實戰調優檢查清單（看計劃時必看的 5 點）

1. Operation 類型：FULL TABLE SCAN → 檢查索引；INDEX RANGE SCAN → 正常；INDEX FAST FULL SCAN → 類全表掃描
2. Cost 與 Cardinality：Cost 高不一定慢，但 Cardinality 嚴重偏離實際行數 → 統計資訊過期或斜態欄位缺直方圖
3. Predicate 區分：Access Predicates（存取條件，決定掃描範圍）vs Filter Predicates（過濾條件，掃描後再篩選）
4. Partition Pruning：分區表查詢是否出現 PARTITION RANGE SINGLE/ALL，未修剪分區 = 全分區掃描
5. Parallel Execution：PX SEND/RECEIVE、PX BLOCK ITERATOR 表示並行，檢查 DOP 是否合理

總結

四大獲取方式各有定位：

• EXPLAIN PLAN FOR：開發階段、DML 調優、不想執行 SQL 時的首選
• AUTOTRACE：快速查詢 SELECT 語句的計劃與統計，但記住會執行 SQL
• GUI 工具：日常開發最直觀、最高效
• DBMS_XPLAN.DISPLAY_CURSOR：生產環境疑難雜症調優的金標準，看實際而非預估

掌握這四種方式並知曉其適用邊界，是 Oracle 開發者與 DBA 的必備基本功。

1. 上一篇文章： Oracle RAC 部署避坑指南：11 大關鍵檢查點與三階段安裝流程
2. 下一篇文章： Oracle 預存程序開發規範與效能優化實戰指南

Oracle 資料庫基礎

圖解 SQL Server 2005 連結 Oracle 11g 完整步驟：從 Client 安裝到 Linked Server 測試

架構概覽 SQL Server 透過 Linked Serv

Linux 下 Oracle 手動建庫腳本實戰全記錄

建庫核心腳本 c.sql CREATE DATABASE m

Oracle Database Link 完整實作：類型、認證模式、同義詞封裝與異構連線

前言：跨資料庫存取的標準化通道 Database Link

Oracle 資料庫前瞻性維護：善用 AWR 自動工作負載儲存庫

資料庫維護分為「反應式」（事後補救）與「前瞻式」（事前預防）