如何通過explain和dbms

140次閱讀

沒有評論

共計 7145 個字符，預計需要花費 18 分鐘才能閱讀完成。

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

該工具需要訪問一張特殊的表 plan_table，該表用于存儲執行計劃，在 Oracle 10g 之前需要用腳本 utlxplan.sql 創建：

建表：

@?\rdbms\admin\utlxplan.sql

建同義詞：

create public synonym plan_table for plan_table;

授權：

grant all on plan_table to public;

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

explain 基本語法：

explain plan [set statement_id = stmt_id] for sql_statement;

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

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

或者用以下查詢，包含了執行計劃樹的 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 獲得執行計劃

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

col PLAN_TABLE_OUTPUT for a100

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

PLAN_TABLE_OUTPUT

—————————————————————————————————-

Plan hash value: 2937609675

——————————————————————-

——————————————————————-

| 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 和子游標來查詢，該函數并不要求先做 explain，顯示的信息也較詳細，另外還有一個 format 參數可以做更詳細的定制。

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

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

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

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 注釋的語句被執行了兩條，所以因取先執行的第一條為實際的 ID。

查看執行計劃：

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

———————————————————————————–

———————————————————————————–

| 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)

查看更詳細的執行計劃信息，需要先設置統計級別為 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

———————————————————————————————

———————————————————————————————

|* 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 是預估的返回行數，A-Rows 是實際的返回行數。

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

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

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

last：僅顯示最后一次執行的統計信息

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

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

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

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 程序包還有一個函數 display_awr 可以獲取 AWR 報告中指定 SQL_ID 的執行計劃

col PLAN_TABLE_OUTPUT for a100;

select * from table(dbms_xplan.display_awr( 9jk2r7a64s470

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

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

@?\rdbms\admin\utlxpls.sql

或

@?\rdbms\admin\utlxplp.sql

PLAN_TABLE_OUTPUT

—————————————————————————————————-

Plan hash value: 2937609675

——————————————————————-

——————————————————————-

| 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 包分析執行計劃就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。

正文完

發表至：數據庫

2023-07-17

轉載說明：除特殊說明外本站除技術相關以外文章皆由網絡搜集發布，轉載請注明出處。

navicat連接不上服務器的原因是什么

navicat如何查看表關系

Heartbeat+DRBD如何實現MySQL高可用性

MySQL中InnoDB鎖機制分析

MySQL常用基本操作都有哪些

久久精品人人爽,华人av在线,亚洲性视频网站,欧美专区一二三

如何通過explain和dbms