1.SQL語句的執行計劃

使用EXPLAIN PLAN語句來確定Oracle資料庫下指定SQL語句的執行計劃，這個語句插入每一步執行計劃的行描述到指定表中。你也可使用EXPLAIN PLAN語句作為SQL跟蹤工具的一部分。

EXPLAIN PLAN命令的語法如下：

EXPLAIN PLAN
[ SET STATEMENT_ID = string ]
[ INTO [ schema. ] table_name [ @ dblink ] ]
FOR sql_statement ;

EXPLAIN PLAN的相關選下如下：

• STATEMENT_ID

SQL語句的唯一識別符號。通過使用SQL語句的識別符號，可以向一個計劃表中存入多條SQL語句。

• TABLE_NAME

儲存執行計劃的計劃表的名稱。此表必須已經存在並且與標準表結構一致。如果沒有指定計劃表名稱，EXPLAIN PLAN會嘗試使用表名PLAN_TABLE.

• SQL_STATEMENT

你想要知道其執行計劃的那條SQL語句。這條SQL語句必須是有效的。並且你也必須有足夠的許可權來執行它。這條SQL語句可以含有繫結變數。

2.計劃表

預設情況下，Oracle會將執行計劃插入如到一張名為PLAN_TABLE的表中。可以使用指令碼utlexplain.sql來建立自己的計劃表。這個指令碼位於Oracle軟體安裝目錄的子目錄$ORACLE_HMOE/rmdbs/admin/中。然而，從Oracle 10g開始，Oracle會建立一個全域性臨時表PLAN_TABLE供所有使用者使用，所以通常情況下不需要建立自己的計劃表。由於此預設的計劃表是一個全域性臨時表，所以你無法看到其他會話插入的執行計劃，你的執行計劃也會隨著自己會話的結束而自動消失。

（計劃表）

列名	型別	描述
STATEMENT_ID	VARCHAR2(30)	在EXPLAIN PLAN的SET STATEMENT_ID子句提供的SQL語句的唯一標誌符。
PLAN_ID	NUMBER	執行計劃的在全域性表plan_table中的唯一識別符號
TIMESTAMP	DATE	EXPLAN PLAN語句執行的日期和時間
REMARKS	VARCHAR2(80)	註釋
OPERATION	VARCHAR2(30)	執行的操作型別。如TABLE ACCESS,SORT或HASH JOIN
OPTIONS	VARCHAR2(225)	操作的附加資訊，例如，以TABLE SCAN為例，選項可能是FULL或BY ROWID
OBJECT_NODE	VARCHAR2(128)	如果是分散式查詢，這一列表示用於引用物件的資料庫連結名稱。如果並行查詢，它的值可能對應一個臨時的結果集。
OBJECT_OWNER	VARCHAR2(30)	物件的名字
OBJECT_NAME	VARCHAR2(30)	物件名稱
OBJECT_ALIAS	VARCHAR2(65)	物件的別名
OBJECT_INSTANCE	NUMERIC	物件在SQL語句中的位置
OBJECT_TYPE	VARCHAR2(30)	物件的型別（表，索引等）
OPTIMIZER	VARCHAR2(255)	解釋SQL語句時生效的優化器
SEARCH_COLUMNS	NUMBERIC	未使用
ID	NUMERIC	執行計劃的ID號
PARENT_ID	NUMERIC	上一個步驟的ID號
DEPTH	NUMERIC	操作的深度
POSITION	NUMERIC	如果兩個步驟有相同的父步驟，有更低POSITION值的步驟將被先執行
COST	NUMERIC	優化器估算出來的此操作的相對成本
CARDINALITY	NUMERIC	優化器預期這一步將飯後的記錄數
BYTES	NUMERIC	預計這一步將返回的位元組數
OTHER_TAG	VARCHAR2(255)	標識OTHER列中的值的型別。
PARTITION_START	VARCHAR2(255)	訪問的分割槽範圍的起始分割槽
PARTITION_STOP	VARCHAR2(255)	訪問的分割槽範圍的結束分割槽
PARTITION_ID	NUMERIC	計算PARTITION_START和PARTITION_STOP列的值對的ID
OTHER	LONG	對於分散式查詢，這列可能是包含發往遠端資料庫的SQL語句的文字。對於並行查詢，它比啊是並行從屬程式執行的SQL語句。
DISTRIBUTION	VARCHAR2(30)	描述記錄是如何從一組並行查詢從屬程式分配到後續的“消費者”從屬程式的。
CPU_COST	NUMERIC	估算出來的操作的CPU成本
IO_COST	NUMERIC	估算出來的的操作的IO成本
TEMP_SPACE	NUMERIC	估算出來的這一步操作所使用的臨時儲存的空間大小
ACCESS_PREDICATES	VARCHAR2(4000)	SQL語句中，確定如何在當前步驟中提取記錄的子句。
FILTER_PREDICATES	VARCHAR2(4000)	SQL語句中確定對見記錄進行過濾的子句路，如WHERE子句在非索引列上的條件。
PROJECTION	VARCHAR2(4000)	決定將返回的記錄的子句，通常是SELECT後面的欄位列表
TIME	NUMBER(20,2)	優化器為這一步執行估算的時間消耗
QBLOCK_NAME	VARCHAR2(30)	查詢塊的唯一識別符號。

(常見的執行計劃操作)

操 作	選 項	描 述
表的訪問路徑

TABLE ACCESS

FULL

全表掃描，他會讀取表中的每一條記錄（嚴格地說，它讀取表的高水位以內的每個資料塊）

CLUSTER

通過索引簇的鍵來訪問資料

HASH

通過散鍵列來訪問表中匹配特定的雜湊值的一條或多條記錄

BY INDEX ROWID

通過指定ROWID來訪問表中的單條記錄。ROWID是訪問單條記錄的最快捷的方式。通常，ROWID的資訊都是有一個相關的索引檢索而來

BY USER ROWID

通過提供一個繫結變數、字面變數或WHERE CURRENT OF CURSOR子句來通過ROWID進行訪問

BY GLOBAL INDEX ROWID

通過由全域性分割槽索引獲得的ROWID進行訪問

BY LOCAL INDEX ROWID

通過本地分割槽索獲得的ROWID進行訪問

SAMPLE

使用SAMPLE子句得到結果集的一個經過取樣的子集

EXTERNAL TABLE ACCESS

訪問一張外部表

RESULT CACHE

這個SQL結果集可能來自結果集快取

MAT_VIEW REWIRTE ACCESS

SQL語句被重寫以利用物化檢視

索引操作

ADN_EQUAL

合併來自一個或多個索引掃描的結果集

INDEX

UNIQUE SCAN

只返回一條記錄的地址（ROWID）的索引檢索

RANGE SCAN

返回多條記錄的ROWID的索引檢索。之所以可以這樣返回，是因為是非唯一索引或是使用了區間操作符（例如，>）

FULL SCAN

按照索引的順序掃描整個索引

KIP SCAN

搜尋碎索引鍵中哦非前導列的索引掃描

FULL SCAN(MAX/MIN)

檢索最高或最低的索引條目

FAST FULL SCAN

按照塊順序掃描索引的每個條目，可能會使用多塊讀取

DOMAIN INDEX

域索引（使用者定義的索引型別）檢索

點陣圖操作

BITMAP

CONVERSION

將ROWID轉換成點陣圖或將點陣圖轉換成ROWID

INDEX

從點陣圖中提取一個值或一個範圍的值

MERGE

合併多個點陣圖

MINUS

從一個點陣圖中減去另一個點陣圖

OR

按位（bit-wise）對兩個點陣圖做OR操作

表連線

CONNECT BY

對前一個步驟的輸出結果執行一個層次化的自聯接操作

MERGE JOIN

對前一個步驟的輸出結果執行一次合併聯接

NESTED LOOPS

對前一個步驟執行巢狀迴圈聯接。對於上層的結果集中的每一行，都會掃描下層的結果集以找到匹配的記錄

HASH JOIN

對兩個記錄源（row source）進行雜湊聯接

任何連線操作

OUTER

此連線為外聯接

任何連線操作

ANTI

此連線為反聯接

任何連線操作

SEMI

此連線為半聯接

任何連線操作

CARTESIAN

一個結果集中的每一條記錄與另一個結果中的每一條記錄進行聯接

集合操作

CONCATENATION

與顯示指定一個UNION語句一樣，多個結果集被按照同樣的方式做合併。它通常會發生在對索引列使用OR語句時

INTERSECTION

對兩個結果集進行比較，只返回在兩個結果集中都存在的記錄。通常只有顯式地使用INTERSECT子句，這個操作才會發生

MINUS

除在第二個結果中出現過的記錄外，返回一個結果中的所有記錄。它是使用MINUS集合操作符的結果

UNION-ALL

對兩個結果集進行合併，並返回兩個結果集中的所有記錄

UNION

對兩個結果集進行合併，並返回兩個結果集中的所有記錄，但是不返回重複記錄

VIEW

要麼訪問一個檢視定義，要麼建立一個臨時表來儲存結果集

其他雜項

FOR UPDATE

由於FOR UPDATE子句的原因，返回的記錄都會被鎖住

COLLECTION ITERATOR

各種

從一個表函式提取記錄的操作（也就是 FROM TABLE()）

FAST DUAL

訪問DUAL表，以避免從緩衝區高數快取中讀取

FILTER

從結果集中排除掉不匹配給定選取條件的記錄

REMOTE

通過資料庫連結訪問一個外部的資料庫

FIRST ROW

獲取查詢的第一條記錄

SEQUENCE

使用Oracle序列號生成器來獲得一個唯一的序列號

INLIST ITERATOR

對於IN列表中的每個值都執行一次下一個操作

LOAD AS SELECT

表示這是一個基於SELECT語句的直接路徑INSERT操作

FIXED TABLE

訪問固定的（X$或V$）表

FIXED INDEX

訪問固定表X$上的索引

WINDOW

BUFFER

支援分析函式（如OVER()）的內部操作

WINDOW

SORT [PUSHED]RANK

分析函式需要為實現RANK()函式執行一次排序操作

分割槽操作

PARTITION

SINGLE

訪問單個分割槽

ITERATOR

訪問多個分割槽

ALL

訪問所有分割槽

INLIST

基於IN列表中的值來訪問多個分割槽

彙總操作

COUNT

為了滿足COUNT()函式而計算結果集中的記錄數

COUNT

STOPKEY

計算結果集中的記錄數，當達到一定數量後就停止處理。這通常發生在使用了WHERE子句，並指定了一個最大值ROWNUM（例如，WHERE ROWNUM<=10）的情況下

BUFFER

SORT

對臨時結果集做的一次記憶體排序

HASH

GROUP BY

使用雜湊操作而不是排序操作實現GROUP BY

INLIST

ITERATOR

對於IN列表中的每個值都實現一次子操作

SORT

ORDER BY

為了滿足ORDER BY子句而對結果集進行排序

AGGREGATE

當在已經分好組的資料上使用分組函式是會出現此操作

JOIN

為了準備合併連線而對記錄進行排序

UNIQUE

排除重複記錄的排序操作，通常是使用DISTINCT子句的結果

GROUP BY

為GROUP BY子句對結果集進行排序分組

GROUP BY NOSORT

不需要進行排序操作的GROUP BY操作

GROUP BY ROLLUP

含有ROLLUP選項的GROUP BY操作

GROUP BY CUBE

含有CUBE選項的GROUP BY操作

3.檢視執行計劃

當SQL語句的執行計劃生成以後，我們就可以去檢視SQL語句的執行計劃了。有兩種方法可以檢視執行計劃：直接檢視計劃表和DBMS_XPLAN.DISPALY表函式。

第一種方法：

為了更好地理解計劃表中的資料，需要針對計劃表做層次查詢。通過SELECT語句的 CONNECT BY子句對PARENT_ID和ID兩列進行自連線。這種查詢語句通常的寫法如下：

select rtrim(lpad(' ', 2 * level) || rtrim(operation) || ' ' || rtrim(options)) description,
object_owner,
object_name,
cost,
cardinality,
bytes,
io_cost,
cpu_cost
from plan_table
connect by prior id = parent_id
start with id = 0

第二種方法：

與手工查詢計劃表相比，使用DBMS_XPLAN通常可以更好的結果，它的語法更加簡單，還提供了多種有用的輸出格式，並且可以利用快取的執行計劃統計資訊。

呼叫DBMS_XPLAN函式最簡單的方法就是使用 select * from table()語句，如下面的語句：

select * from table(dbms_xplan.function(options));

最常用的兩個DBMS_XPLAN函式：

DBMS_XPLAN.DISPLAY(
table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE',
statement_id IN VARCHAR2 DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL',
filter_preds IN VARCHAR2 DEFAULT NULL);

DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
child_number IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL');

4.Examples

建立emp_test表

create table emp_test as select *from emp;

create unique index EMP_TEST_U1 on EMP_TEST (empno);
create index emp_test_n1 on EMP_TEST (ename);

通過EXPLAIN PLAN語句，插入指定SQL語句的執行計劃。

SQL> explain plan set statement_id ='plan_sql_id' for select * from emp_test t where t.ename='SCOTT';

Explained

手動查詢計劃表檢視計劃：

SQL> select rtrim(lpad(' ', 2 * level) || rtrim(operation) || ' ' || rtrim(options)) description,
2 object_owner,
3 object_name,
4 cost,
5 cardinality,
6 bytes,
7 io_cost,
8 cpu_cost
9 from plan_table
10 connect by prior id = parent_id
11 start with id = 0;

DESCRIPTION OBJECT_OWNER OBJECT_NAME COST CARDINALITY BYTES IO_COST CPU_COST
-------------------------------- ------------- ------------- ------ ----------- ----- ------- ---------
SELECT STATEMENT 2 1 38 2 14733
TABLE ACCESS BY INDEX ROWID SCOTT EMP_TEST 2 1 38 2 14733
INDEX RANGE SCAN SCOTT EMP_TEST_N1 1 1 1 7321

呼叫DBMS_XPLAN函式檢視：

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1758671844
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| EMP_TEST | 1 | 38 | 2 (0)|
|* 2 | INDEX RANGE SCAN | EMP_TEST_N1 | 1 | | 1 (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."ENAME"='SCOTT')