导航：首页 > 数据库 >

oracle查看执行计划之DBMS_XPLAN

发表于：2025-01-23 作者：千家信息网编辑

千家信息网最后更新 2025年01月23日，使用DBMS_XPLAN包中的方法是在oracle数据库中得到目标SQL的执行计划的另一种方法。针对不同的应用场景吗，你可以选择如下四种方法中的一种：select * from table(dbms_

千家信息网最后更新 2025年01月23日oracle查看执行计划之DBMS_XPLAN

使用DBMS_XPLAN包中的方法是在oracle数据库中得到目标SQL的执行计划的另一种方法。针对不同的应用场景吗，你可以选择如下四种方法中的一种：

select * from table(dbms_xplan.display)

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

select * from table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number,'advanced'));

select * from table(dbms_xplan.display_awr('sql_id'));

方法1是执行select * from table(dbms_xplan.display)，这需要与explain plan命令配合使用，它用于查看使用explain plan命令后得到的执行计划。

方法2是执行select * from table(dbms_xplan.display_cursor(null,null,'advenced'));它用于查看刚刚执行过的sql的执行计划，这里针对dbms_xplan.display_cursor所传入的第一个和第二参数的值均为null，第三参数的值是"advanced"，第三个输入参数的值也可以是"all"，只不过用"advanced"后的显示结果会比用"all"的显示结果更详细些。

SQL> conn scott/scott;

Connected.

SQL> set linesize 800

SQL> set pagesize 900

SQL> col plan_table_output for a200

SQL> select empno,ename,dname from emp,dept where emp.deptno=dept.deptno;

EMPNO ENAME DNAME

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

7782 CLARK ACCOUNTING

7839 KING ACCOUNTING14 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID 7ww0fhpbqqt0n, child number 0

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

select empno,ename,dname from emp,dept where emp.deptno=dept.deptno

Plan hash value: 844388907

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

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

| 0 | SELECT STATEMENT | | | | 6 (100)| |

| 1 | MERGE JOIN | | 14 | 364 | 6 (17)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |

| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |

|* 4 | SORT JOIN | | 14 | 182 | 4 (25)| 00:00:01 |

| 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1

2 - SEL$1 / DEPT@SEL$1

3 - SEL$1 / DEPT@SEL$1

5 - SEL$1 / EMP@SEL$1

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

DB_VERSION('11.2.0.4')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))

FULL(@"SEL$1" "EMP"@"SEL$1")

LEADING(@"SEL$1" "DEPT"@"SEL$1" "EMP"@"SEL$1")

USE_MERGE(@"SEL$1" "EMP"@"SEL$1")

END_OUTLINE_DATA

Predicate Information (identified by operation id):

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

4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

Column Projection Information (identified by operation id):

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

1 - "DNAME"[VARCHAR2,14], "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]

2 - "DEPT"."DEPTNO"[NUMBER,22], "DNAME"[VARCHAR2,14]

3 - "DEPT".ROWID[ROWID,10], "DEPT"."DEPTNO"[NUMBER,22]

4 - (#keys=1) "EMP"."DEPTNO"[NUMBER,22], "EMPNO"[NUMBER,22],

"ENAME"[VARCHAR2,10]

5 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "EMP"."DEPTNO"[NUMBER,22]

58 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'all'));

PLAN_TABLE_OUTPUT

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

SQL_ID 7ww0fhpbqqt0n, child number 0

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

select empno,ename,dname from emp,dept where emp.deptno=dept.deptno

Plan hash value: 844388907

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

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

| 0 | SELECT STATEMENT | | | | 6 (100)| |

| 1 | MERGE JOIN | | 14 | 364 | 6 (17)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |

| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |

|* 4 | SORT JOIN | | 14 | 182 | 4 (25)| 00:00:01 |

| 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1

2 - SEL$1 / DEPT@SEL$1

3 - SEL$1 / DEPT@SEL$1

5 - SEL$1 / EMP@SEL$1

Predicate Information (identified by operation id):

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

4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

Column Projection Information (identified by operation id):

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

1 - "DNAME"[VARCHAR2,14], "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]

2 - "DEPT"."DEPTNO"[NUMBER,22], "DNAME"[VARCHAR2,14]

3 - "DEPT".ROWID[ROWID,10], "DEPT"."DEPTNO"[NUMBER,22]

4 - (#keys=1) "EMP"."DEPTNO"[NUMBER,22], "EMPNO"[NUMBER,22],

"ENAME"[VARCHAR2,10]

5 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "EMP"."DEPTNO"[NUMBER,22]

41 rows selected.

当参数传入all，显示结果中了"Qutline Data"部分的内容：

方法3是执行 select * from table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number,'advanced'));它用于查看指定sql的执行计划。这里针对方法dbms_xplan.display_cursor所传人的第一个参数的值是指定sql的sql_id或者sql hash value,第二参数的值是要看执行计划所在的child cursor number，第三个参数是advcanced或者all

SQL> select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like 'select empno,ename,dname%';

SQL_TEXT

select empno,ename,dname from emp,dept where emp.deptno=dept.deptno

SQL_ID HASH_VALUE CHILD_NUMBER

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

7ww0fhpbqqt0n 1466655764 0

只要目标sql的执行计划所在的child cursor还没有被page out出shared pool，就可以使用方法3查看该sql的执行计划：

SQL> select * from table(dbms_xplan.display_cursor('2qm0f3qgsqqyc',0,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID 2qm0f3qgsqqyc, child number 0

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

select empno,ename,dname from scott.emp,scott.dept where

scott.emp.deptno=scott.dept.deptno

Plan hash value: 844388907

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

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

| 0 | SELECT STATEMENT | | | | 6 (100)| |

| 1 | MERGE JOIN | | 14 | 364 | 6 (17)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |

| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |

|* 4 | SORT JOIN | | 14 | 182 | 4 (25)| 00:00:01 |

| 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1

2 - SEL$1 / DEPT@SEL$1

3 - SEL$1 / DEPT@SEL$1

5 - SEL$1 / EMP@SEL$1

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

DB_VERSION('11.2.0.4')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))

FULL(@"SEL$1" "EMP"@"SEL$1")

LEADING(@"SEL$1" "DEPT"@"SEL$1" "EMP"@"SEL$1")

USE_MERGE(@"SEL$1" "EMP"@"SEL$1")

END_OUTLINE_DATA

Predicate Information (identified by operation id):

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

4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

Column Projection Information (identified by operation id):

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

1 - "DNAME"[VARCHAR2,14], "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]

2 - "DEPT"."DEPTNO"[NUMBER,22], "DNAME"[VARCHAR2,14]

3 - "DEPT".ROWID[ROWID,10], "DEPT"."DEPTNO"[NUMBER,22]

4 - (#keys=1) "EMP"."DEPTNO"[NUMBER,22], "EMPNO"[NUMBER,22],

"ENAME"[VARCHAR2,10]

5 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "EMP"."DEPTNO"[NUMBER,22]

59 rows selected.

我们可以通过dbms_xplan.display_awr来得到该sql的所有历史执行计划：

SQL> set lines 100

SQL> select * from table(dbms_xplan.display_awr('cy097a90nu4fk'));

PLAN_TABLE_OUTPUT

SQL_ID cy097a90nu4fk

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

Plan hash value: 1999179007

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

Inst |IN-OUT|

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

| 0 | SELECT STATEMENT | | | | 11 (100)| |

| |

| 1 | HASH GROUP BY | | 1 | 101 | 11 (19)| 00:00:01 |

| |

| 2 | VIEW | | 1 | 101 | 10 (10)| 00:00:01 |

| |

| 3 | HASH GROUP BY | | 1 | 261 | 10 (10)| 00:00:01 |

| |

| 4 | FILTER | | | | | |

| |

| 5 | NESTED LOOPS | | | | | |

| |

| 6 | NESTED LOOPS | | 1 | 261 | 9 (0)| 00:00:01 |

| |

| 7 | REMOTE | | 1 | 176 | 5 (0)| 00:00:01 |

DBLK_~ | R->S |

| 8 | INDEX RANGE SCAN | PRODUCTSPEC_PK | 1 | | 1 (0)| 00:00:01 |

| |

| 9 | TABLE ACCESS BY INDEX ROWID| PRODUCTSPEC | 1 | 41 | 2 (0)| 00:00:01 |

| |

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

Remote SQL Information (identified by operation id):

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

7 - SELECT "A1"."EVENTTIMEKEY","A1"."EVENTID","A1"."EVENTTIME","A1"."FOLLOTID","A1"."PRODUCTSPECI

D","A1"

."PROCESSFLOWID","A1"."OLDOPERATIONID","A1"."QUANTITY","A1"."EQUIPMENTID","A2"."PROCESSFLOWID

","A2"."REWORK

TYPE" FROM "LOTHISTORY" "A1","PROCESSFLOW" "A2" WHERE "A2"."REWORKTYPE"='Normal' AND

"A1"."PROCESSFLOWID"="A2"."PROCESSFLOWID" AND "A1"."EVENTID"='TrackIn' AND "A1"."OLDOPERATION

ID"='BFOP001'

AND (SUBSTR("A1"."FOLLOTID",1,1)='V' OR SUBSTR("A1"."FOLLOTID",1,1)='P') AND

"A1"."EVENTTIME">=TO_DATE('20140918080001','YYYY-MM-DD HH24:MI:SS') AND

"A1"."EVENTTIME"<=TO_DATE('20140919080000','YYYY-MM-DD HH24:MI:SS') (accessing 'DBLK_RPT_EOL_

PRD' )

55 rows selected.

不过此方法显示执行计划中看不到执行步骤对应的谓词条件。

很赞哦！

方法参数结果三个命令所在目标不同中看传人使用方法内容历史只不过可以通过场景数据数据库是在条件数据库的安全要保护哪些东西数据库安全各自的含义是什么生产安全数据库录入数据库的安全性及管理数据库安全策略包含哪些海淀数据库安全审计系统建立农村房屋安全信息数据库易用的数据库客户端支持安全管理连接数据库失败ssl安全错误数据库的锁怎样保障安全河源卫星软件开发价格走势网络安全总监面试球员球队数据库 2014 数据库均衡负债 java是应用软件开发服务器内存什么主板支持请求服务器失败的软件是什么关于成立网络安全公司的通知 2021计算机网络技术专科教材终端计算机网络安全防护 wincc服务器连接成功无数据肇庆市网络技术防范网络技术窃密方法语音会议软件开发的未来发展原型化方法的软件开发过程 js 获取父窗口中的数据库启用根服务器云桌面服务器性能要求安卓手机聊天软件开发数据库数据清洗操作高考多少分适合学软件开发打印机服务器设置后找不到上海软件开发怎么报价软件开发岗应知应会关系数据库理论设计中起核心作用单机能用服务器cpu吗无线网络软件开发山东服务器回收近期行情海尔经营状况数据库为党委提供网络安全服务的公司

千家信息网

千家信息网

oracle查看执行计划之DBMS_XPLAN

phpmyadmin如何还原数据库

WITH语句怎么用

相关文章