解釋計(jì)劃
語句explain plan用來顯示優(yōu)化器為sql語句選擇的執(zhí)行計(jì)劃。
SQL執(zhí)行計(jì)劃
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命令來生成解釋計(jì)劃輸出。使用autotrace可以自動(dòng)生成計(jì)劃，使得你所要做的事件就是打開autotrace并執(zhí)行一個(gè)查詢，當(dāng)使用這個(gè)方法生成查詢，并不實(shí)際執(zhí)行查詢，只產(chǎn)生預(yù)期的執(zhí)行計(jì)劃。
plan_table
解釋計(jì)劃輸出中所看到的信息是由explain plan命令生成并默認(rèn)存儲(chǔ)在表plan_table中的。autotrace命令從所提供的dbms_xplan包中調(diào)用display函數(shù)來自動(dòng)生成輸出。當(dāng)使用explan plan命令時(shí)你必須手工執(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一個(gè)非常好的特性是它可以基于每個(gè)特定的SQL語句生成的執(zhí)行計(jì)劃而自動(dòng)顯示適當(dāng)?shù)牧?。如果?jì)劃中使用了分區(qū)的運(yùn)算，在輸出中就會(huì)包含partition_start,partition_stop以及partition_id這些列。
| 列 | 描述 |
|---|---|
| ID | 為每個(gè)步驟分配的唯一的編號(hào) |
| OPERATION | 這一步驟所進(jìn)行的內(nèi)部運(yùn)算 |
| OPTIONS | 運(yùn)算列的附加說明(附于OPERATION) |
| OBJECT_NAME | 表或索引的名稱 |
| COST | 由優(yōu)化器確定的運(yùn)算所需要的成本值 |
| ACCESS_PREDICATES | 用來在訪問結(jié)構(gòu)（一般為索引）中確定數(shù)據(jù)行所在位置的條件 |
| FILTER_PREDICATES | 用來在數(shù)據(jù)行被訪問后進(jìn)行篩選的條件 |
分解計(jì)劃
總的來說，只有一個(gè)子運(yùn)算可以分為以下３類
- 加工運(yùn)算 從子運(yùn)算接收一一個(gè)數(shù)據(jù)庫行集并經(jīng)過加工以后傳遞給其父運(yùn)算
- 傳遞運(yùn)算 只是起傳遞的作用而不對來自子運(yùn)算的數(shù)據(jù)做任何修改或加工。它們基本上是用來確定某個(gè)運(yùn)算的特性。VIEW運(yùn)算就是傳遞運(yùn)算的一個(gè)很好的例子。
- 迭代運(yùn)算 表示子運(yùn)算要多次執(zhí)行。通常會(huì)在這類運(yùn)算上看到iterator,inlist或all等字眼。
導(dǎo)致解釋計(jì)劃未達(dá)到目的的原因
解釋計(jì)劃輸出可能與語句實(shí)際執(zhí)行時(shí)的使用計(jì)劃可能不一致。
- 解釋計(jì)劃是基于你使用它的時(shí)候的環(huán)境來生成的
- 解釋計(jì)劃不考慮綁定變量的數(shù)據(jù)類型
- 解釋計(jì)劃不“窺視”綁定變量的值
- explain plan只顯示原始計(jì)劃而不顯示最終的計(jì)劃。
--解釋計(jì)劃與綁定變量數(shù)據(jù)類型
--創(chuàng)建一個(gè)測試表，主鍵設(shè)置為string數(shù)據(jù)類型
drop table regions2;
create table regions2(
region_id varchar2(10) primary key,
region_name varchar2(25)
);
--插入記錄到這個(gè)表中
insert into regions2
select * from hr.regions;
--創(chuàng)建一個(gè)變量并設(shè)置值
variable regid number;
exec :regid := 1;
--得到這個(gè)查詢的解釋計(jì)劃
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'));
解釋計(jì)劃不考慮綁定變量的數(shù)據(jù)類型并假設(shè)所有的綁定變量都是字符串類型的方式。對于解釋計(jì)劃來說，數(shù)據(jù)類型被認(rèn)為都是一樣的。然而，當(dāng)語句真正執(zhí)行時(shí)所準(zhǔn)備的執(zhí)行計(jì)劃卻要考慮數(shù)據(jù)類型。
謂語必須嚴(yán)格匹配索引定義，否則將不會(huì)使用索引。
閱讀計(jì)劃
有３種途徑有助于閱讀和理解所有計(jì)劃：
- 學(xué)會(huì)識(shí)別和分割父子組
- 掌握計(jì)劃中運(yùn)算執(zhí)行的順序
- 學(xué)會(huì)以敘述的形式閱讀計(jì)劃
解釋計(jì)劃例子
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
第６行和第８行縮進(jìn)是最多的。第６行首先執(zhí)行接下來執(zhí)行第８行，并將索引全掃描所得到的行編號(hào)傳遞給它的父步驟，第７行。繼續(xù)按照縮進(jìn)程度，從縮進(jìn)量最大的行到最小的行來執(zhí)行。每一步將行源數(shù)據(jù)傳給其父步驟。直到所有步驟完成為止。
訪問與篩選謂語
解釋計(jì)劃輸出中最有用的部分之一就是被稱為謂語信息的部分。在這個(gè)部分中，將會(huì)示出ACCESS_PREDICATES和FILTER_PREDICATE列。這兩列與計(jì)劃中的一行，用ID列來指示相關(guān)。計(jì)劃中每個(gè)有相關(guān)的訪問或篩選謂語的運(yùn)算，在其ID旁邊都有一個(gè)星號(hào)(*)。
訪問謂語要么進(jìn)行索引運(yùn)算，要么進(jìn)行聯(lián)結(jié)運(yùn)算。訪問謂語就是一種更直接的訪問數(shù)據(jù)的方法，它只獲取表中滿足where子句的條件，或者與聯(lián)結(jié)兩張表的字段相匹配的數(shù)據(jù)。
使計(jì)劃便于閱讀
學(xué)會(huì)把計(jì)劃當(dāng)做一段文字描述來進(jìn)行閱讀會(huì)非常有幫助。對于很多人來說，將一系列的計(jì)劃運(yùn)算轉(zhuǎn)化為一段文字描述能夠比其它方法更有助于理解計(jì)劃執(zhí)行。
為了生成這個(gè)select語句的結(jié)果集，DEPARTMENTS表中的數(shù)據(jù)行將會(huì)通過對DEPARTMENTS.DEPARTMENT_ID列進(jìn)行索引全掃描來訪問。通過對LOCATIONS表使用進(jìn)行全掃描，將會(huì)取出按LOCATION_ID進(jìn)行排序的數(shù)據(jù)行。然后將這兩個(gè)數(shù)據(jù)行合并生成聯(lián)結(jié)后的包含DEPARTMENTS和LOCATIONS表中相匹配數(shù)據(jù)行的數(shù)據(jù)集。這個(gè)數(shù)據(jù)行集，可暫稱為dept_loc，將與再與countries表相聯(lián)結(jié)，并會(huì)迭代取出dept_loc數(shù)據(jù)表中每一行來在countries表尋找與COUNTRY_ID相匹配的行。這樣得到的數(shù)據(jù)集，暫將其稱為dept_loc_city。現(xiàn)在其中包含DEPARTMENTS，LOCATION，countries表中的數(shù)據(jù)并針會(huì)散列化到內(nèi)存中并與REGIONS表通過REGION_ID列進(jìn)行聯(lián)結(jié)匹配。這個(gè)結(jié)果集暫稱為dept_loc_city_reg，又將會(huì)被散列化到內(nèi)存中并通過DEPARTMENT_ID列來與EMPLOYEES表進(jìn)行匹配以生成最終的數(shù)據(jù)行的結(jié)果集。
執(zhí)行計(jì)劃
當(dāng)一條SQL執(zhí)行時(shí)將會(huì)生成該語句的實(shí)際執(zhí)行計(jì)劃。在語句被硬解析后，所選的執(zhí)行計(jì)劃就會(huì)存到庫高速緩存中以便以后重用?？梢圆樵僔$SQL_PLAN查看計(jì)劃運(yùn)算。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%');
當(dāng)針對V$SQL執(zhí)行查詢時(shí)，你可以看到它們現(xiàn)在被載入到了庫高速緩存中，并且每一個(gè)都有與之相聯(lián)結(jié)的標(biāo)識(shí)。SQL_ID和CHILD_NUMBER列包含了最常用的獲取語句執(zhí)行計(jì)劃和執(zhí)行統(tǒng)計(jì)信息的標(biāo)識(shí)信息。
查看相關(guān)執(zhí)行計(jì)劃
有好幾中方法可以用來查看任何之前己執(zhí)行過的SQL語句保存在庫高速緩存中的執(zhí)行計(jì)劃。最簡單的方法就是使用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í)行計(jì)劃
----------------------------------------------------------
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)計(jì)信息
----------------------------------------------------------
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í)行計(jì)劃
----------------------------------------------------------
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)計(jì)信息
----------------------------------------------------------
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，為了為計(jì)劃抓取行數(shù)據(jù)源執(zhí)行統(tǒng)計(jì)信息，你必須告訴oracle在語句執(zhí)行時(shí)收集這些信息。行數(shù)據(jù)源的執(zhí)行統(tǒng)計(jì)信息包括行數(shù)，一致性讀取次數(shù)，物理讀取次數(shù)，物理寫入次數(shù)，以及每一個(gè)運(yùn)算在一行數(shù)據(jù)上的運(yùn)行時(shí)間?？梢允褂眠@個(gè)提示來一句一句的收集這些信息，或者也可以將statistics_level實(shí)例參數(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)了一個(gè)警告信息表明無法獲取計(jì)劃統(tǒng)計(jì)信息，并告訴你如何中來收集這些信息。
收集執(zhí)行計(jì)劃統(tǒng)計(jì)信息
要想準(zhǔn)確的知道計(jì)劃的效果如何，你需要計(jì)劃的行數(shù)據(jù)源執(zhí)行統(tǒng)計(jì)信息。這些值可以告訴你計(jì)劃中的每個(gè)運(yùn)算實(shí)際上發(fā)生了什么。該數(shù)據(jù)是從V$SQL_PLAN_STATISTICS_ALL的視圖中取出來的。這個(gè)視圖將計(jì)劃的每個(gè)運(yùn)算行與一行統(tǒng)計(jì)數(shù)據(jù)聯(lián)系起來。一個(gè)名為V$SQL_PLAN_STATISTICS_ALL的復(fù)合視圖包括了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)計(jì)信息的列均以前綴LAST_開頭。當(dāng)使用ALLSTATS LAST格式選項(xiàng)時(shí)，計(jì)劃就會(huì)為其中的每一行顯示這些列的值。因此，對于每個(gè)運(yùn)算，你將能夠準(zhǔn)確的知道：
- 將會(huì)返回多少行（LAST_OUTPUT_ROWS在A-Rows列中給出）
- 發(fā)生了多少次一致性讀取（LAST_CR_BUFFER_GETS在Buffers列中給出）
- 發(fā)生了多少次物理讀?。↙AST_DISK_READS在Reads列中給出）
- 每個(gè)步驟執(zhí)行的次數(shù)（LAST_STARTS在Starts列中給出）
根據(jù)所執(zhí)行的運(yùn)算不同，還將顯示其它的一些列，但上面列出的這些是最常見的。
dbms_xplan.display_cursor的調(diào)用簽名
如果SQL_ID => null\CURSOR_CHILD_NO => null和FORMAT => ALLSTATS LAST,前兩個(gè)參數(shù)使用空值，表明需要取出上一個(gè)執(zhí)行語句的執(zhí)行計(jì)劃。因此可以執(zhí)行一個(gè)語句后，然后執(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。任何時(shí)候當(dāng)你執(zhí)行一個(gè)語句并打開serveroutput，都會(huì)隱式地調(diào)用dbms_output.如果你沒有將serveroutput關(guān)閉，那么最后執(zhí)行的一條語句將會(huì)是這個(gè)dbms_output的調(diào)用。
標(biāo)識(shí)SQL語句以便以后取回計(jì)劃
如果想取出之前的執(zhí)行過的一個(gè)語句，可以從V$SQL中取出SQL_ID和CHILD_NUMBRER。為了簡化尋找正確語句標(biāo)識(shí)過程，尤其是在測試時(shí)，可以執(zhí)行的每個(gè)語句上加一個(gè)唯一的注釋來進(jìn)行標(biāo)識(shí)。
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時(shí)，顯示了兩條語句，一條是尋找v$sql中的條目所執(zhí)行的select查詢語句，另一個(gè)是實(shí)際執(zhí)行的查詢。
自動(dòng)為任何SQL語句取出執(zhí)行計(jì)劃
SQL> --自動(dòng)為任何SQL語句取出執(zhí)行計(jì)劃
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
這個(gè)腳本返回與輸入的模式相匹配的最后執(zhí)行的一條sql語句的執(zhí)行計(jì)劃。
深入理解DBMS_XPLAN
Oracle提供DBMS_XPLAN并且可以用來簡化執(zhí)行計(jì)劃輸出的獲取和顯示。為了全面地使用該包中的所有步驟和功能，需要對一些回定的視圖擁有權(quán)限。對SELECT_CATALOG_ROLE的單一授權(quán)就可以確保擁有訪問所有對象的權(quán)限。為了恰當(dāng)?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個(gè)函數(shù)。這些函數(shù)不權(quán)可以用來展示解釋計(jì)劃輸出，而且可以用來輸出存儲(chǔ)在自動(dòng)工作負(fù)載信息庫auomatic workload repository, AWR,SQL調(diào)試集，緩存sql游標(biāo)以及sql計(jì)劃基線中的語句計(jì)劃。
７個(gè)主要的用來在上述領(lǐng)域顯示計(jì)劃的表函數(shù)分別如下：
- DISPLAY
- DISPLAY_CURSOR
- DISPLAY_AWR
- DISPLAY_SQLSET
- DISPLAY_SQL_PATCH_PLAN
- DISPLAY_SQL_PROFILE_PLAN
- DISPLAY_SQL_PLAN_BASELINE
這７個(gè)函數(shù)都返回 DBMS_XPLAN_TYPE_TABLE類型，由一個(gè)300字節(jié)的字串符組成。這個(gè)類型包含含了每個(gè)表函數(shù)用來動(dòng)態(tài)顯示計(jì)劃表中的列所需的不同格式。這些函數(shù)是表函數(shù)意味著，當(dāng)在select語句中使用這些函數(shù)時(shí)，你必須用table函數(shù)將返回類型轉(zhuǎn)換為正確的類型。一個(gè)表函數(shù)就是一個(gè)存儲(chǔ)起來，行為與通常對表的查詢類似的PL/SQL函數(shù)。其好處在于你可以函數(shù)中寫代碼來在數(shù)據(jù)返回最終結(jié)果集之前對其進(jìn)行數(shù)據(jù)轉(zhuǎn)換。在查詢plan_table或v$sql_plan時(shí)，表函數(shù)可以實(shí)現(xiàn)只輸出某個(gè)給定的sql語句相關(guān)的列所需的所有動(dòng)態(tài)格式，而不必努力創(chuàng)建多個(gè)查詢來處理不同的需求。
這些表函數(shù)中的每個(gè)都可以接受format參數(shù)作為輸入。format參數(shù)控制著那些信息將包含在顯示輸出中。
- basic只顯示運(yùn)算名稱和選項(xiàng)
- typecal顯示相關(guān)信息以及在適當(dāng)?shù)那闆r下可能的顯示選項(xiàng)，如分區(qū)和并發(fā)使用。這是默認(rèn)值。
- serial與typical相同但總是排除并發(fā)信息。
- all在輸出中顯示最多的信息。
除了基本的格式參數(shù)值以外，還有一些附加的細(xì)化選項(xiàng)可以用來定制基值的默認(rèn)行為?？梢允褂枚禾?hào)或空格分隔來聲明多個(gè)關(guān)鍵字，并使用加號(hào)（+）表示包含或使用減號(hào)標(biāo)識(shí)（－）表示排除某個(gè)特定的顯示元素。所有的這些選項(xiàng)都僅顯示相關(guān)的信息。
下面是一些可選的關(guān)鍵字。 - advanced顯示與all相同的信息再加上大綱部分和窺視的綁定值部分。
- alias顯示查詢塊名稱、對象別名部分。
- all顯示查詢塊名稱/對象別名部分，謂語部分，以及列投影部分。
- allstats*與iostats last等價(jià)。
- bytes顯示估計(jì)的字節(jié)數(shù)
- cost顯示優(yōu)化器所計(jì)算出的成本信息。
- iostats*顯示游標(biāo)執(zhí)行的IO統(tǒng)計(jì)信息。
- last*權(quán)顯示最后執(zhí)行的游標(biāo)執(zhí)行計(jì)劃統(tǒng)計(jì)信息（默認(rèn)為all并且是可累積的）。
- memstats*為內(nèi)存密集運(yùn)算如散列聯(lián)結(jié)，排序或一些類型的位圖運(yùn)算顯示器內(nèi)存管理統(tǒng)計(jì)信息。
- 顯示注釋部分。
- outline顯示大納部分，將會(huì)重新生成計(jì)劃的一系列提示。
- parallel顯示并行執(zhí)行信息。
- partition顯示分區(qū)裁剪信息。
- peeked_binds顯示綁定變量值信息。
- predicate顯示謂語部分。
- projection顯示列投影部分，每一行中的那些列被傳遞給其父列以及這些列的大小。
- remote顯示分布式查詢信息。
后面有星號(hào)的關(guān)鍵字不能在dispaly函數(shù)中使用，因?yàn)樗鼈冃枰褂弥挥性谡Z句執(zhí)行后才會(huì)在v$sql_plan_statistics_all中存在的信息。
顯示使用format參數(shù)的選項(xiàng)
SQL> --顯示使用format參數(shù)的選項(xiàng)
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í)行計(jì)劃的詳細(xì)信息
SQL> --使用basic+parallel+predicate參數(shù)的輸出結(jié)果，顯示了并行查詢執(zhí)行計(jì)劃的詳細(xì)信息
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)控報(bào)告
自從Oracle 11g中引入SQL監(jiān)控報(bào)告以來，就有另一種查看執(zhí)行計(jì)劃中的數(shù)據(jù)源執(zhí)行統(tǒng)計(jì)信息的方法，從而搞清楚時(shí)間和資源是如何在特定的sql語句中使用的。它與dbms_xpaln.display_cursor相似，但還具備一些特有的特性。SQL監(jiān)控報(bào)告在可用性方面最值得注意的一點(diǎn)是，即使statistics_level參數(shù)設(shè)置為typical，也會(huì)默認(rèn)開啟監(jiān)控報(bào)告。此外在消耗cpu或io時(shí)間超過５秒的語句，以及使用并行執(zhí)行的語句，都將自動(dòng)被監(jiān)控。
dbms_xplan.report_sql_monitor調(diào)用的內(nèi)容
顯示一份sql監(jiān)控報(bào)告
--顯示一份sql監(jiān)控報(bào)告
select /*+ monitor */ * from employees2 where email like 'S%';
select dbms_sqltune.report_sql_monitor() from dual;
使用計(jì)劃信息解決問題
確定索引缺失
如果缺少一個(gè)索引或某個(gè)索引是次優(yōu)的，可以從計(jì)劃中看出來。
使用計(jì)劃信息確定次優(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;
不管計(jì)劃有多復(fù)雜，要分辯出索引的缺失或者次優(yōu)索引的方法就是要找具有較小的A-Rows值，也就是與表中的總行數(shù)相比較來說比較小的篩選性謂語的全且訪問運(yùn)算。優(yōu)化后可能通過索引rowid進(jìn)行父表訪問的A-Rows值相比較具有較大的A-Rows值的索引掃描運(yùn)算。
想提高效率就需要建立更好索引，如果直接將兩列都放到索引的定義中去，索引就會(huì)只會(huì)返回一個(gè)行ID，父步驟也就不必訪問最終會(huì)被舍棄的數(shù)據(jù)所在的數(shù)據(jù)塊了。
沒有索引的情況下，優(yōu)化器的唯一選擇就是進(jìn)行全表掃描。隨著數(shù)據(jù)的增大，響應(yīng)時(shí)間會(huì)持續(xù)下降。
--使用計(jì)劃信息來確定缺少的索引
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 視圖可以驗(yàn)證存在那些索引，這些索引建立在那些列上。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)化器就選擇使用這個(gè)索引，訪問能夠滿足查詢條件的數(shù)據(jù)行。現(xiàn)在不需要檢驗(yàn)表中所有的行，僅僅通過索引訪問了與所需查詢的電話號(hào)碼相匹配的那一行數(shù)據(jù)。
小結(jié)
小結(jié)
