???? 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 | PCWP | | --------------------------------------------------------------------------------------------------------------

四、總結(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ù)庫負荷。
??? ?4. 注意set statement_id標(biāo)識符區(qū)分大小寫。

?
?

使用 EXPLAIN PLAN 獲取SQL語句執(zhí)行計劃