导航：首页 > 数据库 >

如何使用dbms_xplan查看执行计划

发表于：2025-02-06 作者：千家信息网编辑

千家信息网最后更新 2025年02月06日，小编给大家分享一下如何使用dbms_xplan查看执行计划，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！概述dbms_xp

千家信息网最后更新 2025年02月06日如何使用dbms_xplan查看执行计划

小编给大家分享一下如何使用dbms_xplan查看执行计划，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

概述

dbms_xplan包提供了多样的，预定义的方式去解释执行计划。sql执行计划，执行时间等信息都存储与v$sql_plan,v$sql_plan_statistics_all视图中。此包运行的权限是执行用户，不是包的拥有者sys。

当执行diplay_awr时，需要DBA_HIST_SQL_PLAN, DBA_HIST_SQLTEXT, and V$DATABASE的查询权限

当执行display_cursor时需要V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL的查询权限

当执行display_sql_plan_baseline时，需要DBA_SQL_PLAN_BASELINES的查询权限

当执行display_sqlset时，需要ALL_SQLSET_STATEMENTS and ALL_SQLSET_PLANS的查询权限

以上的所有权限都在SELECT_CATALOG角色中，所以直接赋权SELECT_CATALOG就可以了。

dbms_xplan中有许多功能，我这里着重研究其中几个常用的

SQL> desc dbms_xplan;

…

FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE

Argument Name Type In/Out Default?

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

SQL_ID VARCHAR2 IN

PLAN_HASH_VALUE NUMBER(38) IN DEFAULT

DB_ID NUMBER(38) IN DEFAULT

FORMAT VARCHAR2 IN DEFAULT

FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE

Argument Name Type In/Out Default?

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

SQL_ID VARCHAR2 IN DEFAULT

CURSOR_CHILD_NO NUMBER(38) IN DEFAULT

FORMAT VARCHAR2 IN DEFAULT

FUNCTION DISPLAY_PLAN RETURNS CLOB

Argument Name Type In/Out Default?

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

TABLE_NAME VARCHAR2 IN DEFAULT

STATEMENT_ID VARCHAR2 IN DEFAULT

FORMAT VARCHAR2 IN DEFAULT

FILTER_PREDS VARCHAR2 IN DEFAULT

TYPE VARCHAR2 IN DEFAULT

FUNCTION DISPLAY_SQLSET RETURNS DBMS_XPLAN_TYPE_TABLE

Argument Name Type In/Out Default?

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

SQLSET_NAME VARCHAR2 IN

SQL_ID VARCHAR2 IN

PLAN_HASH_VALUE NUMBER(38) IN DEFAULT

FORMAT VARCHAR2 IN DEFAULT

SQLSET_OWNER VARCHAR2 IN DEFAULT

FUNCTION DISPLAY_SQL_PLAN_BASELINE RETURNS DBMS_XPLAN_TYPE_TABLE

Argument Name Type In/Out Default?

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

SQL_HANDLE VARCHAR2 IN DEFAULT

PLAN_NAME VARCHAR2 IN DEFAULT

FORMAT VARCHAR2 IN DEFAULT

…

dbms_xplan.display

展示执行计划内容

简单的执行一次display

SQL> explain plan for insert into scott.emp values(1111,'xplan','','','','','','');

Explained.

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

7 rows selected.

Execution Plan

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

Plan hash value: 2137789089

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

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

| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |

| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 29 (0)| 00:00:01 |

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

Statistics

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

1890 recursive calls

40 db block gets

3635 consistent gets

0 physical reads

0 redo size

1102 bytes sent via SQL*Net to client

523 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

38 sorts (memory)

0 sorts (disk)

7 rows processed

SQL> select * from scott.emp where empno=1111;

no rows selected

-explain plan for ，dbms_xplan.display都没有执行sql。explain plan for是产生sql的执行计划并保存在PLAN_TABLE表中，dbms_xplan.display从PLAN_TABLE中提取计划并展示出来。

定义

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

table_name：指定的是计划的存储表表名（不是执行表的表名）。默认是PLAN_TABLE。

statement_id：指定计划的statement_id，如果没有指定，则该值为explain plan的statement_id , 如果没有expain plan，则该值为最近一次解释的执行计划。

format：解释计划的level

? BASIC: Displays the minimum information in the plan-the operation ID, the operation name and its option.展示最少的信息

? TYPICAL: This is the default. Displays the most relevant information in the plan (operation id, name and option, #rows, #bytes and optimizer cost). Pruning, parallel and predicate information are only displayed when applicable. Excludes only PROJECTION, ALIAS and REMOTE SQL information (see below).默认值

? SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel.没有并发信息

? ALL: Maximum user level. Includes information displayed with the TYPICAL level with additional information (PROJECTION, ALIAS and information about REMOTE SQL if the operation is distributed).展示最多的信息，包含了分布式操作的远程sql执行信息

filter_preds：sql过滤，限制从计划表中返回的行。

dbms_xplan.display_awr

展示在awr中存储的sql的执行计划

定义

DBMS_XPLAN.DISPLAY_AWR(

sql_id IN VARCHAR2,

plan_hash_value IN NUMBER DEFAULT NULL,

db_id IN NUMBER DEFAULT NULL,

format IN VARCHAR2 DEFAULT TYPICAL);

sql_id:在dba_hist_sqltext中可以找到sql_id

plan_hash_value:sql执行计划的hash值。如果该值被忽略，则函数返回sql_id中的所有执行计划

db_id：database_id。如果不知道则该值为V$DATABASE视图中的database_id，也就是本地数据库。

format：与display类似。总共有4个level：BASIC，TYPICAL,SERIAL,ALL。

SQL> select * from scott.emp where rownum<=1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7369 SMITH CLERK 7902 17-DEC-80 800

-手动生成快照

SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

PL/SQL procedure successfully completed.

SQL> select sql_id,sql_text from dba_hist_sqltext where sql_text like '%rownum%';

SQL_ID

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

SQL_TEXT

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

6yzbcy3x0yr1j

insert into wrh$_dispatcher (snap_id, dbid, instance_number, name, serial#,

fsbqktj5vw6n9

select next_run_date, obj#, run_job, sch_job from (select decode(bitand(a.flags,

cv959u044n88s

select 1 from sys.aq$_subscriber_table where rownum < 2 and subscriber_id <> 0 a

…

SQL_ID

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

SQL_TEXT

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

bd3tcy3ar02px

select * from scott.emp where rownum<=1

7 rows selected.

SQL>

-指定awr中存在sql

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

PLAN_TABLE_OUTPUT

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

SQL_ID bd3tcy3ar02px

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

select * from scott.emp where rownum<=1

Plan hash value: 1973284518

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

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

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

| 1 | COUNT STOPKEY | | | | | |

PLAN_TABLE_OUTPUT

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

| 2 | TABLE ACCESS FULL| EMP | 15 | 1305 | 2 (0)| 00:00:01 |

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

Note

-----

- dynamic sampling used for this statement (level=2)

18 rows selected.

SQL>

dbms_xplan.display_cursor

展示cursor中的执行计划

定义

DBMS_XPLAN.DISPLAY_CURSOR(

sql_id IN VARCHAR2 DEFAULT NULL,

cursor_child_no IN NUMBER DEFAULT 0,

format IN VARCHAR2 DEFAULT 'TYPICAL');

sql_id

cursor_child_no:子游标标志。如果没有指定，则展示sql_id下的所有执行计划

format

默认值执行

SQL> select * from scott.emp where rownum<=2;

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7369 SMITH CLERK 7902 17-DEC-80 800

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300

-默认查询了当前session最后一次执行sql

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

PLAN_TABLE_OUTPUT

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

SQL_ID 90ud69jbjz75c, child number 0

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

select * from scott.emp where rownum<=2

Plan hash value: 1973284518

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

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

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

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

PLAN_TABLE_OUTPUT

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

| 2 | TABLE ACCESS FULL| EMP | 15 | 1305 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter(ROWNUM<=2)

Note

-----

- dynamic sampling used for this statement (level=2)

PLAN_TABLE_OUTPUT

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

23 rows selected.

SQL>

指定sql_id查找plan，首先要找到sql_id

SQL> select sql_id,sql_text from v$sqlarea where sql_text like '%rownum<=2%';

SQL_ID

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

SQL_TEXT

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

75gpskbx0uk8w

select sql_id,sql_text from v$sqlarea where sql_text like '%rownum<=2%'

90ud69jbjz75c

select * from scott.emp where rownum<=2

SQL> select * from table(dbms_xplan.display_cursor('90ud69jbjz75c'));

PLAN_TABLE_OUTPUT

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

SQL_ID 90ud69jbjz75c, child number 0

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

select * from scott.emp where rownum<=2

Plan hash value: 1973284518

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

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

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

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

PLAN_TABLE_OUTPUT

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

| 2 | TABLE ACCESS FULL| EMP | 15 | 1305 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter(ROWNUM<=2)

Note

-----

- dynamic sampling used for this statement (level=2)

PLAN_TABLE_OUTPUT

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

23 rows selected.

SQL>

如果sql不在内存中，则不能通过cursor方式查询

SQL> select * from table(dbms_xplan.display_cursor('90ud69jbjz75c'));

PLAN_TABLE_OUTPUT

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

SQL_ID: 90ud69jbjz75c, child number: 0 cannot be found

这时就可以通过生成快照的方式从awr中查询plan

以上是"如何使用dbms_xplan查看执行计划"这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注行业资讯频道！

很赞哦！

查询权限信息内容方式篇文章存储解释快照视图生成不怎么也就是内存函数分布式功能可以通过大部分常用数据库的安全要保护哪些东西数据库安全各自的含义是什么生产安全数据库录入数据库的安全性及管理数据库安全策略包含哪些海淀数据库安全审计系统建立农村房屋安全信息数据库易用的数据库客户端支持安全管理连接数据库失败ssl安全错误数据库的锁怎样保障安全数据挖掘与网络安全冲突吗触摸屏考勤机软件开发 sql中执行数据库语句整理数据分析数据库金庐软件开发中心怎么样 unraid正在连接到服务器银行柜台和软件开发中心哪个好马来西亚互联网农业科技北京网络安全工程师待遇学生宿舍数据库代码 win7网络安全模式联网娱网棋牌服务器燕山大学服务器地址怎样进入校园服务器 2008数据库修复个人信息数据库建立的时间软件开发的迭代模型服务器的连接被重置网络直播平台软件开发山西it软件开发定制企业浙江民主评议软件开发系统东西湖网络安全基地三期数据库架构设计公司网络安全教育培训成效用友怎么用数据库账号登录樟树租房软件开发梦幻可以两个服务器来回转么数据库技术支持闲置的服务器江苏悦道互联网科技有限公司

千家信息网

千家信息网

如何使用dbms_xplan查看执行计划

navicat导入dmp的方法

Oracle Client的安装步骤

相关文章