Oracle 執行計劃深度解析：自建 PLAN_TABLE、DBMS_XPLAN 顯示與層級樹形查詢

Oracle 執行計劃深度解析：自建 PLAN_TABLE、DBMS_XPLAN 顯示與層級樹形查詢

一、EXPLAIN PLAN 基礎流程

EXPLAIN PLAN FOR
SELECT t1.name, t2.grade
FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id
WHERE t1.id = 704;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

輸出關鍵欄位解讀

二、PLAN_TABLE 結構與常用欄位

DESC PLAN_TABLE;
-- 關鍵欄位：STATEMENT_ID, PLAN_ID, TIMESTAMP, OPERATION, OPTIONS,
-- OBJECT_NAME, ID, PARENT_ID, DEPTH, POSITION, COST, CARDINALITY, BYTES,
-- ACCESS_PREDICATES, FILTER_PREDICATES, PROJECTION

三、自建樹形顯示查詢（利用 CONNECT BY）

SELECT id, parent_id,
       LPAD(' ', LEVEL) || operation || ' ' || options || ' ' || object_name AS operation_tree
FROM plan_table
START WITH id = 0
CONNECT BY PRIOR id = parent_id;

輸出範例

ID PARENT_ID OPERATION_TREE
-- --------- -------------------------------------------
 0           SELECT STATEMENT
 1          0  HASH JOIN OUTER
 2          1   TABLE ACCESS BY INDEX ROWID TABLE1
 3          2    INDEX RANGE SCAN INDEX_TABLE1_ID
 4          1   TABLE ACCESS FULL TABLE2

四、實戰調優切入點

1. Cardinality 偏差大 → 收集統計資訊、直方圖
2. 出現 FULL TABLE SCAN → 檢查索引缺失、函數運算、隱式轉換
3. JOIN 順序不佳 → 檢查統計資訊、考慮 LEADING/ORDERED HINT
4. INDEX RANGE SCAN 後 TABLE ACCESS BY ROWID 過多 → 考慮覆蓋索引、Index Fast Full Scan

五、進階：V$SQL_PLAN_STATISTICS_ALL 即時計劃

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

顯示實際執行行數 (A-Rows)、啟動次數、時間、記憶體等運行時統計，比 EXPLAIN PLAN 更精準。