#Oracle? #執行計劃? #EXPLAIN PLAN
一、引言
在Oracle數據庫性能優化中，?執行計劃（Execution Plan）?是理解SQL語句如何被數據庫處理的關鍵工具。EXPLAIN PLAN是Oracle提供的一種靜態分析SQL執行路徑的方法，它通過生成邏輯執行計劃幫助開發者和DBA預測SQL的性能表現，而無需實際執行SQL語句。本文將深入探討EXPLAIN PLAN的原理、使用場景、操作步驟及最佳實踐，并結合DBA_OBJECTS表提供完整案例。
二、EXPLAIN PLAN 的核心原理
- ?靜態分析?：EXPLAIN PLAN不會實際執行SQL，而是將優化器生成的執行計劃寫入PLAN_TABLE（系統臨時表）。
- ?無副作用?：適合測試敏感操作（如DDL、DML）的執行計劃，避免對生產數據產生影響。
- ?依賴優化器統計信息?：執行計劃的準確性高度依賴表和索引的統計信息（如行數、塊數、直方圖等）。
三、使用 EXPLAIN PLAN 的步驟
1. ?準備環境?
1.1 確認 PLAN_TABLE 存在
默認情況下，Oracle會通過腳本UTLXPLAN.SQL創建PLAN_TABLE。若表不存在，需以DBA身份執行：
@?/rdbms/admin/utlxplan.sql1.2 權限要求
用戶需具備以下權限：
- SELECT ANY TABLE（訪問目標表如DBA_OBJECTS）
- CREATE SESSION（登錄數據庫）
2. ?基本語法
EXPLAIN PLAN FOR [SQL語句];SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);關于DBMS_XPLAN.DISPLAY包的使用，會在專題文章中詳解說明。
四、實戰案例
1. ?案例背景?
假設需要分析以下SQL的執行計劃，查詢TEST1表中所有類型為TABLE的對象：
create table test1 as select * from dba_objects;select count(1) from test1;COUNT(1)
----------862592. ?生成執行計劃?
2.1 使用 EXPLAIN PLAN
SQL> explain plan for SELECT OBJECT_ID, OBJECT_NAME, OBJECT_TYPE FROM TEST1 WHERE OBJECT_TYPE = 'TABLE';Explained.-- 查看執行計劃
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC'));2.2 輸出解析
示例輸出：
SQL>
set linesize 1000
set pages 1000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALL'));PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4122059633---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2013 | 176K| 336 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TEST1 | 2013 | 176K| 336 (1)| 00:00:05 |
---------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------1 - SEL$1 / TEST1@SEL$1Predicate Information (identified by operation id):
---------------------------------------------------1 - filter("OBJECT_TYPE"='TABLE')Column Projection Information (identified by operation id):
-----------------------------------------------------------1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22],"OBJECT_TYPE"[VARCHAR2,19]Note
------ dynamic sampling used for this statement (level=2)28 rows selected.
?關鍵字段說明?：
- ?Id?：步驟編號，根節點為0。
- ?Operation?：操作類型（如全表掃描TABLE ACCESS FULL）。
- ?Name?：涉及的索引或表名
- Rows?：優化器估算的返回行數。
- ?Cost?：總成本。
五、關于explian plan底層原理
在Oracle 10g及更高版本中，當對目標SQL執行explain plan命令時 ，Oracle會把解析該SQL所生成執行計劃的具體執行步驟，寫入PLAN_TABLE$表。PLAN_TABLE$是一個提交時保留行的全局臨時表（ON COMMIT PRESERVE ROWS GLOBAL TEMPORARY TABLE）。這就使得各個會話（Session）只能看到自己執行SQL產生的執行計劃，而且不同會話往PLAN_TABLE$寫入執行計劃的操作互不干擾。后續執行select * from table(dbms_xplan.display) ，只是將PLAN_TABLE$的執行步驟以格式化形式展示出來。
1、PLAN_TABLE$
查看表定義
SQL> set long 90000
set heading off
set serveroutput on size 1000000
select dbms_metadata.get_ddl('TABLE', 'PLAN_TABLE$', 'SYS') from dual;CREATE GLOBAL TEMPORARY TABLE "SYS"."PLAN_TABLE$"( "STATEMENT_ID" VARCHAR2(30),"PLAN_ID" NUMBER,"TIMESTAMP" DATE,"REMARKS" VARCHAR2(4000),"OPERATION" VARCHAR2(30),"OPTIONS" VARCHAR2(255),"OBJECT_NODE" VARCHAR2(128),"OBJECT_OWNER" VARCHAR2(30),"OBJECT_NAME" VARCHAR2(30),"OBJECT_ALIAS" VARCHAR2(65),"OBJECT_INSTANCE" NUMBER(*,0),"OBJECT_TYPE" VARCHAR2(30),"OPTIMIZER" VARCHAR2(255),"SEARCH_COLUMNS" NUMBER,"ID" NUMBER(*,0),"PARENT_ID" NUMBER(*,0),"DEPTH" NUMBER(*,0),"POSITION" NUMBER(*,0),"COST" NUMBER(*,0),"CARDINALITY" NUMBER(*,0),"BYTES" NUMBER(*,0),"OTHER_TAG" VARCHAR2(255),"PARTITION_START" VARCHAR2(255),"PARTITION_STOP" VARCHAR2(255),"PARTITION_ID" NUMBER(*,0),"OTHER" LONG,"OTHER_XML" CLOB,"DISTRIBUTION" VARCHAR2(30),"CPU_COST" NUMBER(*,0),"IO_COST" NUMBER(*,0),"TEMP_SPACE" NUMBER(*,0),"ACCESS_PREDICATES" VARCHAR2(4000),"FILTER_PREDICATES" VARCHAR2(4000),"PROJECTION" VARCHAR2(4000),"TIME" NUMBER(*,0),"QBLOCK_NAME" VARCHAR2(30)) ON COMMIT PRESERVE ROWS2、測試explain plan與PLAN_TABLE$
2.1 使用explain plan對目標SQL解析
SQL>
select sid from v$mystat where rownum<2;SID
----------23explain plan for SELECT OBJECT_ID, OBJECT_NAME, OBJECT_TYPE FROM TEST1 WHERE OBJECT_TYPE = 'TABLE';Explained.2.2 查看PLAN_TABLE$信息
SQL>
set linesize 1000
col OPERATION for a20
col OPTIONS for a20
col OBJECT_NAME for a20
select operation,options,object_name,id,cardinality,cost from sys.plan_table$;OPERATION OPTIONS OBJECT_NAME ID CARDINALITY COST
-------------------- -------------------- -------------------- ---------- ----------- ----------
SELECT STATEMENT 0 2013 336
TABLE ACCESS FULL TEST1 1 2013 3362.3 查看session、事務和對象關系
- 當前session產生的事務
SQL> select saddr from v$session where sid=23;SADDR
----------------
00000000924403A0select count(1) from v$transaction where SES_ADDR='00000000924403A0';COUNT(1)
----------1- 查看產生事務的對象
select object_id from v$locked_object where session_id=23;OBJECT_ID
----------5187select owner,object_name from dba_objects where object_id=5187;OWNER OBJECT_NAME
------------------------------ --------------------
SYS PLAN_TABLE$- 對比其它事務
SQL> select sid from v$mystat where rownum<2;SID
----------35SQL> SQL> select count(1) from sys.plan_table$;COUNT(1)
----------0其它事務中沒有任何執行計劃相關信息。
從上面的實驗可能看出：在Oracle 10g及以上版本中，explain plan命令執行后會將解析目標SQL所產生的執行計劃具體步驟寫入 PLAN_TABLE$表，而 select * from table(dbms_xplan.display) 則是從 PLAN_TABLE$ 表中以格式化方式展示這些執行步驟，幫助用戶了解SQL執行計劃，以便優化SQL。
五、EXPLAIN PLAN 的優缺點
1. ?優點?
- ?零風險?：不執行SQL，避免對生產環境造成影響。
- ?輕量級?：適合快速驗證復雜查詢（如多表關聯、子查詢）的計劃。
- ?兼容性?：適用于所有Oracle版本。
2. ?缺點?
- ?依賴統計信息?：若統計信息過期，生成的計劃可能不準確。
- ?不執行實際語句?：無法捕獲運行時問題（如鎖等待、死鎖）。
六、總結
EXPLAIN PLAN是Oracle性能調優的基礎工具，通過靜態分析幫助開發者理解優化器的決策邏輯。結合DBA_OBJECTS表的案例實踐，可快速掌握其核心用法。盡管它無法替代動態監控工具（如AWR、SQL Trace），但在SQL開發階段和敏感操作測試中具有不可替代的作用。實際應用中，需結合統計信息維護和多工具交叉驗證，才能全面解決性能問題。
)