使用 EXPLAIN PLAN 獲取SQL語句執行計劃 （R0.1）
計劃則決定了SQL語句將會採用何種方式從資料庫提取資料並返回給客戶端，本文描述的將是如何透過EXPLAIN PLAN 獲取SQL語句執行計劃來獲
取SQL語句的執行計劃。
一、獲取SQL語句執行計劃的方式
1. 使用explain plan 將執行計劃載入到表plan_table，然後查詢該表來獲取預估的執行計劃
2. 查詢動態效能檢視v$sql_plan,v$sql_plan_statistics,v$sql_workarea 等來獲取已快取到庫快取中的真實執行計劃
3. 查詢自動工作量資料庫(Automatic Workload Repository)或查詢Statspack，即從資料庫中獲取執行計劃
4. 啟用執行計劃跟蹤功能，即autotrace功能
5. 使用PL/SQL Developer提供的獲取執行計劃方法
6. 使用Toad工具來獲取執行計劃
下面主要討論使用explain plan獲取執行計劃的方法
二、explain plan工作實質、前提及操作方法
1. 工作實質
將SQL語句預估的執行計劃載入到表plan_table,是對錶plan_table 執行了DML操作，故不會執行隱式提交
可以對select,insert,update,merge,delete,create table, create index,alter index等載入執行計劃到plan_table
2. 前提條件
需要先建立plan_table，建立方法：@?/rdbms/admin/utlxplan
對當前的SQL語句有執行許可權以及對依賴的物件有相應操作的許可權
3. 使用方法：
explain plan for select * from scott.emp where ename='SCOTT'; --未設定標記位
explain plan set statement_id='TEST' for select * from scott.emp where ename='SCOTT' --設定標記位為TEST
三、實戰演習
1.環境
scott@ORCL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
2.建立測試表演示獲取執行計劃
scott@ORCL> create table t as select * from all_objects where rownum<=1000;
Table created.
--載入建立表的執行計劃(DDL 執行計劃)
scott@ORCL> explain plan set statement_id='T1' for create table t1 as select * from t;
Explained.
--使用下面的語句從plan_table 獲取執行計劃
col OPERATION format a25
col OPTIONS format a25
col OBJECT_NAME format a25
SELECT lpad(' ', 2 * (LEVEL - 1)) || operation operation
,options
,object_name
,position pos
,bytes
,cost
FROM plan_table
START WITH id = 0
AND statement_id =upper( '&input_statement_id')
CONNECT BY PRIOR id = parent_id;
Enter value for input_statement_id: T1
old 9: AND statement_id =upper( '&input_statement_id')
new 9: AND statement_id =upper( 'T1')
OPERATION OPTIONS OBJECT_NAME POS BYTES COST
------------------------- ------------------------- ------------------------- ---------- ---------- ----------
CREATE TABLE STATEMENT 8 79000 8
LOAD AS SELECT T1 1
TABLE ACCESS FULL T 1 79000 5
--建立測試表t1並收集統計資訊
scott@ORCL> create table t1 nologging as select * from t;
scott@ORCL> exec dbms_stats.gather_table_stats('SCOTT','T1');
--使用explain plan載入建立索引的執行計劃
scott@ORCL> explain plan set statement_id='IDX' for create index i_t1 on t1(object_id);
Explained.
scott@ORCL> @Get_Plan
Enter value for input_statement_id: IDX
old 9: AND statement_id =upper( '&input_statement_id')
new 9: AND statement_id =upper( 'IDX')
OPERATION OPTIONS OBJECT_NAME POS BYTES COST
------------------------- ------------------------- ------------------------- ---------- ---------- ----------
CREATE INDEX STATEMENT 6 4000 6
INDEX BUILD NON UNIQUE I_T1 1
SORT CREATE INDEX 1 4000
TABLE ACCESS FULL T1 1 4000 5
scott@ORCL> CREATE INDEX i_t1 ON t1 (object_id);
scott@ORCL> delete from plan_table;
3.使用自頂向下的讀取方法獲取執行計劃
--使用explain plan載入重建索引的執行計劃
scott@ORCL> explain plan set statement_id='A_IDX' for alter index i_t1 rebuild;
Explained.
--執行下面的語句來獲的A_IDX的執行計劃，其結果是從上至下來讀，從最內側往最外側讀。
SELECT LPAD(' ', 2 * (LEVEL - 1)) || LEVEL || '.' || NVL(POSITION, 0) || ' ' ||
OPERATION || ' ' || OPTIONS || ' ' || OBJECT_NAME || ' ' ||
OBJECT_TYPE || ' ' ||
DECODE(ID, 0, STATEMENT_ID || ' Cost = ' || POSITION) || COST || ' ' ||
OBJECT_NODE "Query Plan"
FROM PLAN_TABLE
START WITH ID = 0
AND STATEMENT_ID = UPPER('&input_statement_id')
CONNECT BY PRIOR ID = PARENT_ID
AND STATEMENT_ID = UPPER('&input_statement_id');
Enter value for input_statement_id: A_IDX
old 8: AND STATEMENT_ID = UPPER('&input_statement_id')
new 8: AND STATEMENT_ID = UPPER('A_IDX')
Enter value for input_statement_id: A_IDX
old 10: AND STATEMENT_ID = UPPER('&input_statement_id')
new 10: AND STATEMENT_ID = UPPER('A_IDX')
Query Plan
---------------------------------------------------------------------------------------------
1.2 ALTER INDEX STATEMENT A_IDX Cost = 22
2.1 INDEX BUILD NON UNIQUE I_T1
3.1 SORT CREATE INDEX
4.1 INDEX FAST FULL SCAN I_T1 INDEX 2
--使用explain plan載入查詢語句的執行計劃
scott@ORCL> explain plan set statement_id='QUERY' for
2 select ename,dname
3 from emp join dept
4 on emp.deptno=dept.deptno
5 where dept.deptno=30;
Explained.
scott@ORCL> @Get_Plan2
Query Plan
--------------------------------------------------------------------------------------------
1.4 SELECT STATEMENT QUERY Cost = 44
2.1 NESTED LOOPS 4
3.1 TABLE ACCESS BY INDEX ROWID DEPT TABLE 1
4.1 INDEX UNIQUE SCAN PK_DEPT INDEX (UNIQUE) 0
3.2 TABLE ACCESS FULL EMP TABLE 3
上面的例子的讀取方法：
執行4.1的索引唯一掃描
將4.1的結果集返回給3.1
執行3.2的全表掃描
將3.1和3.2步驟的結果集返回給2.1
執行2.1的巢狀迴圈
返回最終結果集
注意巢狀迴圈的查詢方法
Oracle 從第一個行源中讀取第一行，然後和第二個行源中的所有記錄行進行比對，所有匹配的記錄放在結果集中，然後Oracle 將讀第一
個行源中的下一行。依次類推，直到第一行源中的所有行處理完畢。
4.使用構建樹方式檢視執行計劃
scott@ORCL> delete from plan_table;
--使用explian plan載入SQL查詢執行計劃
scott@ORCL> explain plan set statement_id='QUERY2' for
2 select ename,dname
3 from emp join dept
4 on emp.deptno=dept.deptno
5 where emp.empno=7788;
Explained.
--使用下面的SQl查詢來生成構建樹
col operation format a30
col options format a20
col "OBJECT NAME" format a25
col order format a10
col opt format a15
SELECT LPAD(' ', 2 * (LEVEL - 1)) || operation "OPERATION",
options "OPTIONS",
DECODE(TO_CHAR(id),
'0',
'COST = ' || NVL(TO_CHAR(position), 'n/a'),
object_name) "OBJECT NAME",
id || '-' || NVL(parent_id, 0) || '-' || NVL(position, 0) "ORDER",
SUBSTR(optimizer, 1, 6) "OPT"
FROM plan_table
START WITH id = 0
AND statement_id = UPPER('&input_statement_id')
CONNECT BY PRIOR id = parent_id
AND statement_id = UPPER('&input_statement_id');
OPERATION OPTIONS OBJECT NAME ORDER OPT
------------------------------ -------------------- ------------------------- ---------- ---------------
SELECT STATEMENT COST = 2 0-0-2 ALL_RO
NESTED LOOPS 1-0-1
TABLE ACCESS BY INDEX ROWID EMP 2-1-1 ANALYZ
INDEX UNIQUE SCAN PK_EMP 3-2-1 ANALYZ
TABLE ACCESS BY INDEX ROWID DEPT 4-1-2 ANALYZ
INDEX UNIQUE SCAN PK_DEPT 5-4-1 ANALYZ
查詢結果中的order列與opt列
order
order列的指名了ID，父ID，以及執行計劃中這一步驟的位置。
ID列標識了這個步驟，但並沒有說明執行的順序
父ID表明了這個步驟中的父步驟
位置資訊說明了父ID相同的子操作的執行順序
opt
說明當前最佳化器使用的模式
分析
首先會從步驟3開始執行，步驟3透過索引唯一掃描PK_EMP將得到的結果集返回給父步驟2
步驟2根據上一子步驟3得到的rowid訪問表EMP並將結果集返回給父步驟1
對於步驟2檢索到的每一行資料，步驟1會將deptno傳遞給步驟5
步驟5根據得到的deptno執行索引唯一掃描並將結果集返回給步驟4
步驟4根據步驟5得到的rowid 訪問表dept，並將結果集返回給父步驟1
對於步驟3中剩餘的行依次按上述方式將所有結果集返回給步驟1
步驟1將獲得的最終結果集返回給步驟0，SQL完成查詢
根據查詢返回的結果來構建執行計劃樹
從ID為1的列開始，作為根節點
尋找所有父ID為1的所有子ID，如本例為2和4，將其納入樹中
分別尋找以2和4為父ID的所有子ID，將其納入樹中
如此迴圈直到所有的ID沒有父ID
---------------
NESTED LOOP (1)
---------------
- -
- -
- -
--------- ----------
EMP (2) DEPT(4)
--------- ----------
- -
- -
--------- ----------
PK_EMP(3) PK_DEPT(5)
--------- ----------
5.透過Oracle 自帶的SQL語句執行計劃
可以透過Oracle提供的SQl語句來獲得當前會話最後一條SQL語句的執行計劃
utlxpls.sql -->用於檢視序列執行計劃
utlxplp.sql -->用於檢視並行執行計劃
scott@ORCL> @?/rdbms/admin/utlxpls.sql --獲得當前session plan_table 最後一條SQL語句的執行計劃
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash value: 2385808155
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 28 | 2 (0)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 14 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 56 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMP"."DEPTNO" IS NOT NULL)
3 - access("EMP"."EMPNO"=7788)
5 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
19 rows selected.
--載入並行SQL的執行計劃
scott@ORCL> explain plan for select /*+ parallel(t4,2) */ * from t4;
Explained.
scott@ORCL> @?/rdbms/admin/utlxplp.sql
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
Plan hash value: 128826497
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 400K| 7817K| 183 (4)| 00:00:03 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 400K| 7817K| 183 (4)| 00:00:03 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 400K| 7817K| 183 (4)| 00:00:03 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| T4 | 400K| 7817K| 183 (4)| 00:00:03 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
四、總結：
1. explain plan並不執行當前的SQL語句，而是根據資料字典中記錄的統計資訊獲取最佳的執行計劃並載入到表plan_table。
2. 由於統計資訊，執行環境的變化，explain plan與實際的執行計劃可能會有差異。
3. 對於執行時將較長的SQL語句，不需要等到結果輸出即可提前獲得該SQL的執行計劃,對於生產環境除錯情況會減輕資料庫負荷。
4. 注意set statement_id識別符號區分大小寫。
轉載地址：http://blog.csdn.net/leshami/article/details/6837771
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26613085/viewspace-1129368/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle explain plan for獲取執行計劃並不可靠.OracleAI
- 【檢視】使用V$SQL_PLAN檢視獲取曾經執行過的SQL語句執行計劃SQL
- 【Explain Plan】檢視SQL的執行計劃AISQL
- Oracle執行計劃Explain Plan 如何使用OracleAI
- 使用EXPLAIN PLAN來檢視執行計劃AI
- 獲取oracle sql語句詳細些執行計劃OracleSQL
- 用EXPLAIN PLAN 分析SQL語句AISQL
- Oracle 執行計劃（Explain Plan） 說明OracleAI
- Oracle 執行計劃（Explain Plan） 說明OracleAI
- TOAD中檢視執行計劃（Explain Plan）AI
- 配置oracle 解釋執行計劃--explain planOracleAI
- 獲取SQL執行計劃SQL
- 根據SQL Id獲得SQL語句的執行計劃SQL
- Laravel 獲取執行的sql語句LaravelSQL
- 【最佳化】explain plan for 方式存取執行計劃AI
- 獲取oracle正在處於等待狀態的sql語句的執行計劃的語句OracleSQL
- 獲得目標SQL語句執行計劃的方法SQL
- 使用dbms_xplan包來獲得sql語句的執行計劃SQL
- 【sql調優之執行計劃】獲取執行計劃SQL
- oracle 使用explain plan分析查詢語句OracleAI
- 清除SQL語句的執行計劃SQL
- Oracle 獲取SQL執行計劃方法OracleSQL
- 獲取SQL執行計劃的方式：SQL
- sql中使用函式導致explain plan for和set autotrace得到執行計劃不準確SQL函式AI
- autotrace 和explain plan for可能導致執行計劃錯誤AI
- NOTE: cannot fetch plan for SQL_ID_在plsql developer無法獲取sql執行計劃SQLDeveloper
- 通過分析SQL語句的執行計劃優化SQL語句SQL優化
- 執行計劃-1：獲取執行計劃
- oracle執行計劃的使用(EXPLAIN)OracleAI
- 檢視執行計劃方法總結之一：explain plan命令AI
- 【執行計劃】格式化EXPLAIN PLAN的輸出結果AI
- Oracle優化——如何檢視語句的準確的執行計劃（explain plan可能不是真實的）Oracle優化AI
- 在MySQL中使用explain查詢SQL的執行計劃MySqlAI
- explain執行計劃分析AI
- mysql執行計劃explainMySqlAI
- mysql explain 執行計劃MySqlAI
- MySQL——通過EXPLAIN分析SQL的執行計劃MySqlAI
- 指令碼：獲得現有語句的執行計劃指令碼