Oracle SQL 執行計畫快速上手：AUTOTRACE 與 TOAD 圖形化分析

為什麼要看執行計畫？

SQL 調優的起點是**理解 Oracle 如何執行你的語句**：掃描方式（全表/索引）、Join 順序/方法、預估成本。AUTOTRACE 是 SQL*Plus 內建、零安裝的輕量分析工具。

環境準備（一次性，需 DBA 權限）

CONNECT / AS SYSDBA
@?/rdbms/admin/utlxplan.sql          -- 建立 PLAN_TABLE
CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;
GRANT SELECT, INSERT, UPDATE, DELETE ON PLAN_TABLE TO PUBLIC;

@?/sqlplus/admin/plustrce.sql        -- 建立 PLUSTRACE ROLE
GRANT PLUSTRACE TO PUBLIC;           -- 讓普通用戶能用 AUTOTRACE

AUTOTRACE 三種模式

實戰範例

SET AUTOTRACE TRACEONLY EXPLAIN
SET TIMING ON
SET LINESIZE 200
SET PAGESIZE 1000

SELECT e.ENAME, d.DNAME
FROM EMP e, DEPT d
WHERE e.DEPTNO = d.DEPTNO
  AND e.SAL > 2000;

輸出解讀重點

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

| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
|  0 | SELECT STATEMENT   |      |   5  |  200  |     3  (0) | 00:00:01 |
|  1 |  HASH JOIN         |      |   5  |  200  |     3  (0) | 00:00:01 |
|  2 |   TABLE ACCESS FULL| EMP  |  14  |  350  |     1  (0) | 00:00:01 |
|  3 |   TABLE ACCESS FULL| DEPT |   4  |   80  |     1  (0) | 00:00:01 |

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

• Operation：TABLE ACCESS FULL = 全表掃描（需檢查索引）；INDEX RANGE SCAN = 索引範圍掃描（好）。
• Cost：優化器估算資源消耗（CPU+I/O），**相對值**，同一 SQL 不同計畫比較有意義。
• Rows/Bytes：預估輸出基數，偏離實際 → 統計資訊過期需 DBMS_STATS.GATHER_TABLE_STATS。
• Statistics：
  • consistent gets：邏輯讀（記憶體），**越小越好**。
  • physical reads：物理讀（磁碟），應為 0 或極小。
  • sorts (disk)：磁碟排序，出現即需調大 PGA_AGGREGATE_TARGET 或優化 SQL。

TOAD 圖形化執行計畫（Ctrl+E）

1. 編輯器中游標置於 SQL 任意位置。
2. 按 Ctrl+E（或工具列「Explain Plan」圖示）。
3. 彈出視窗顯示**樹狀圖 + 成本 + 統計 + 物件屬性**，支援：

• 節點右鍵 → 「View Object」查看索引/表定義。
• 「Compare Plans」對比兩次執行計畫差異。
• 「Auto Optimize」嘗試 HINT 重寫建議。

進階：DBMS_XPLAN 顯示游標實際計畫 (10g+)

AUTOTRACE 顯示的是**預估計畫**；實際執行可能不同（Bind Variable Peeking、Adaptive Plan）。

-- 1. 執行目標 SQL（含 Bind 變數）
VARIABLE b_deptno NUMBER;
EXEC :b_deptno := 10;
SELECT * FROM EMP WHERE DEPTNO = :b_deptno;

-- 2. 立即顯示該游標實際計畫
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALL +OUTLINE +PEEKED_BINDS'));

輸出包含：**Peeked Binds 值、Outline Data（HINT 全集）、Actual Rows/Time (含 STATISTICS_LEVEL=ALL)**。

常用調優檢查清單

結語

AUTOTRACE 是 **SQL 調優的聽診器**：零部署、即時可用、輸出標準。掌握 SET AUTOTRACE TRACEONLY EXPLAIN + SET TIMING ON 組合，配合 TOAD Ctrl+E 圖形化、DBMS_XPLAN 實際計畫，即可覆蓋 95% 日常調優場景。**記住：看計畫不看 Cost 看 Operation，看統計不看 Recursive Calls 看 Consistent Gets/Physical Reads。**

1. 上一篇文章： Oracle Heterogeneous Services 異構查詢實戰：透過 ODBC 連接 SQL Server
2. 下一篇文章： Oracle VPD 細粒度存取控制進階實戰：策略類型選型與效能優化

Oracle 資料庫基礎

以 PHP 建立 Oracle 驅動的 SOAP Web Service 完整實作教學

一、架構概覽與必要元件 本範例建立一個 BookManage

RedHat 4 上安裝 Oracle 10g 全過程詳記（僅軟件安裝）

前言 本文記錄在 RedHat 4 虛擬機上安裝 Oracl

DAN (Database Area Network) 架構深度解析：超越 SAN 的動態資料庫負載均衡

前言：資料庫層的「最後一公里」瓶頸 Web/應用層已透過負載

從 Oracle 到 SQL Server：基於語法樹的 SQL 智能翻譯器架構設計

動機：異構資料庫遷移的 SQL 轉換挑戰 簡單關鍵字替換 (