善用 DBMS_XPLAN 函數輕鬆解讀 EXPLAIN PLAN 執行計劃

EXPLAIN PLAN：SQL 效能調校的關鍵診斷工具

EXPLAIN PLAN 指令是診斷 SQL 效能低落問題最重要的工具之一。它會將 Oracle 優化器為特定 SQL 語句計算出的執行計劃步驟，載入一張工作表（預設名為 PLAN_TABLE）。由於 PLAN_TABLE 中的資料呈現階層結構，若要查詢分析，過去必須撰寫包含 START WITH 與 CONNECT BY 子句的複雜 SQL。自 Oracle 9i 推出並經 10g 擴充的 DBMS_XPLAN 套件，讓格式化與顯示執行計劃變得前所未有的簡單。

核心函數：DBMS_XPLAN.DISPLAY

DBMS_XPLAN.DISPLAY 是一個表格回傳函數，能直接顯示 PLAN_TABLE 的內容。不同於一般的純量函數（如 SUBSTR）或彙總函數（如 SUM），表格函數會回傳完整的資料列集合，必須在 SELECT 陳述式的 FROM 子句中搭配 TABLE 關鍵字使用：

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY);

這行指令即可產生結構清晰、易讀的執行計劃報表，包含操作類型、物件名稱、成本、基數等關鍵資訊。

進階功能：即時與歷史計劃分析

DBMS_XPLAN 的強大之處不止於靜態分析，還提供兩個關鍵函數支援動態與歷史場景：

• DBMS_XPLAN.DISPLAY_CURSOR：可從系統全域區（SGA）中讀取「即時」執行計劃。透過查詢 V$SESSION 找到目標會話的 SQL_ID，傳入此函數即可獲得該游標實際使用的執行計劃，對於排查生產環境中的效能尖峰極具價值。
• DBMS_XPLAN.DISPLAY_AWR：可查詢自動負載儲存庫（AWR）中保留的歷史 SQL 執行計劃。它提供長達 7 天的滾動歷史視窗，讓 DBA 能事後分析過去的效能問題，或比較不同時期的計劃變化。

總結

這三個函數——DISPLAY、DISPLAY_CURSOR、DISPLAY_AWR——構成了 Oracle SQL 調校的鐵三角。無論是開發階段的靜態分析、生產環境的即時偵測，還是事後的歷史追溯，它們都能提供標準化、高可讀性的執行計劃輸出，是 DBA 與開發人員不可或缺的利器。