解釋計劃
語句explain plan用來顯示優(yōu)化器為sql語句選擇的執(zhí)行計劃。
SQL執(zhí)行計劃
set autotrace off;
set autotrace traceonly;
explain plan for
select e.last_name||','||e.first_name as full_name,
e.phone_number,e.email,e.department_id,d.department_name,c.country_name,l.city,l.state_province,
r.region_name
from hr.employees e,hr.departments d,hr.countries c,
hr.locations l,hr.regions r
where e.department_id=d.department_id
and d.location_id=l.location_id
and l.country_id=c.country_id
and c.region_id=r.region_id;
select * from table(dbms_xplan.display);
select * from table(dbms_xplan.display(format=>'BASIC +COST +PREDICATE'));
執(zhí)行結(jié)果
SQL>
Cannot SET AUTOTRACE
Cannot SET AUTOTRACE
Explained
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2498281325
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cos
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 11766 |
|* 1 | HASH JOIN | | 106 | 11766 |
|* 2 | HASH JOIN | | 27 | 1890 |
| 3 | NESTED LOOPS | | 27 | 1512 |
| 4 | MERGE JOIN | | 27 | 1134 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 513 |
| 6 | INDEX FULL SCAN | DEPT_LOCATION_IX | 27 | |
|* 7 | SORT JOIN | | 23 | 529 |
| 8 | TABLE ACCESS FULL | LOCATIONS | 23 | 529 |
|* 9 | INDEX UNIQUE SCAN | COUNTRY_C_ID_PK | 1 | 14 |
| 10 | TABLE ACCESS FULL | REGIONS | 4 | 56 |
| 11 | TABLE ACCESS FULL | EMPLOYEES | 107 | 4387 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
2 - access("C"."REGION_ID"="R"."REGION_ID")
7 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
filter("D"."LOCATION_ID"="L"."LOCATION_ID")
9 - access("L"."COUNTRY_ID"="C"."COUNTRY_ID")
27 rows selected
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2498281325
-------------------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 (16)|
|* 1 | HASH JOIN | | 13 (16)|
|* 2 | HASH JOIN | | 10 (20)|
| 3 | NESTED LOOPS | | 6 (17)|
| 4 | MERGE JOIN | | 6 (17)|
| 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 2 (0)|
| 6 | INDEX FULL SCAN | DEPT_LOCATION_IX | 1 (0)|
|* 7 | SORT JOIN | | 4 (25)|
| 8 | TABLE ACCESS FULL | LOCATIONS | 3 (0)|
|* 9 | INDEX UNIQUE SCAN | COUNTRY_C_ID_PK | 0 (0)|
| 10 | TABLE ACCESS FULL | REGIONS | 3 (0)|
| 11 | TABLE ACCESS FULL | EMPLOYEES | 3 (0)|
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
2 - access("C"."REGION_ID"="R"."REGION_ID")
7 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
filter("D"."LOCATION_ID"="L"."LOCATION_ID")
9 - access("L"."COUNTRY_ID"="C"."COUNTRY_ID")
這兒使用了explain plan命令和sql*plus autotrace命令來生成解釋計劃輸出。使用autotrace可以自動生成計劃，使得你所要做的事件就是打開autotrace并執(zhí)行一個查詢，當使用這個方法生成查詢，并不實際執(zhí)行查詢，只產(chǎn)生預期的執(zhí)行計劃。
plan_table
解釋計劃輸出中所看到的信息是由explain plan命令生成并默認存儲在表plan_table中的。autotrace命令從所提供的dbms_xplan包中調(diào)用display函數(shù)來自動生成輸出。當使用explan plan命令時你必須手工執(zhí)行查詢。
SQL> desc 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(38)
OBJECT_TYPE VARCHAR2(30)
OPTIMIZER VARCHAR2(255)
SEARCH_COLUMNS NUMBER
ID NUMBER(38)
PARENT_ID NUMBER(38)
DEPTH NUMBER(38)
POSITION NUMBER(38)
COST NUMBER(38)
CARDINALITY NUMBER(38)
BYTES NUMBER(38)
OTHER_TAG VARCHAR2(255)
PARTITION_START VARCHAR2(255)
PARTITION_STOP VARCHAR2(255)
PARTITION_ID NUMBER(38)
OTHER LONG
OTHER_XML CLOB
DISTRIBUTION VARCHAR2(30)
CPU_COST NUMBER(38)
IO_COST NUMBER(38)
TEMP_SPACE NUMBER(38)
ACCESS_PREDICATES VARCHAR2(4000)
FILTER_PREDICATES VARCHAR2(4000)
PROJECTION VARCHAR2(4000)
TIME NUMBER(38)
QBLOCK_NAME VARCHAR2(30)
dbms_xplan.display一個非常好的特性是它可以基于每個特定的SQL語句生成的執(zhí)行計劃而自動顯示適當?shù)牧?。如果計劃中使用了分區(qū)的運算，在輸出中就會包含partition_start,partition_stop以及partition_id這些列。
| 列 | 描述 |
|---|---|
| ID | 為每個步驟分配的唯一的編號 |
| OPERATION | 這一步驟所進行的內(nèi)部運算 |
| OPTIONS | 運算列的附加說明(附于OPERATION) |
| OBJECT_NAME | 表或索引的名稱 |
| COST | 由優(yōu)化器確定的運算所需要的成本值 |
| ACCESS_PREDICATES | 用來在訪問結(jié)構(gòu)（一般為索引）中確定數(shù)據(jù)行所在位置的條件 |
| FILTER_PREDICATES | 用來在數(shù)據(jù)行被訪問后進行篩選的條件 |
分解計劃
總的來說，只有一個子運算可以分為以下３類
- 加工運算 從子運算接收一一個數(shù)據(jù)庫行集并經(jīng)過加工以后傳遞給其父運算
- 傳遞運算 只是起傳遞的作用而不對來自子運算的數(shù)據(jù)做任何修改或加工。它們基本上是用來確定某個運算的特性。VIEW運算就是傳遞運算的一個很好的例子。
- 迭代運算 表示子運算要多次執(zhí)行。通常會在這類運算上看到iterator,inlist或all等字眼。
導致解釋計劃未達到目的的原因
解釋計劃輸出可能與語句實際執(zhí)行時的使用計劃可能不一致。
- 解釋計劃是基于你使用它的時候的環(huán)境來生成的
- 解釋計劃不考慮綁定變量的數(shù)據(jù)類型
- 解釋計劃不“窺視”綁定變量的值
- explain plan只顯示原始計劃而不顯示最終的計劃。
--解釋計劃與綁定變量數(shù)據(jù)類型
--創(chuàng)建一個測試表，主鍵設(shè)置為string數(shù)據(jù)類型
drop table regions2;
create table regions2(
region_id varchar2(10) primary key,
region_name varchar2(25)
);
--插入記錄到這個表中
insert into regions2
select * from hr.regions;
--創(chuàng)建一個變量并設(shè)置值
variable regid number;
exec :regid := 1;
--得到這個查詢的解釋計劃
explain plan for
select /* DataTypeTest */ *
from regions2
where region_id =: regid;
select * from table(dbms_xplan.display(format=>'BASIC +COST +PREDICATE'));
select * from table(dbms_xplan.display_cursor(null,null,format=>'BASIC +COST +PREDICATE'));
解釋計劃不考慮綁定變量的數(shù)據(jù)類型并假設(shè)所有的綁定變量都是字符串類型的方式。對于解釋計劃來說，數(shù)據(jù)類型被認為都是一樣的。然而，當語句真正執(zhí)行時所準備的執(zhí)行計劃卻要考慮數(shù)據(jù)類型。
謂語必須嚴格匹配索引定義，否則將不會使用索引。
閱讀計劃
有３種途徑有助于閱讀和理解所有計劃：
- 學會識別和分割父子組
- 掌握計劃中運算執(zhí)行的順序
- 學會以敘述的形式閱讀計劃
解釋計劃例子
set autotrace off;
set autotrace traceonly;
explain plan for
select e.last_name||','||e.first_name as full_name,
e.phone_number,e.email,e.department_id,d.department_name,c.country_name,l.city,l.state_province,
r.region_name
from hr.employees e,hr.departments d,hr.countries c,
hr.locations l,hr.regions r
where e.department_id=d.department_id
and d.location_id=l.location_id
and l.country_id=c.country_id
and c.region_id=r.region_id;
select * from table(dbms_xplan.display(format=>'BASIC +COST +PREDICATE'));
Explained
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2498281325
-------------------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 (16)|
|* 1 | HASH JOIN | | 13 (16)|
|* 2 | HASH JOIN | | 10 (20)|
| 3 | NESTED LOOPS | | 6 (17)|
| 4 | MERGE JOIN | | 6 (17)|
| 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 2 (0)|
| 6 | INDEX FULL SCAN | DEPT_LOCATION_IX | 1 (0)|
|* 7 | SORT JOIN | | 4 (25)|
| 8 | TABLE ACCESS FULL | LOCATIONS | 3 (0)|
|* 9 | INDEX UNIQUE SCAN | COUNTRY_C_ID_PK | 0 (0)|
| 10 | TABLE ACCESS FULL | REGIONS | 3 (0)|
| 11 | TABLE ACCESS FULL | EMPLOYEES | 3 (0)|
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
2 - access("C"."REGION_ID"="R"."REGION_ID")
7 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
filter("D"."LOCATION_ID"="L"."LOCATION_ID")
9 - access("L"."COUNTRY_ID"="C"."COUNTRY_ID")
27 rows selected
第６行和第８行縮進是最多的。第６行首先執(zhí)行接下來執(zhí)行第８行，并將索引全掃描所得到的行編號傳遞給它的父步驟，第７行。繼續(xù)按照縮進程度，從縮進量最大的行到最小的行來執(zhí)行。每一步將行源數(shù)據(jù)傳給其父步驟。直到所有步驟完成為止。
訪問與篩選謂語
解釋計劃輸出中最有用的部分之一就是被稱為謂語信息的部分。在這個部分中，將會示出ACCESS_PREDICATES和FILTER_PREDICATE列。這兩列與計劃中的一行，用ID列來指示相關(guān)。計劃中每個有相關(guān)的訪問或篩選謂語的運算，在其ID旁邊都有一個星號(*)。
訪問謂語要么進行索引運算，要么進行聯(lián)結(jié)運算。訪問謂語就是一種更直接的訪問數(shù)據(jù)的方法，它只獲取表中滿足where子句的條件，或者與聯(lián)結(jié)兩張表的字段相匹配的數(shù)據(jù)。
使計劃便于閱讀
學會把計劃當做一段文字描述來進行閱讀會非常有幫助。對于很多人來說，將一系列的計劃運算轉(zhuǎn)化為一段文字描述能夠比其它方法更有助于理解計劃執(zhí)行。
為了生成這個select語句的結(jié)果集，DEPARTMENTS表中的數(shù)據(jù)行將會通過對DEPARTMENTS.DEPARTMENT_ID列進行索引全掃描來訪問。通過對LOCATIONS表使用進行全掃描，將會取出按LOCATION_ID進行排序的數(shù)據(jù)行。然后將這兩個數(shù)據(jù)行合并生成聯(lián)結(jié)后的包含DEPARTMENTS和LOCATIONS表中相匹配數(shù)據(jù)行的數(shù)據(jù)集。這個數(shù)據(jù)行集，可暫稱為dept_loc，將與再與countries表相聯(lián)結(jié)，并會迭代取出dept_loc數(shù)據(jù)表中每一行來在countries表尋找與COUNTRY_ID相匹配的行。這樣得到的數(shù)據(jù)集，暫將其稱為dept_loc_city。現(xiàn)在其中包含DEPARTMENTS，LOCATION，countries表中的數(shù)據(jù)并針會散列化到內(nèi)存中并與REGIONS表通過REGION_ID列進行聯(lián)結(jié)匹配。這個結(jié)果集暫稱為dept_loc_city_reg，又將會被散列化到內(nèi)存中并通過DEPARTMENT_ID列來與EMPLOYEES表進行匹配以生成最終的數(shù)據(jù)行的結(jié)果集。
執(zhí)行計劃
當一條SQL執(zhí)行時將會生成該語句的實際執(zhí)行計劃。在語句被硬解析后，所選的執(zhí)行計劃就會存到庫高速緩存中以便以后重用?？梢圆樵僔$SQL_PLAN查看計劃運算。V$SQL_PLAN與PALN_TABLE的基本相同。這些附加的列是：ADDRESS,HASH_VALUE,SQL_ID,SQL_ID,PLAN_HASH_VALUE,CHILD_ADDRESS以及CHILD_NUMBER。
查看最近生成的SQL語句
--獲取最近執(zhí)行的SQL語句的V$SQL查詢
select /* recentsql */
sql_id, child_number, hash_value, address, executions, sql_text
from v$sql
where parsing_user_id =
(select user_id from all_users where username = 'SCOTT')
and command_type in (2, 3, 6, 7, 189)
and upper(sql_text) not like upper('%recentsql%');
當針對V$SQL執(zhí)行查詢時，你可以看到它們現(xiàn)在被載入到了庫高速緩存中，并且每一個都有與之相聯(lián)結(jié)的標識。SQL_ID和CHILD_NUMBER列包含了最常用的獲取語句執(zhí)行計劃和執(zhí)行統(tǒng)計信息的標識信息。
查看相關(guān)執(zhí)行計劃
有好幾中方法可以用來查看任何之前己執(zhí)行過的SQL語句保存在庫高速緩存中的執(zhí)行計劃。最簡單的方法就是使用dbms_xplan.display_cursor函數(shù)。
SQL> --使用dbms_xplan.display_cursor函數(shù)
SQL> select /*+ gather_plan_statistics */ empno,ename from scott.emp where ename='tom';
執(zhí)行計劃
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 10 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='tom')
統(tǒng)計信息
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
475 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set serveroutput off;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
已選擇8行。
執(zhí)行計劃
----------------------------------------------------------
Plan hash value: 3713220770
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 |29 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR | | | | |
----------------------------------------------------------------------------------------------------
統(tǒng)計信息
----------------------------------------------------------
42 recursive calls
0 db block gets
122 consistent gets
0 physical reads
0 redo size
810 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
8 rows processed
SQL>
注意在查詢中使用的gather_plan_statistics，為了為計劃抓取行數(shù)據(jù)源執(zhí)行統(tǒng)計信息，你必須告訴oracle在語句執(zhí)行時收集這些信息。行數(shù)據(jù)源的執(zhí)行統(tǒng)計信息包括行數(shù)，一致性讀取次數(shù)，物理讀取次數(shù)，物理寫入次數(shù)，以及每一個運算在一行數(shù)據(jù)上的運行時間?？梢允褂眠@個提示來一句一句的收集這些信息，或者也可以將statistics_level實例參數(shù)設(shè)置為all。
使用沒有g(shù)ather_plan_statistics提示的dbms_xplan.display_cursor函數(shù)
SQL> select empno,ename from scott.emp where ename='tom';
EMPNO ENAME
---------- ------------------------------
8 tom
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 2zd33t69f63cm, child number 0
-------------------------------------
select empno,ename from scott.emp where ename='tom'
Plan hash value: 3956160932
-------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | |
|* 1 | TABLE ACCESS FULL| EMP | 1 |
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='tom')
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
已選擇24行。
可以看到，出現(xiàn)了一個警告信息表明無法獲取計劃統(tǒng)計信息，并告訴你如何中來收集這些信息。
收集執(zhí)行計劃統(tǒng)計信息
要想準確的知道計劃的效果如何，你需要計劃的行數(shù)據(jù)源執(zhí)行統(tǒng)計信息。這些值可以告訴你計劃中的每個運算實際上發(fā)生了什么。該數(shù)據(jù)是從V$SQL_PLAN_STATISTICS_ALL的視圖中取出來的。這個視圖將計劃的每個運算行與一行統(tǒng)計數(shù)據(jù)聯(lián)系起來。一個名為V$SQL_PLAN_STATISTICS_ALL的復合視圖包括了V$SQL_PLAN中的所有列加上V$SQL_PLAN_STATISTICS中的列以及一些包含內(nèi)存使用信息的附加列。
--V$SQL_PLAN_STATISTICS_ALL視圖描述
desc V$SQL_PLAN_STATISTICS_ALL;
Name Type Nullable Default Comments
---------------------- -------------- -------- ------- --------
ADDRESS RAW(4) Y
HASH_VALUE NUMBER Y
SQL_ID VARCHAR2(13) Y
PLAN_HASH_VALUE NUMBER Y
CHILD_ADDRESS RAW(4) Y
CHILD_NUMBER NUMBER Y
TIMESTAMP DATE Y
OPERATION VARCHAR2(30) Y
OPTIONS VARCHAR2(30) Y
OBJECT_NODE VARCHAR2(40) Y
OBJECT# NUMBER Y
OBJECT_OWNER VARCHAR2(30) Y
OBJECT_NAME VARCHAR2(30) Y
OBJECT_ALIAS VARCHAR2(65) Y
OBJECT_TYPE VARCHAR2(20) Y
OPTIMIZER VARCHAR2(20) Y
ID NUMBER Y
PARENT_ID NUMBER Y
DEPTH NUMBER Y
POSITION NUMBER Y
SEARCH_COLUMNS NUMBER Y
COST NUMBER Y
CARDINALITY NUMBER Y
BYTES NUMBER Y
OTHER_TAG VARCHAR2(35) Y
PARTITION_START VARCHAR2(64) Y
PARTITION_STOP VARCHAR2(64) Y
PARTITION_ID NUMBER Y
OTHER VARCHAR2(4000) Y
DISTRIBUTION VARCHAR2(20) Y
CPU_COST NUMBER Y
IO_COST NUMBER Y
TEMP_SPACE NUMBER Y
ACCESS_PREDICATES VARCHAR2(4000) Y
FILTER_PREDICATES VARCHAR2(4000) Y
PROJECTION VARCHAR2(4000) Y
TIME NUMBER Y
QBLOCK_NAME VARCHAR2(30) Y
REMARKS VARCHAR2(4000) Y
OTHER_XML CLOB Y
EXECUTIONS NUMBER Y
LAST_STARTS NUMBER Y
STARTS NUMBER Y
LAST_OUTPUT_ROWS NUMBER Y
OUTPUT_ROWS NUMBER Y
LAST_CR_BUFFER_GETS NUMBER Y
CR_BUFFER_GETS NUMBER Y
LAST_CU_BUFFER_GETS NUMBER Y
CU_BUFFER_GETS NUMBER Y
LAST_DISK_READS NUMBER Y
DISK_READS NUMBER Y
LAST_DISK_WRITES NUMBER Y
DISK_WRITES NUMBER Y
LAST_ELAPSED_TIME NUMBER Y
ELAPSED_TIME NUMBER Y
POLICY VARCHAR2(10) Y
ESTIMATED_OPTIMAL_SIZE NUMBER Y
ESTIMATED_ONEPASS_SIZE NUMBER Y
LAST_MEMORY_USED NUMBER Y
LAST_EXECUTION VARCHAR2(10) Y
LAST_DEGREE NUMBER Y
TOTAL_EXECUTIONS NUMBER Y
OPTIMAL_EXECUTIONS NUMBER Y
ONEPASS_EXECUTIONS NUMBER Y
MULTIPASSES_EXECUTIONS NUMBER Y
ACTIVE_TIME NUMBER Y
MAX_TEMPSEG_SIZE NUMBER Y
LAST_TEMPSEG_SIZE NUMBER Y
包含與涉及dbms_xplan.display_cursor函數(shù)輸出相關(guān)的統(tǒng)計信息的列均以前綴LAST_開頭。當使用ALLSTATS LAST格式選項時，計劃就會為其中的每一行顯示這些列的值。因此，對于每個運算，你將能夠準確的知道：
- 將會返回多少行（LAST_OUTPUT_ROWS在A-Rows列中給出）
- 發(fā)生了多少次一致性讀?。↙AST_CR_BUFFER_GETS在Buffers列中給出）
- 發(fā)生了多少次物理讀?。↙AST_DISK_READS在Reads列中給出）
- 每個步驟執(zhí)行的次數(shù)（LAST_STARTS在Starts列中給出）
根據(jù)所執(zhí)行的運算不同，還將顯示其它的一些列，但上面列出的這些是最常見的。
dbms_xplan.display_cursor的調(diào)用簽名
如果SQL_ID => null\CURSOR_CHILD_NO => null和FORMAT => ALLSTATS LAST,前兩個參數(shù)使用空值，表明需要取出上一個執(zhí)行語句的執(zhí)行計劃。因此可以執(zhí)行一個語句后，然后執(zhí)行：
set serveroutput off;
select * from regions2;
select * FROM table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
調(diào)用dbms_xplan.display_cursor函數(shù)之前執(zhí)行了SQL*Plus命令set serveroutput off。任何時候當你執(zhí)行一個語句并打開serveroutput，都會隱式地調(diào)用dbms_output.如果你沒有將serveroutput關(guān)閉，那么最后執(zhí)行的一條語句將會是這個dbms_output的調(diào)用。
標識SQL語句以便以后取回計劃
如果想取出之前的執(zhí)行過的一個語句，可以從V$SQL中取出SQL_ID和CHILD_NUMBRER。為了簡化尋找正確語句標識過程，尤其是在測試時，可以執(zhí)行的每個語句上加一個唯一的注釋來進行標識。
SQL> select /* km-emptest1 */ empno,ename,job
2 from emp
3 where job='saler';
EMPNO ENAME JOB
---------- ------------------------------ --------------------
1 litao saler
2 liqian saler
SQL>
SQL> select sql_id,CHILD_NUMBER,SQL_TEXT from v$sql
2 where sql_text like '%km-emptest1%';
SQL_ID CHILD_NUMBER
------------- ------------
SQL_TEXT
--------------------------------------------------------------------------------
ddfaq3mrpn1vy 0
select /* km-emptest1 */ empno,ename,job from emp where job='saler'
86hxc8yg7hrs3 0
select sql_id,CHILD_NUMBER,SQL_TEXT from v$sql where sql_text like '%km-emptest1
%'
fatny27bpcw9v 0
select /* km-emptest1 */ empno,ename from emp
SQL_ID CHILD_NUMBER
------------- ------------
SQL_TEXT
--------------------------------------------------------------------------------
2t51rxgu9kfk7 0
select sql_id,CHILD_NUMBER,SQL_TEXT from v$sql where sql_text like '%km-emptest1
%'
1hg38zrg7kz3w 0
select /* km-emptest1 */ empno,ename,job from emp
50w1wnmpamkw6 0
SQL_ID CHILD_NUMBER
------------- ------------
SQL_TEXT
--------------------------------------------------------------------------------
select /* km-emptest1 */ empno,ename,job from emp where job='saler'
已選擇6行。
SQL>
SQL> select * from table(dbms_xplan.display_cursor('50w1wnmpamkw6',0,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 50w1wnmpamkw6, child number 0
-------------------------------------
select /* km-emptest1 */ empno,ename,job from emp where job='saler'
Plan hash value: 3956160932
-------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | |
|* 1 | TABLE ACCESS FULL| EMP | 1 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB"='saler')
注意：查詢v$sql時，顯示了兩條語句，一條是尋找v$sql中的條目所執(zhí)行的select查詢語句，另一個是實際執(zhí)行的查詢。
自動為任何SQL語句取出執(zhí)行計劃
SQL> --自動為任何SQL語句取出執(zhí)行計劃
SQL> select /* KM-EMPTEST2 */ empno,ename
2 from emp where job='saler';
EMPNO ENAME
---------- ------------------------------
1 litao
2 liqian
SQL> get E:\bjc2016\study\pln.sql
select xplan.*
from
(select max(sql_id) keep
(dense_rank last order by last_active_time) sql_id,
max(child_number) keep
(dense_rank last order by last_active_time) child_number
FROM V$SQL
WHERE UPPER(SQL_TEXT) LIKE '%&1%'
and upper(sql_text) not like '%FROM V$SQL WHERE UPPER(SQL_TEXT) LIKE %'
) sqlinfo,
table(dbms_xplan.display_cursor(sqlinfo.sql_id, sqlinfo.child_number, 'ALLSTATS LAST')) xplan;
SQL> @'E:\bjc2016\study\pln.sql' KM-EMPTEST2
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 985sj533vz6z7, child number 0
-------------------------------------
select /* KM-EMPTEST2 */ empno,ename from emp where job='saler'
Plan hash value: 3956160932
-------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | |
|* 1 | TABLE ACCESS FULL| EMP | 1 |
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB"='saler')
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- Warning: basic plan statistics not available. These are only collected when
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system leve
24 rows selected
這個腳本返回與輸入的模式相匹配的最后執(zhí)行的一條sql語句的執(zhí)行計劃。
深入理解DBMS_XPLAN
Oracle提供DBMS_XPLAN并且可以用來簡化執(zhí)行計劃輸出的獲取和顯示。為了全面地使用該包中的所有步驟和功能，需要對一些回定的視圖擁有權(quán)限。對SELECT_CATALOG_ROLE的單一授權(quán)就可以確保擁有訪問所有對象的權(quán)限。為了恰當?shù)膱?zhí)行display和display_cursor函數(shù)，至少應(yīng)該用有V$SQL，V$SQL_PLAN，V$SESSION，V$SQL_PLAN_STATISTICS_ALL的選擇權(quán)限。
dbms_xpaln包最初只有display, 在oracle 12c中， 己有26個函數(shù)。這些函數(shù)不權(quán)可以用來展示解釋計劃輸出，而且可以用來輸出存儲在自動工作負載信息庫auomatic workload repository, AWR,SQL調(diào)試集，緩存sql游標以及sql計劃基線中的語句計劃。
７個主要的用來在上述領(lǐng)域顯示計劃的表函數(shù)分別如下：
- DISPLAY
- DISPLAY_CURSOR
- DISPLAY_AWR
- DISPLAY_SQLSET
- DISPLAY_SQL_PATCH_PLAN
- DISPLAY_SQL_PROFILE_PLAN
- DISPLAY_SQL_PLAN_BASELINE
這７個函數(shù)都返回 DBMS_XPLAN_TYPE_TABLE類型，由一個300字節(jié)的字串符組成。這個類型包含含了每個表函數(shù)用來動態(tài)顯示計劃表中的列所需的不同格式。這些函數(shù)是表函數(shù)意味著，當在select語句中使用這些函數(shù)時，你必須用table函數(shù)將返回類型轉(zhuǎn)換為正確的類型。一個表函數(shù)就是一個存儲起來，行為與通常對表的查詢類似的PL/SQL函數(shù)。其好處在于你可以函數(shù)中寫代碼來在數(shù)據(jù)返回最終結(jié)果集之前對其進行數(shù)據(jù)轉(zhuǎn)換。在查詢plan_table或v$sql_plan時，表函數(shù)可以實現(xiàn)只輸出某個給定的sql語句相關(guān)的列所需的所有動態(tài)格式，而不必努力創(chuàng)建多個查詢來處理不同的需求。
這些表函數(shù)中的每個都可以接受format參數(shù)作為輸入。format參數(shù)控制著那些信息將包含在顯示輸出中。
- basic只顯示運算名稱和選項
- typecal顯示相關(guān)信息以及在適當?shù)那闆r下可能的顯示選項，如分區(qū)和并發(fā)使用。這是默認值。
- serial與typical相同但總是排除并發(fā)信息。
- all在輸出中顯示最多的信息。
除了基本的格式參數(shù)值以外，還有一些附加的細化選項可以用來定制基值的默認行為?？梢允褂枚禾柣蚩崭穹指魜砺暶鞫鄠€關(guān)鍵字，并使用加號（+）表示包含或使用減號標識（－）表示排除某個特定的顯示元素。所有的這些選項都僅顯示相關(guān)的信息。
下面是一些可選的關(guān)鍵字。 - advanced顯示與all相同的信息再加上大綱部分和窺視的綁定值部分。
- alias顯示查詢塊名稱、對象別名部分。
- all顯示查詢塊名稱/對象別名部分，謂語部分，以及列投影部分。
- allstats*與iostats last等價。
- bytes顯示估計的字節(jié)數(shù)
- cost顯示優(yōu)化器所計算出的成本信息。
- iostats*顯示游標執(zhí)行的IO統(tǒng)計信息。
- last*權(quán)顯示最后執(zhí)行的游標執(zhí)行計劃統(tǒng)計信息（默認為all并且是可累積的）。
- memstats*為內(nèi)存密集運算如散列聯(lián)結(jié)，排序或一些類型的位圖運算顯示器內(nèi)存管理統(tǒng)計信息。
- 顯示注釋部分。
- outline顯示大納部分，將會重新生成計劃的一系列提示。
- parallel顯示并行執(zhí)行信息。
- partition顯示分區(qū)裁剪信息。
- peeked_binds顯示綁定變量值信息。
- predicate顯示謂語部分。
- projection顯示列投影部分，每一行中的那些列被傳遞給其父列以及這些列的大小。
- remote顯示分布式查詢信息。
后面有星號的關(guān)鍵字不能在dispaly函數(shù)中使用，因為它們需要使用只有在語句執(zhí)行后才會在v$sql_plan_statistics_all中存在的信息。
顯示使用format參數(shù)的選項
SQL> --顯示使用format參數(shù)的選項
SQL> explain plan for
2 select * from emp e,dept d
3 where e.deptno=d.deptno
4 and e.ename='litao';
已解釋。
SQL>
SQL> select * from table(dbms_xplan.display(format=>'all'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2709701336
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 55 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 55 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 38 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 4 | INDEX UNIQUE SCAN | SYS_C0010266 | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 17 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / E@SEL$1
4 - SEL$1 / D@SEL$1
5 - SEL$1 / D@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
3 - filter("E"."ENAME"='litao')
4 - access("E"."DEPTNO"="D"."DEPTNO")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,30],
"E"."DEPTNO"[NUMBER,22], "E"."JOB"[VARCHAR2,20], "E"."MGR"[NUMBER,22],
"E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22],
"D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,30], "D"."LOC"[VARCHAR2,30]
2 - (#keys=0) "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,30],
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"E"."DEPTNO"[NUMBER,22], "E"."JOB"[VARCHAR2,20], "E"."MGR"[NUMBER,22],
"E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22],
"D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
3 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,30], "E"."DEPTNO"[NUMBER,22]
,
"E"."JOB"[VARCHAR2,20], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],
"E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22]
4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
5 - "D"."DNAME"[VARCHAR2,30], "D"."LOC"[VARCHAR2,30]
已選擇43行。
使用allstats last-cost-bytes參數(shù)
--使用allstats last-cost-bytes參數(shù)
SQL> explain plan for
2 select empno,ename from emp e,dept d
3 where e.deptno=d.deptno
4 and e.ename='litao';
已解釋。
SQL> select * from table(dbms_xplan.display_cursor(null,null,format=>'ALLSTATS LAST -COST -BYTES'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID gfc5y4bb4s05s, child number 0
explain plan for select empno,ename from emp e,dept d where
e.deptno=d.deptno and e.ename='litao'
NOTE: cannot fetch plan for SQL_ID: gfc5y4bb4s05s, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_p
lan)
已選擇9行。
使用+peeked binds參數(shù)的輸出，顯示綁定變量的值
SQL> --使用+peeked binds參數(shù)的輸出，顯示綁定變量的值
SQL> variable v_empno number;
SQL> exec :v_empno :=1;
PL/SQL 過程已成功完成。
SQL> select empno,ename,job,mgr,sal,deptno from emp where empno = :v_empno;
EMPNO ENAME JOB MGR
---------- ------------------------------ -------------------- ----------
SAL DEPTNO
---------- ----------
1 litao saler 2 9000 1
SQL> select * from table(dbms_xplan.display_cursor(null,null,format => '+PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID ahfvq692m3xs2, child number 0
-------------------------------------
select empno,ename,job,mgr,sal,deptno from emp where empno = :v_empno
Plan hash value: 887528266
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 29 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0010262 | 1 | | 0 (0)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------| |
--------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :V_EMPNO (NUMBER): 1
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=:V_EMPNO)
已選擇24行。
使用basic+parallel+predicate參數(shù)的輸出結(jié)果，顯示了并行查詢執(zhí)行計劃的詳細信息
SQL> --使用basic+parallel+predicate參數(shù)的輸出結(jié)果，顯示了并行查詢執(zhí)行計劃的詳細信息
SQL> select /*+ parallel(d,4) parallel (4,4) */
2 d.dname,avg(e.sal),max(e.sal)
3 from dept d,emp e
4 where d.deptno=e.deptno
5 group by d.dname
6 order by max(e.sal), avg(e.sal) desc;
DNAME AVG(E.SAL) MAX(E.SAL)
------------------------------ ---------- ----------
develop 9000 9000
sales 9000 9000
finance 9000 9000
hr 7400 9000
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'BASIC +PARALLEL +PREDICATE'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+ parallel(d,4) parallel (4,4) */
d.dname,avg(e.sal),max(e.sal) from dept d,emp e where d.deptno=e.deptno
group by d.dname order by max(e.sal), avg(e.sal) desc
Plan hash value: 3127499263
-------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
|
| 1 | PX COORDINATOR | | | |
|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 2 | PX SEND QC (ORDER) | :TQ10002 | Q1,02 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | Q1,02 | PCWP | |
| 5 | PX SEND RANGE | :TQ10001 | Q1,01 | P->P | R
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
ANGE |
| 6 | HASH GROUP BY | | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | Q1,01 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | Q1,00 | P->P | HASH |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 9 | HASH GROUP BY | | Q1,00 | PCWP | |
| 10 | NESTED LOOPS | | Q1,00 | PCWP | |
| 11 | NESTED LOOPS | | Q1,00 | PCWP | |
| 12 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 13 | TABLE ACCESS FULL | EMP | Q1,00 | PCWP | |
|* 14 | INDEX UNIQUE SCAN | SYS_C0010266 | Q1,00 | PCWP | |
| 15 | TABLE ACCESS BY INDEX ROWID| DEPT | Q1,00 | PCWP | |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
13 - access(:Z>=:Z AND :Z<=:Z)
14 - access("D"."DEPTNO"="E"."DEPTNO")
已選擇35行。
使用sql監(jiān)控報告
自從Oracle 11g中引入SQL監(jiān)控報告以來，就有另一種查看執(zhí)行計劃中的數(shù)據(jù)源執(zhí)行統(tǒng)計信息的方法，從而搞清楚時間和資源是如何在特定的sql語句中使用的。它與dbms_xpaln.display_cursor相似，但還具備一些特有的特性。SQL監(jiān)控報告在可用性方面最值得注意的一點是，即使statistics_level參數(shù)設(shè)置為typical，也會默認開啟監(jiān)控報告。此外在消耗cpu或io時間超過５秒的語句，以及使用并行執(zhí)行的語句，都將自動被監(jiān)控。
dbms_xplan.report_sql_monitor調(diào)用的內(nèi)容
顯示一份sql監(jiān)控報告
--顯示一份sql監(jiān)控報告
select /*+ monitor */ * from employees2 where email like 'S%';
select dbms_sqltune.report_sql_monitor() from dual;
使用計劃信息解決問題
確定索引缺失
如果缺少一個索引或某個索引是次優(yōu)的，可以從計劃中看出來。
使用計劃信息確定次優(yōu)索引
--create index hr.EMP_JOB_IX on hr.employees(job_id);
select /* KM1 */
job_id, department_id, last_name
from hr.employees
where job_id = 'SA_REP'
and department_id is null;
@E:\bjc2016\study\pln KM1;
在索引中增加一列來優(yōu)化查詢
--在索引中增加一列來優(yōu)化查詢
create index hr.emp_job_dept_ix on hr.employees(department_id,job_id) compute statistics;
select /* KM2 */ job_id, department_id, last_name from hr.employees
where job_id = 'SA_REP' and department_id is null;
@E:\bjc2016\study\pln KM2;
不管計劃有多復雜，要分辯出索引的缺失或者次優(yōu)索引的方法就是要找具有較小的A-Rows值，也就是與表中的總行數(shù)相比較來說比較小的篩選性謂語的全且訪問運算。優(yōu)化后可能通過索引rowid進行父表訪問的A-Rows值相比較具有較大的A-Rows值的索引掃描運算。
想提高效率就需要建立更好索引，如果直接將兩列都放到索引的定義中去，索引就會只會返回一個行ID，父步驟也就不必訪問最終會被舍棄的數(shù)據(jù)所在的數(shù)據(jù)塊了。
沒有索引的情況下，優(yōu)化器的唯一選擇就是進行全表掃描。隨著數(shù)據(jù)的增大，響應(yīng)時間會持續(xù)下降。
--使用計劃信息來確定缺少的索引
select /* KM3 */ last_name,phone_number from hr.employees
where phone_number='650.507.9822';
@E:\bjc2016\study\pln KM3;
column column_name format a22 heading 'Column name';
column index_name format heading 'Index name';
column column_position format 999999999 heading 'Pos#';
column descend format a5 heading 'Order';
column column_expression format a40 heading 'Expression';
break on index_name skip 1;
--檢查目前的索引
select lower(b.index_name) index_name,b.COLUMN_POSITION,b.DESCEND,lower(b.COLUMN_NAME) column_name
from all_ind_columns b
where b.TABLE_OWNER='HR'
and b.TABLE_NAME='EMPLOYEES'
order by b.INDEX_NAME,b.COLUMN_POSITION,b.COLUMN_NAME;
查詢 all_ind_columns 視圖可以驗證存在那些索引，這些索引建立在那些列上。phone_number列上沒有索引，因此傳化器除了全表掃描篩選相匹配的數(shù)據(jù)行外，別無選擇。
--建立索引來提升性能
create index hr.emp_phone_ix on hr.employees(phone_number) compute statistics;
set serveroutput off;
select /* KM4 */ last_name,phone_number from hr.employees
where phone_number='650.507.9822';
@E:\bjc2016\study\pln KM4;
有了索引以后，優(yōu)化器就選擇使用這個索引，訪問能夠滿足查詢條件的數(shù)據(jù)行?，F(xiàn)在不需要檢驗表中所有的行，僅僅通過索引訪問了與所需查詢的電話號碼相匹配的那一行數(shù)據(jù)。
小結(jié)
小結(jié)
