這篇文章將為大家詳細講解有關如何通過explain和dbms_xplan包分析執(zhí)行計劃，文章內容質量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。

創(chuàng)新互聯(lián)建站專注為客戶提供全方位的互聯(lián)網綜合服務，包含不限于網站建設、做網站、立山網絡推廣、成都小程序開發(fā)、立山網絡營銷、立山企業(yè)策劃、立山品牌公關、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運營等，從售前售中售后，我們都將竭誠為您服務，您的肯定，是我們最大的嘉獎；創(chuàng)新互聯(lián)建站為所有大學生創(chuàng)業(yè)者提供立山建站搭建服務，24小時服務熱線：028-86922220，官方網址：jinyejixie.com

該工具需要訪問一張?zhí)厥獾谋韕lan_table，該表用于存儲執(zhí)行計劃，在Oracle 10g之前需要用腳本utlxplan.sql創(chuàng)建：

建表：

@?\rdbms\admin\utlxplan.sql

建同義詞：

create public synonym plan_table for plan_table;

授權：

grant all on plan_table to public;

Oracle 10g之后不再需要創(chuàng)建表plan_table，而是增加了數(shù)據(jù)字典表plan_table$，然后基于plan_table$創(chuàng)建了公共同義詞供用戶使用。

explain基本語法：

explain plan [set statement_id = 'stmt_id'] for sql_statement;

explain指令的執(zhí)行結果存儲于表plan_table中，有幾種方法獲取執(zhí)行計劃的詳細信息：

1、直接查詢plan_table表

解釋計劃

explain plan for select count(*) from scott.emp;

查詢結果

col id for 999

col operation for a50

col options for a20

col object_name for a20

select id,

lpad(' ', 2 * depth) || operation || ' ' || options ||

decode(id, 0, substr(optimizer, 1, 6) || ' Cost = ' || to_char(cost)) operation,

options,

object_name,

position

from plan_table

where plan_id = (select max(plan_id) from plan_table)

order by id;

ID OPERATION OPTIONS OBJECT_NAME POSITION

---- -------------------------------------------------- -------------------- -------------------- ----------

0 SELECT STATEMENT ALL_RO Cost = 1 1

1 SORT AGGREGATE AGGREGATE 1

2 INDEX FULL SCAN FULL SCAN PK_EMP 1

或者用以下查詢，包含了執(zhí)行計劃樹的level層次關系

col "Execute Plan" for a100

select id || ' ' || parent_id || ' ' || lpad(' ', 2 * level - 1) ||

operation || ' ' || options || ' ' || object_name || ' (Cost=' || cost || ')' as "Execute Plan"

from plan_table

start with id = 0

connect by prior id = parent_id;

Execute Plan

----------------------------------------------------------------------------------------------------

0 SELECT STATEMENT (Cost=1)

1 0 SORT AGGREGATE (Cost=)

2 1 INDEX FULL SCAN PK_EMP (Cost=1)

2、通過程序包dbms_xplan獲得執(zhí)行計劃

1)獲得最近一次explain的執(zhí)行計劃

col "PLAN_TABLE_OUTPUT" for a100

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

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

Plan hash value: 2937609675

-------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |

-------------------------------------------------------------------

2)通過指定的語句ID來查詢

select * from table(dbms_xplan.display('plan_table', 'stmt_id'));

3)通過SQL_ID和子游標來查詢，該函數(shù)并不要求先做explain，顯示的信息也較詳細，另外還有一個format參數(shù)可以做更詳細的定制。

select * from table(dbms_xplan.display_cursor('sql_id', 'child_number', 'format'));

sql_id為null時顯示最近一次執(zhí)行的SQL的執(zhí)行計劃，但注意要保持set serveroutput off，否則最后一句SQL將不是你運行的SQL，child number為null，則返回所有子游標的執(zhí)行計劃。

可以通過在SQL語句中加入注釋，方便的獲取SQL_ID和CHILD_NUMBER信息，如以下SQL，先執(zhí)行一次

select /*12345*/ m.material_id, m.material_no, m.part_no, m.material_name, m.validaterule, m.validaterule2

from cmes.c_material_t m

where (regexp_like('641234HNGA080001A', m.validaterule, 'c') or

regexp_like('641234HNGA080001A', m.validaterule2, 'c'))

and m.deleted_flag = '0'

and rownum = 1;

查出它的ID：

select sql_id, child_number from v$sql where sql_text like '%12345%';

SQL_ID CHILD_NUMBER

------------- ------------

9jk2r7a64s470 0

cc274s1r7ab6w 0

因為以上包含"12345"注釋的語句被執(zhí)行了兩條，所以因取先執(zhí)行的第一條為實際的ID。

查看執(zhí)行計劃：

col PLAN_TABLE_OUTPUT for a100;

select * from table(dbms_xplan.display_cursor('9jk2r7a64s470', 0));

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

SQL_ID 9jk2r7a64s470, child number 0

-------------------------------------

select /*12345*/ m.material_id, m.material_no, m.part_no, m.material_name, m.validaterule, m.validaterule2

from cmes.c_material_t m

where (regexp_like('641234HNGA080001A', m.validaterule, 'c') or

regexp_like('641234HNGA080001A', m.validaterule2, 'c'))

and m.deleted_flag = '0'

and rownum = 1;

Plan hash value: 1524529232

-----------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 2 (100)| |

|* 1 | COUNT STOPKEY | | | | | |

|* 2 | TABLE ACCESS FULL| C_MATERIAL_T | 2 | 116 | 2 (0)| 00:00:01 |

-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM=1)

2 - filter(("M"."DELETED_FLAG"='0' AND ( REGEXP_LIKE

('641234HNGA080001A',"M"."VALIDATERULE",'c',<not feasible>)

查看更詳細的執(zhí)行計劃信息，需要先設置統(tǒng)計級別為ALL，否則沒有A-ROWS等信息

alter session set statistics_level = all;

或者在語句級別使用HINT

select /*+gather_plan_statistics*/

select m.material_id, m.material_no, m.part_no, m.material_name, m.validaterule, m.validaterule2

from cmes.c_material_t m

where (regexp_like('641234HNGA080001A', m.validaterule, 'c') or

regexp_like('641234HNGA080001A', m.validaterule2, 'c'))

and m.deleted_flag = '0'

and rownum = 1;

select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS'));

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------

SQL_ID fmu73t3umxm1r, child number 0

-------------------------------------

select m.material_id, m.material_no, m.part_no, m.material_name, m.validaterule,

m.validaterule2 from cmes.c_material_t m where (regexp_like('641234HNGA080001A',

m.validaterule, 'c') or regexp_like('641234HNGA080001A', m.validaterule2,

'c')) and m.deleted_flag = '0' and rownum = 1

Plan hash value: 1524529232

---------------------------------------------------------------------------------------------

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |

---------------------------------------------------------------------------------------------

|* 1 | COUNT STOPKEY | | 1 | | 0 |00:00:00.01 | 16 |

|* 2 | TABLE ACCESS FULL| C_MATERIAL_T | 1 | 2 | 0 |00:00:00.01 | 16 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM=1)

2 - filter((( REGEXP_LIKE ('641234HNGA080001A',"M"."VALIDATERULE",'c',???)

這里E-Rows是預估的返回行數(shù)，A-Rows是實際的返回行數(shù)。

format的高級應用可以顯示更多的信息，特別是可以顯示綁定變量的具體值，這個非常有用。以下是推薦的使用格式

select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));

allstats：iostats + memstats，iostats顯示該游標累計執(zhí)行的io統(tǒng)計信息(buffers, reads)，memstats顯示累計執(zhí)行的pga使用信息(omem 1mem used-mem)

last：僅顯示最后一次執(zhí)行的統(tǒng)計信息

advanced：顯示outline、query block name、column projection等信息

peeked_binds：打印解析時使用的綁定變量

如以下操作可以顯示非常詳細的執(zhí)行計劃和綁定變量等信息

alter session set statistics_level = all;

var a varchar2(20);

exec :a := 'EMP';

select object_id from dba_objects where object_name = :a;

select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));

dbms_xplan程序包還有一個函數(shù)display_awr可以獲取AWR報告中指定SQL_ID的執(zhí)行計劃

col PLAN_TABLE_OUTPUT for a100;

select * from table(dbms_xplan.display_awr('9jk2r7a64s470'));

該函數(shù)獲取的執(zhí)行計劃來自dba_hist_sql_plan視圖，通過歷史數(shù)據(jù)記錄，甚至一些被老化的SQL執(zhí)行計劃仍然可以被查詢到。

3、通過腳本utlxpls.sql或utlxplp.sql獲得執(zhí)行計劃

@?\rdbms\admin\utlxpls.sql

或

@?\rdbms\admin\utlxplp.sql

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

Plan hash value: 2937609675

-------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |

-------------------------------------------------------------------

看一下這兩個腳本的內容，該方法實際是調用了方法2中的程序包

get ?\rdbms\admin\utlxpls.sql

......

41* select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

42

get ?\rdbms\admin\utlxplp.sql

......

40* select * from table(dbms_xplan.display());

關于如何通過explain和dbms_xplan包分析執(zhí)行計劃就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。

文章名稱：如何通過explain和dbms_xplan包分析執(zhí)行計劃
標題來源：http://jinyejixie.com/article12/pgicgc.html

成都網站建設公司_創(chuàng)新互聯(lián)，為您提供網站導航、品牌網站建設、網站營銷、微信小程序、搜索引擎優(yōu)化、網站建設

廣告

聲明：本網站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)