Oracle中使用DBMS_XPLAN查看执行计划的方法

目录

一、简介

二、示例

三、dbms_xplan.display_cursor返回列参数详解

四、参考资料


一、简介

DBMS_XPLAN是Oracle提供的一个用于查看SQL计划,包括执行计划和解释计划的包.DBMS_XPLAN包给出了更加简化的获取和显示计划的方式。 DBMS_XPLAN包不仅可以获取解释计划,还可以用来输出存储在AWR、SQL调试集、缓存的SQL游标,以及SQL基线中的语句计划。下面将重点讨论关于DBMS_XPLAN包在解释计划和执行计划上的应用。

首先看一个经常使用的查看某条语句的解释计划的示例:

//登录oracle
sqlplus / as sysdba
//查看执行计划
explain plan for select * from scott.emp;
select * from table(dbms_xplan.display);

上例中使用了dbms_xplan.display方法来显示PLAN_TABLE中保存的解释计划,如果想要显示执行计划,就需要使用到DMBS_XPLAN.DISPLAY_CURSOR方法。

DMBS_XPLAN.DISPLAY_CURSOR方法语法如下:

DBMS_XPLAN.DISPLAY_CURSOR(
   sql_id        IN  VARCHAR2  DEFAULT  NULL,
   child_number  IN  NUMBER    DEFAULT  NULL,
   format        IN  VARCHAR2  DEFAULT  'TYPICAL'
);

参数解释如下:

  • sql_id:表示存储在cursor cache中的SQL语句的id;
  • child_number:用于指示缓存sql语句计划的子id;
  • format:参数用于控制包含在输出中的信息类型; 

除了以上的基本的四种输出格式外,format还有一些附加的选项可用于定制化输出行为,使用中可以通过逗号和空格分隔来声明多个关键字,同时可以使用”+”和”-”符号来包含或排除相应的显示元素,这些附加的选项在官方文档中可以查到:

1.ROWS – 显示被优化器估算的记录的行号
2.BYTES – 显示优化器估算的字节数
3.COST – 显示优化器计算的成本信息
4.PARTITION – 显示分区的分割信息
5.PARALLEL – 显示并行执行信息
6.PREDICATE – 显示谓语
7.PROJECTION – 显示列投影部分(每一行的那些列被传递给其父列已经这些列的大小)
8.ALIAS – 显示查询块名称已经对象别名
9.REMOTE – 显示分布式查询信息
10.NOTE – 显示注释
11.IOSTATS – 显示游标执行的IO统计信息
12.MEMSTATS – 为内存密集运算如散列联结,排序,或一些类型的位图运算显示内存管理统计信息
13.ALLSTATS – 与'IOSTATS MEMSTATS'等价
14.LAST – 显示最后执行的执行计划统计信息,默认显示为ALL类型,并且可以累积。

二、示例

【a】使用display_cursor方法查看最近一条语句的执行计划

//收集运行时的统计信息,包括行数,一直读取次数,物理读次数,物理写次数以及运算在一行数据上耗费的运行时间,如果没有指定该参数,就不会有A-Rows,A-Time,Buffers这三列信息
select /*+ gather_plan_statistics */ count(*) from scott.emp;

//将sql_id和child_number设置成null,表示获取上一条执行语句的执行计划
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

执行过程如下图所示: 

【b】获取某条指定语句的执行计划

//执行一个简单查询
select count(*) from scott.emp where sal between 100 and 3000;

//收集运行时的统计信息
select /*+ gather_plan_statistics */ count(*) from scott.emp where sal between 100 and 3000;

//通过v$sql视图查询到sql语句的SQL_ID和CHILD_NUMBER(注意,必须要确保要查询的sql语句还在shared pool中)
select sql_id,child_number,sql_text from v$sql where sql_text like '%select /*+ gather_plan_statistics */ count(*)%';

//查看该语句执行时的实现执行计划,将上一步查询出来的sql_id作为传递进去
select * from table(dbms_xplan.display_cursor('d86dz1fjtn7g7',0,'ALLSTATS LAST'));

执行过程如下图所示:  

详细的执行过程参考如下:


SQL> select count(*) from scott.emp where sal between 100 and 3000;

  COUNT(*)
----------
        13

SQL> select /*+ gather_plan_statistics */ count(*) from scott.emp where sal between 100 and 3000;

  COUNT(*)
----------
        13

SQL> select sql_id,child_number,sql_text from v$sql where sql_text like '%select /*+ gather_plan_statistics */ count(*)%';

SQL_ID        CHILD_NUMBER
------------- ------------
SQL_TEXT
--------------------------------------------------------------------------------
d86dz1fjtn7g7            0
select /*+ gather_plan_statistics */ count(*) from scott.emp where sal between 1
00 and 3000

94m52fnv9j1u1            0
 select /*+ gather_plan_statistics */ count(*) from scott.emp

b66kdapyrka0z            0
select sql_id,child_number,sql_text from v$sql where sql_text like '%select /*+

SQL_ID        CHILD_NUMBER
------------- ------------
SQL_TEXT
--------------------------------------------------------------------------------
gather_plan_statistics */ count(*)%'


SQL> select * from table(dbms_xplan.display_cursor(' d86dz1fjtn7g7',0,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID:  d86dz1fjtn7g7, child number: 0 cannot be found


SQL> select * from table(dbms_xplan.display_cursor('d86dz1fjtn7g7',0,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  d86dz1fjtn7g7, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from scott.emp where sal
between 100 and 3000

Plan hash value: 2083865914

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

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
ers |

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

|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |
  7 |

|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |
  7 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| EMP  |      1 |     13 |     13 |00:00:00.01 |
  7 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("SAL"<=3000 AND "SAL">=100))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


已选择20行。

SQL>

三、dbms_xplan.display_cursor返回列参数详解

dbms_xplan.display_cursor返回的列参数说明:

  • Id:步骤序号,如果前面有*,表示这一步会根据某一个过滤条件过滤数据;
  • Operation:执行的操作,比如索引扫描,选择数据,表连接等等;
  • Name:操作的对象;
  • Starts:对应的动作执行的次数;
  • E-Rows:优化器估算这一步返回的数据行数(每次执行的估计);
  • A-Rows:这一步实际返回的数据行数,实际值是累积计数;例如E-Rows = 15并且A-Rows = 225是完全合理的,因为第5行开始了15次:所以225实际行= 15开始*每次启动估计有15行.
  • A-Time:实际耗费的时间;
  • Buffers:实际的逻辑IO数量,这里是累计值,包括子操作的值; 

四、参考资料

https://blog.csdn.net/liqfyiyi/article/details/7421242

https://www.cnblogs.com/princessd8251/articles/3537457.html

http://blog.itpub.net/31401608/viewspace-2148824/

https://www.cnblogs.com/tracy/archive/2010/04/16/1713272.html

http://www.mamicode.com/info-detail-2327550.html

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页
实付 19.90元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值