SQL查詢語句的性能從一定程度上影響整個
數據庫的性能。很多情況下，數據庫性能的低下差不多都是不良SQL語句所引起。而SQL語句的執行
計劃則決定了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 | PC
WP | |
--------------------------------------------------------------------------------------------------------------
四、總結：
1. explain plan并不執行當前的SQL語句，而是根據數據字典中記錄的統計信息獲取最佳的執行計劃并加載到表plan_table。
2. 由于統計信息，執行環境的變化，explain plan與實際的執行計劃可能會有差異。
3. 對于運行時將較長的SQL語句，不需要等到結果輸出即可提前獲得該SQL的執行計劃,對于生產環境調試情況會減輕數據庫負荷。
4. 注意set statement_id標識符區分大小寫