使用dbms_xplan包來獲得sql語句的執行計劃

dbms_xplan包
dbms_xplan包可以用來顯示儲存在三個不同地方的執行計劃:plan_table表,庫快取和awr.下面將會介紹dbms_xplan包中可用的函式.

輸出
這裡主要解釋透過dbms_xplan包中函式返回的資訊.下面是dbms_xplan.display_cursor輸出資訊的第一部分
SQL_ID 9nrttza3c2x2u, child number 0
————————————-
select * from scott.emp where empno=7788
Plan hash value: 2949544139

在這一部資訊中指出與sql語句相關的以下資訊:
sql_id識別父遊標.這個資訊只有當使用display_cursor和display_awr時才有

child number與sql_id一起用來識別子游標.這種資訊只有當使用display_cursor時才有

sql語句的文字只有當使用display_cursor和display_awr函式時才有

第二部分顯示的是一個表中的執行計劃雜湊值和執行計劃本身:
Plan hash value: 2949544139
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
——————————————————————————–
| 0 | SELECT STATEMENT | | | | 1 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:0
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)|
——————————————————————————–

在這個表中,評估和每一操作的執行統計都被提供.這個表中的列數直接取決於可用資訊的數量.例如,關於分割槽的資訊,並行處理或執行統計.由於這個原因相同的函式和完全相同的引數可能產生兩組不同的輸出結果.在這種情況下,你將看一下通常可用的列:
包含執行計劃的表中的列如下:
列 描述
id 在執行計劃中標識每一個操作.如果在數字的前面有一 個星號.它意味著這一行是謂詞資訊
operation 被執行操作.也叫做行資源操作
name 被執行操作的物件

查詢最佳化器評估
rows(e-rows) 評估操作所返回的行數
bytes(e-bytes) 評估操作所返回的資料量
TempSpc 評估操作使用的臨時表空間大小
cost(%cpu) 評估操作的成本.在括號中指出了cpu成本的百分比.這個值是透過執行計劃來計算的.
換句話說,父操作的成本包含了子操作的成本
Time 評估執行這個操作需要的時間(HH:MM:SS)

分割槽資訊
pstart 第一個分割槽被訪問的次數.如果在解析時未知,可以設定為key,key(I),key(mc),key(or)或
kye(sq)

pstop 最後一個分割槽被訪問的次數.如果在解析時未知,可以設定為key,key(I),key(mc),key(or)
或kye(sq)

並行和分散式處理
inst 對於分散式處理,操作使用的資料庫鏈路名稱
tq 對於並行處理,在兩個並行從屬程式之間通訊的表佇列
in-out 並行或分散式操作之間的關係
pqdistrib 對於並行處理,由生產者使用分佈處理將資料返回給消費者

執行時統計
starts 一個特定操作被執行的次數
a-rows 操作返回的實際行數
a-time 操作實際花費的時間(HH:MM:SS)

I/O統計
buffers 在執行時執行的邏輯讀取的次數
reads 在執行時執行的物理讀取的次數
writes 在執行時執行的物理寫的次數

記憶體利用統計
0Mem 評估一個最優的執行所需要的記憶體總量
1Mem 評估一次透過執行所需要的記憶體總量
0/1/m 在最優,一次透過和多次透過模型下被執行的次數

used_mem 在最後一次執行操作時使用的記憶體量
used_tmp 在最後一次執行操作時使用的臨時表空間量.它是以位元組為單位

max_tmp 操作中使用臨時表空間的最大量
下面的部分顯示的是查詢塊的名字和物件別名:
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1 / EMP@SEL$1
2 – SEL$1 / EMP@SEL$1
對於執行計劃中的每一個操作都會有一個查詢與之相關

第四部分只在oracle10gr2中可用,下面顯示是在oracle11G中的輸出資訊.它顯示了為了強制特定的執行計劃可以設定提示
這種設定提示叫做計劃概要
Outline Data
————-
/*+

PLAN_TABLE_OUTPUT
——————————————————————————–
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@”SEL$1″ “EMP”@”SEL$1″ (“EMP”.”EMPNO”))
OUTLINE_LEAF(@”SEL$1″)
ALL_ROWS
DB_VERSION(’11.2.0.1′)
OPTIMIZER_FEATURES_ENABLE(’11.2.0.1′)
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

下面的部分顯示謂詞資訊
Predicate Information (identified by operation id):
—————————————————
2 – access(“EMPNO”=7788)
Column Projection Information (identified by operation id):
———————————————————–
1 – “EMPNO”[NUMBER,22], “EMP”.”ENAME”[VARCHAR2,10],
“EMP”.”JOB”[VARCHAR2,9], “EMP”.”MGR”[NUMBER,22], “EMP”.”HIREDATE”[DATE,7]
“EMP”.”SAL”[NUMBER,22], “EMP”.”COMM”[NUMBER,22], “EMP”.”DEPTNO”[NUMBER,22

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2 - "EMP".ROWID[ROWID,10], “EMPNO”[NUMBER,22]

display函式
display函式返回儲存在plan_table表中的執行計劃.它返回的是一個例項集合dbms_xplan_type_table.
集合中的元素是例項物件型別dbms_xplan_type.唯一屬性的物件型別名叫plan_table_output是varchar2型別
這個函式有以下輸出引數:
table_name:指定plan_table表名.預設值是plan_table如果指定為null將使用預設值

statement_id:指定sql語句名字是一個可選引數.當執行explain plan語句時.預設值是null,如果使用預設值.
那麼最近插入到plan_table表中的執行計劃會被顯示(提供了filter_reds引數但沒有指定)

format:指定什麼資訊被提供在輸出資訊中.這裡有一些原始值(basic,typical,serial,all和advanced).為了
更好的控制有一些額外的修飾符(alias,bytes,cost,note,outline,parallel,partition,peeked_binds,
predicate,projection,remote和rows)可以被加到引數中.如果資訊需要被新增可以透過字元+做為可選的修飾符
例如(basic+predicate).如果資訊要被刪除可以透過字元-做為可選的修飾符(例如,typical-bytes).多個修飾符
可以同時被指定例如(typical+alias-bytes-cost).預設值是typical,原始值advanced和可用的修飾符只在
oracle10gr2中有.

filter_preds:當查詢plan_table表時應用一個限制.這個限制是基於plan table表中某一列的一個常規sql謂詞(
例如statement_id=’test’).預設值是null.如果使用預設值,那麼最近插入plan_table表中的執行計劃將會被顯示.
這個引數只能在oracle10gr2中使用.

為了使用display函式,呼叫都只需要有dbms_xplan包的execute許可權和plan_table表的select許可權

格式引數的原始值表
值 描述
basic 只會顯示最小量的資訊,基本只有操作和被執行的物件的資訊

typical 顯示最相關的資訊,除以別名,計劃概要和列投影資訊以外的資訊

serial 象typical只有並行處理的資訊不顯示

all 顯示了除了計劃概要以外的所有資訊

advanced 顯示所有可用的資訊

236

格式引數可以使用的修飾符
值 描述
alias 控制顯示的查詢塊名字和物件別名
bytes 控制在執行計劃表中列bytes的顯示
cost 控制在執行計劃表中列cost的顯示
note 控制註釋的顯示
outline 控制概要的顯示
parallel 控制並行處理資訊的顯示特別是執行計劃表中的TQ,IN-OUT和PQ Distrib列的顯示
partition 控制分割槽資訊的顯示特別是執行計劃表中的Pstart和Pstop列的顯示
peeked_binds 控制窺視繫結變數的顯示
predicate 控制過濾和訪問謂詞的顯示
projection 控制列投影資訊的顯示
remote 控制遠端執行sql語句的顯示
rows 控制執行計劃表中的rows列的顯示

下面的查詢將顯示使用不同的格式引數值basic,typical和advanced來顯示執行計劃.
SQL> explain plan for select * from scott.emp where empno=7788;

Explained

SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,’basic’));

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 2949544139
———————————————-
| Id | Operation | Name |
———————————————-
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP |
| 2 | INDEX UNIQUE SCAN | PK_EMP |
———————————————-

9 rows selected

SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,’typical’));

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 2949544139
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:0
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:0
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:0
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————
2 – access(“EMPNO”=7788)

14 rows selected

SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,’advanced’));

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 2949544139
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:0
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:0
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:0
——————————————————————————–
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1 / EMP@SEL$1
2 – SEL$1 / EMP@SEL$1
Outline Data
————-
/*+

PLAN_TABLE_OUTPUT
——————————————————————————–
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@”SEL$1″ “EMP”@”SEL$1″ (“EMP”.”EMPNO”))
OUTLINE_LEAF(@”SEL$1″)
ALL_ROWS
DB_VERSION(’11.2.0.1′)
OPTIMIZER_FEATURES_ENABLE(’11.2.0.1′)
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
—————————————————
2 – access(“EMPNO”=7788)
Column Projection Information (identified by operation id):
———————————————————–
1 – “EMPNO”[NUMBER,22], “EMP”.”ENAME”[VARCHAR2,10],
“EMP”.”JOB”[VARCHAR2,9], “EMP”.”MGR”[NUMBER,22], “EMP”.”HIREDATE”[DATE,7]
“EMP”.”SAL”[NUMBER,22], “EMP”.”COMM”[NUMBER,22], “EMP”.”DEPTNO”[NUMBER,22

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2 - "EMP".ROWID[ROWID,10], “EMPNO”[NUMBER,22]

42 rows selected

下面的查詢將顯示使用格式化引數basic和typical並使用修飾符來增加或刪除所要輸出的資訊.
SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,’basic +predicate’,NULL));

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 2949544139
———————————————-
| Id | Operation | Name |
———————————————-
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP |
|* 2 | INDEX UNIQUE SCAN | PK_EMP |
———————————————-
Predicate Information (identified by operation id):
—————————————————
2 – access(“EMPNO”=7788)

14 rows selected

SQL>
SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,’typical -bytes -note’,NULL));

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 2949544139
——————————————————————————
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | 0 (0)| 00:00:01 |
——————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – access(“EMPNO”=7788)

14 rows selected

display_cursor函式
display_cursor函式用來顯示儲存在庫快取中的執行計劃.在oracle10g中可以使用,和display一樣它返回也是例項集合
dbms_xplan_type_table.這個函式有以下輸入引數:
sql_id:指定要被返回的執行計劃的父遊標.預設值是null,如果使用預設值會顯示當前會話最後一次執行的sql語句的
執行計劃

cursor_child_no:指定子游標號與sql_id一起用來識別被返回的執行計劃的子游標.預設值是0,如果指定為null,那麼透過
sql_id找到的父遊標的所有子游標

format:指定哪些資訊被顯示.這個引數與display的格式化引數一樣.如果執行統計可用(換句話說,如查初始化引數
statistics_level設定為all或在sql語句中指定gather_plan_statistics提示),那麼也支援修飾符.它的預設值為
typical

為了使用display_cursor函式,呼叫都要對以下動態效能檢視v$session,v$sql,v$sql_plan和v$sql_plan_statistics_all
有select許可權.而select_catalog_role角色和select any dictionary系統許可權提供了這些許可權

格式化可以使用的修飾符
值 描述
allstats* 這是一個對於iostats,memstats的一個快捷方式
iostats* 控制I/O統計的顯示
last* 預設是所有執行的累積統計被顯示如果這個值被指定只有最後的執行統計被顯示
memstats* 控制PGA相關統計的顯示
runstats_last 和iostats last一樣,它只能在oracle10gr1中使用
runstats_tot 和iostats一樣,只能在oracle10gr1中使用

下面的例子顯示在查詢語句中使用gather_plan_statistics來生成執行計劃.display_cursor函式針顯示最後執行的
I/O統計.注意這裡只會顯示邏輯讀取操作(buffers)因為這裡沒有物理讀或寫:
SQL> select /*+ gather_plan_statistics */ * from scott.emp where empno=7788;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
—– ———- ——— —– ———– ——— ——— ——
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20

SQL> select a.SQL_TEXT,a.SQL_ID from v$sqlarea a where a.SQL_TEXT
2 like ‘% select /*+ gather_plan_statistics */ * from scott.emp where empno=7788 %’ and a.sql_text not like ‘%v$sqlarea%’
3 ;

SQL_TEXT SQL_ID
——————————————————————————– ————-
select /*+ gather_plan_statistics */ * from scott.emp where empno=7788 dzbmswjhdhk8t

 
SQL> SELECT * FROM table(dbms_xplan.display_cursor(‘dzbmswjhdhk8t’,0, ‘iostats last’));

PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID dzbmswjhdhk8t, child number 0
————————————-
select /*+ gather_plan_statistics */ * from scott.emp where empno=7788
Plan hash value: 2949544139
——————————————————————————–
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Ti
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:0
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 1 |00:00:0
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | 1 | 1 |00:00:0
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————
2 – access(“EMPNO”=7788)

19 rows selected

SQL>

display_awr函式
這個函式返回儲存在awr中的執行計劃.在oracle10g中可以使用.和display函式一樣,它返回的也是一個例項集合
dbms_xplan_type_table.這個函式有以下輸入引數:
sql_id:指定要被返回執行計劃的父遊標.這個引數沒有預設值

plan_hash_value:指定要被返回執行計劃的雜湊值.預設值是null.如果使用預設值,與透過sql_id標識的父遊標相關的
所有執行計劃都會被返回

db_id:指定要返回哪個資料庫的執行計劃,這個引數的預設值是null,如果使用預設值就代表是當前資料庫

format:指定哪些資訊會被顯示.與display的格式化引數相同,預設值是typical

為了能使用display_awr函式,呼叫者至少要對以下檢視dba_hist_sql_plan和dbs_hist_sqltext有select許可權.
如果db_id引數沒有指定,那麼對v$database檢視要有select許可權.select_catalog_role角色提供了這些許可權.

當對於一個特定的遊標有多個執行計劃存在時使用plan_hash_value引數進行查詢是有幫助的
SSQL> SELECT * FROM table(dbms_xplan.display_awr(’4pqx4cy7p7tnp’,2657262937,NULL,’basic’));

PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID 4pqx4cy7p7tnp
——————–
select * from v$sql_plan
Plan hash value: 2657262937
————————————–
| Id | Operation | Name |
————————————–
| 0 | SELECT STATEMENT | |
| 1 | FIXED TABLE FULL| X$KQLFXPL |
————————————–

13 rows selected
有很多情況導致一個遊標有多個執行計劃.比如象增加了一個索引或都資料發生變化(物件統計改變).基本上查詢最佳化器的工作環境隨時發生變化所以可能會生成不同的執行計劃.因此當一個sql語句執行比較長的時間又沒有報錯你對這個語句的效能產生懷疑的時候輸出的執行計劃的資訊對於診斷效能問題是有幫助的.如果在這種情況下,你可以基於輸出的資訊推斷出導致問題的原因.