SQL查詢語句的性能從一定程度上影響整個
數(shù)據(jù)庫的性能。很多情況下，數(shù)據(jù)庫性能的低下差不多都是不良SQL語句所引起。而SQL語句的執(zhí)行
計劃則決定了SQL語句將會采用何種方式從數(shù)據(jù)庫提取數(shù)據(jù)并返回給客戶端，本文描述的將是如何通過EXPLAIN PLAN 獲取SQL語句執(zhí)行計劃來獲
取SQL語句的執(zhí)行計劃。
一、獲取SQL語句執(zhí)行計劃的方式
1. 使用explain plan 將執(zhí)行計劃加載到表plan_table，然后查詢該表來獲取預(yù)估的執(zhí)行計劃
2. 查詢動態(tài)性能視圖v$sql_plan,v$sql_plan_statistics,v$sql_workarea 等來獲取已緩存到庫緩存中的真實執(zhí)行計劃
3. 查詢自動工作量資料庫(Automatic Workload Repository)或查詢Statspack，即從資料庫中獲取執(zhí)行計劃
4. 啟用執(zhí)行計劃跟蹤功能，即autotrace功能
5. 使用PL/SQL Developer提供的獲取執(zhí)行計劃方法
6. 使用Toad工具來獲取執(zhí)行計劃
下面主要討論使用explain plan獲取執(zhí)行計劃的方法
二、explain plan工作實質(zhì)、前提及操作方法
1. 工作實質(zhì)
將SQL語句預(yù)估的執(zhí)行計劃加載到表plan_table,是對表plan_table 執(zhí)行了DML操作，故不會執(zhí)行隱式提交
可以對select,insert,update,merge,delete,create table, create index,alter index等加載執(zhí)行計劃到plan_table
2. 前提條件
需要先創(chuàng)建plan_table，創(chuàng)建方法：@?/rdbms/admin/utlxplan
對當(dāng)前的SQL語句有執(zhí)行權(quán)限以及對依賴的對象有相應(yīng)操作的權(quán)限
3. 使用方法：
explain plan for select * from scott.emp where ename='SCOTT'; --未設(shè)置標(biāo)記位
explain plan set statement_id='TEST' for select * from scott.emp where ename='SCOTT' --設(shè)置標(biāo)記位為TEST
三、實戰(zhàn)演習(xí)
1.環(huán)境
scott@ORCL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
2.創(chuàng)建測試表演示獲取執(zhí)行計劃
scott@ORCL> create table t as select * from all_objects where rownum<=1000;
Table created.
--加載創(chuàng)建表的執(zhí)行計劃(DDL 執(zhí)行計劃)
scott@ORCL> explain plan set statement_id='T1' for create table t1 as select * from t;
Explained.
--使用下面的語句從plan_table 獲取執(zhí)行計劃
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
--創(chuàng)建測試表t1并收集統(tǒng)計信息
scott@ORCL> create table t1 nologging as select * from t;
scott@ORCL> exec dbms_stats.gather_table_stats('SCOTT','T1');
--使用explain plan加載創(chuàng)建索引的執(zhí)行計劃
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.使用自頂向下的讀取方法獲取執(zhí)行計劃
--使用explain plan加載重建索引的執(zhí)行計劃
scott@ORCL> explain plan set statement_id='A_IDX' for alter index i_t1 rebuild;
Explained.
--執(zhí)行下面的語句來獲的A_IDX的執(zhí)行計劃，其結(jié)果是從上至下來讀，從最內(nèi)側(cè)往最外側(cè)讀。
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加載查詢語句的執(zhí)行計劃
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
上面的例子的讀取方法：
執(zhí)行4.1的索引唯一掃描
將4.1的結(jié)果集返回給3.1
執(zhí)行3.2的全表掃描
將3.1和3.2步驟的結(jié)果集返回給2.1
執(zhí)行2.1的嵌套循環(huán)
返回最終結(jié)果集
注意嵌套循環(huán)的查詢方法
Oracle 從第一個行源中讀取第一行，然后和第二個行源中的所有記錄行進行比對，所有匹配的記錄放在結(jié)果集中，然后Oracle 將讀第一
個行源中的下一行。依次類推，直到第一行源中的所有行處理完畢。
4.使用構(gòu)建樹方式查看執(zhí)行計劃
scott@ORCL> delete from plan_table;
--使用explian plan加載SQL查詢執(zhí)行計劃
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查詢來生成構(gòu)建樹
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
查詢結(jié)果中的order列與opt列
order
order列的指名了ID，父ID，以及執(zhí)行計劃中這一步驟的位置。
ID列標(biāo)識了這個步驟，但并沒有說明執(zhí)行的順序
父ID表明了這個步驟中的父步驟
位置信息說明了父ID相同的子操作的執(zhí)行順序
opt
說明當(dāng)前優(yōu)化器使用的模式
分析
首先會從步驟3開始執(zhí)行，步驟3通過索引唯一掃描PK_EMP將得到的結(jié)果集返回給父步驟2
步驟2根據(jù)上一子步驟3得到的rowid訪問表EMP并將結(jié)果集返回給父步驟1
對于步驟2檢索到的每一行數(shù)據(jù)，步驟1會將deptno傳遞給步驟5
步驟5根據(jù)得到的deptno執(zhí)行索引唯一掃描并將結(jié)果集返回給步驟4
步驟4根據(jù)步驟5得到的rowid 訪問表dept，并將結(jié)果集返回給父步驟1
對于步驟3中剩余的行依次按上述方式將所有結(jié)果集返回給步驟1
步驟1將獲得的最終結(jié)果集返回給步驟0，SQL完成查詢
根據(jù)查詢返回的結(jié)果來構(gòu)建執(zhí)行計劃樹
從ID為1的列開始，作為根節(jié)點
尋找所有父ID為1的所有子ID，如本例為2和4，將其納入樹中
分別尋找以2和4為父ID的所有子ID，將其納入樹中
如此循環(huán)直到所有的ID沒有父ID
---------------
NESTED LOOP (1)
---------------
- -
- -
- -
--------- ----------
EMP (2) DEPT(4)
--------- ----------
- -
- -
--------- ----------
PK_EMP(3) PK_DEPT(5)
--------- ----------
5.通過Oracle 自帶的SQL語句執(zhí)行計劃
可以通過Oracle提供的SQl語句來獲得當(dāng)前會話最后一條SQL語句的執(zhí)行計劃
utlxpls.sql -->用于查看串行執(zhí)行計劃
utlxplp.sql -->用于查看并行執(zhí)行計劃
scott@ORCL> @?/rdbms/admin/utlxpls.sql --獲得當(dāng)前session plan_table 最后一條SQL語句的執(zhí)行計劃
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的執(zhí)行計劃
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 | |
--------------------------------------------------------------------------------------------------------------
四、總結(jié)：
1. explain plan并不執(zhí)行當(dāng)前的SQL語句，而是根據(jù)數(shù)據(jù)字典中記錄的統(tǒng)計信息獲取最佳的執(zhí)行計劃并加載到表plan_table。
2. 由于統(tǒng)計信息，執(zhí)行環(huán)境的變化，explain plan與實際的執(zhí)行計劃可能會有差異。
3. 對于運行時將較長的SQL語句，不需要等到結(jié)果輸出即可提前獲得該SQL的執(zhí)行計劃,對于生產(chǎn)環(huán)境調(diào)試情況會減輕數(shù)據(jù)庫負(fù)荷。
4. 注意set statement_id標(biāo)識符區(qū)分大小寫