• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    Oracle中使用DBMS_XPLAN处理执行计划详解

    DBMS_XPLAN是Oracle提供的一个用于查看SQL计划,包括执行计划和解释计划的包;在以前查看SQL执行计划的时候,我都是使用set autotrace命令,不过现在看来,DBMS_XPLAN包给出了更加简化的获取和显示计划的方式。

    这5个函数分别对应不同的显示计划的方式,DBMS_XPLAN包不仅可以获取解释计划,它还可以用来输出存储在AWR,SQL调试集,缓存的SQL游标,以及SQL基线中的语句计划,实现如上的功能,通常会用到一下5个方法:

    1.DISPLAY
    2.DISPLAY_AWR
    3.DISPLAY_CURSOR
    4.DISPLAY_PLAN
    5.DISPLAY_SQL_PLAN_BASELINE
    6.DISPLAY_SQLSET

    下面将重点讨论关于DBMS_XPLAN包在解释计划和执行计划上的应用。

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

    复制代码 代码如下:

    SQL> explain plan for select * from scott.emp;

    Explained.

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

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Plan hash value: 3956160932

    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |    14 |   532 |     3   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------

    8 rows selected.

    -- 对应autotrace实现
    SQL> set autotrace traceonly explain


    上例中使用了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参数用于控制包含在输出中的信息类型,官档的参数如下:

    1.BASIC: 显示最少的信息,只包括操作类型,ID名称和选项。
    2.TYPICAL: 默认值,显示相关信息以及某些附加的显示选项,如分区和并发使用等。
    3.SERIAL: 与TYPICAL类型相似,区别是它不包括并发的信息,即使是并行执行的计划。
    4.ALL: 显示最多的信息,包含了TYPICAL的全部以及更多的附加信息,如别名和远程调用等。

    除了以上的基本的四种输出格式外,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类型,并且可以累积。

    以上的参数同样适用于解释计划的display方法。

    示例部分:

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

    复制代码 代码如下:

    SQL> select /*+ gather_plan_statistics */ count(*) from scott.emp;

      COUNT(*)
    ----------
            14

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

    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------------------
    SQL_ID  f9qyz8s3c2c02, child number 0
    -------------------------------------
    select /*+ gather_plan_statistics */ count(*) from scott.emp

    Plan hash value: 2937609675

    -------------------------------------------------------------------------------------
    | Id  | Operation        | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |        |      1 |        |      1 |00:00:00.01 |       1 |
    |   1 |  SORT AGGREGATE  |        |      1 |      1 |      1 |00:00:00.01 |       1 |
    |   2 |   INDEX FULL SCAN| PK_EMP |      1 |     14 |     14 |00:00:00.01 |       1 |
    -------------------------------------------------------------------------------------

    14 rows selected.

    使用dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')时,将sql_id和child_number设置成null,表示获取上一条执行语句的执行计划;注意上面的例子中一定要指定gather_plan_statistics提示或者手动设置数据库STATISTICS_LEVEL参数为ALL来使得其抓取行数据源的执行统计信息,这些信息包括行数,一直读取次数,物理读次数,物理写次数以及运算在一行数据上耗费的运行时间,如果没有指定该提示,就不会有A-Rows,A-Time,Buffers这三列信息。

    二、获取某条指定语句的执行计划

    复制代码 代码如下:

    SQL> select /*+ gather_plan_statistics */ count(*) from hr.employees where department_id in (90, 100);

      COUNT(*)
    ----------
             9

    -- 通过v$sql视图查询到sql语句的SQL_ID和CHILD_NUMBER
    SQL> select sql_id,child_number,sql_text from v$sql
      2  where sql_text like '%select /*+ gather_plan_statistics */ count(*)%';

    SQL_ID        CHILD_NUMBER SQL_TEXT
    ------------- ------------ ----------------------------------------------------------------------------------------------------
    5qxmkvh40yw0p            0 select /*+ gather_plan_statistics */ count(*) from hr.employees where department_id in (90, 100)
    bqjrnskvpv51n            0 select sql_id,child_number,sql_text from v$sql where sql_text like '%select /*+ gather_plan_statisti
                               cs */ count(*)%'

    -- 获取对应的执行计划
    SQL> select * from table(dbms_xplan.display_cursor('5qxmkvh40yw0p',0,'ALLSTATS LAST'));

    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    SQL_ID  5qxmkvh40yw0p, child number 0
    -------------------------------------
    select /*+ gather_plan_statistics */ count(*) from hr.employees where
    department_id in (90, 100)

    Plan hash value: 4167091351

    --------------------------------------------------------------------------------------------------
    | Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    --------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |                   |      1 |        |      1 |00:00:00.01 |       2 |
    |   1 |  SORT AGGREGATE    |                   |      1 |      1 |      1 |00:00:00.01 |       2 |
    |   2 |   INLIST ITERATOR  |                   |      1 |        |      9 |00:00:00.01 |       2 |
    |*  3 |    INDEX RANGE SCAN| EMP_DEPARTMENT_IX |      2 |      9 |      9 |00:00:00.01 |       2 |
    --------------------------------------------------------------------------------------------------

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

       3 - access(("DEPARTMENT_ID"=90 OR "DEPARTMENT_ID"=100))

    21 rows selected.

    三、通过format参数定制执行计划输出信息

    复制代码 代码如下:

    -- 使用ALL来显示解释计划的全部信息
    SQL> explain plan for
      2  select * from emp e, dept d
      3  where e.deptno = d.deptno
      4  and e.ename = 'JONES' ;

    Explained.

    SQL> select * from table(dbms_xplan.display(format=>'ALL'));

    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    Plan hash value: 3625962092

    ----------------------------------------------------------------------------------------
    | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |         |     1 |    58 |     4   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS                |         |       |       |            |          |
    |   2 |   NESTED LOOPS               |         |     1 |    58 |     4   (0)| 00:00:01 |
    |*  3 |    TABLE ACCESS FULL         | EMP     |     1 |    38 |     3   (0)| 00:00:01 |
    |*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
    |   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |

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

       1 - SEL$1
       3 - SEL$1 / E@SEL$1
       4 - SEL$1 / D@SEL$1
       5 - SEL$1 / D@SEL$1

    Predicate Information (identified by operation id):

       3 - filter("E"."ENAME"='JONES')
       4 - access("E"."DEPTNO"="D"."DEPTNO")

    Column Projection Information (identified by operation id):
    -----------------------------------------------------------

       1 - (#keys=0) "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
           "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],
           "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],
           "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
       2 - (#keys=0) "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
           "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],
           "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],
           "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
       3 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
           "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
           "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
       4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
       5 - "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]

    43 rows selected.

    -- 去除执行计划上的字节数和成本统计信息
    SQL> select empno, ename from emp e, dept d
      2  where e.deptno = d.deptno
      3  and e.ename = 'JONES' ;

         EMPNO ENAME
    ---------- ----------
          7566 JONES

    SQL> select * from table(dbms_xplan.display_cursor(null,null,format=>'ALLSTATS LAST -COST -BYTES'));

    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    SQL_ID  3mypf7d6npa97, child number 1
    -------------------------------------
    select empno, ename from emp e, dept d where e.deptno = d.deptno and
    e.ename = 'JONES'

    Plan hash value: 3956160932

    ------------------------------------------------------------------------------------
    | Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.01 |       8 |
    |*  1 |  TABLE ACCESS FULL| EMP  |      1 |      1 |      1 |00:00:00.01 |       8 |
    ------------------------------------------------------------------------------------

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

       1 - filter(("E"."ENAME"='JONES' AND "E"."DEPTNO" IS NOT NULL))

    19 rows selected.

    -- 另一种选项,窥视绑定变量的值,非常方便!!
    SQL> variable v_empno number
    SQL> exec :v_empno := 7566 ;

    PL/SQL procedure successfully completed.

    SQL> select * from emp where empno = :v_empno ;

         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7566 JONES      MANAGER         7839 02-APR-81       2975                    20

    SQL> select * from table(dbms_xplan.display_cursor(null,null,format=>'+PEEKED_BINDS'));

    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    SQL_ID  9q17w9umt58m7, child number 0
    -------------------------------------
    select * from emp where empno = :v_empno

    Plan hash value: 2949544139

    --------------------------------------------------------------------------------------
    | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
    |   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
    |*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)|          |
    --------------------------------------------------------------------------------------

    Peeked Binds (identified by position):
    --------------------------------------

       1 - :V_EMPNO (NUMBER): 7566

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

    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
       2 - access("EMPNO"=:V_EMPNO)

    24 rows selected.

    -- 并行查询信息筛选
    SQL> select /*+ parallel(d, 4) parallel (e, 4) */
      2  d.dname, avg(e.sal), max(e.sal)
      3  from dept d, emp e
      4  where d.deptno = e.deptno
      5  group by d.dname
      6  order by max(e.sal), avg(e.sal) desc;

    DNAME          AVG(E.SAL) MAX(E.SAL)
    -------------- ---------- ----------
    SALES          1566.66667       2850
    RESEARCH             2175       3000
    ACCOUNTING     2916.66667       5000

    SQL> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL -BYTES -COST'));

    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    SQL_ID  gahr597f78j0d, child number 0
    -------------------------------------
    select /*+ parallel(d, 4) parallel (e, 4) */ d.dname, avg(e.sal),
    max(e.sal) from dept d, emp e where d.deptno = e.deptno group by
    d.dname order by max(e.sal), avg(e.sal) desc

    Plan hash value: 3078011448

    --------------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name     | Rows  | Time     |    TQ  |IN-OUT| PQ Distrib |
    --------------------------------------------------------------------------------------------------

    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |          |       |          |        |      |            |
    |   1 |  PX COORDINATOR               |          |       |          |        |      |            |
    |   2 |   PX SEND QC (ORDER)          | :TQ10004 |     4 | 00:00:01 |  Q1,04 | P->S | QC (ORDER) |
    |   3 |    SORT ORDER BY              |          |     4 | 00:00:01 |  Q1,04 | PCWP |            |
    |   4 |     PX RECEIVE                |          |     4 | 00:00:01 |  Q1,04 | PCWP |            |
    |   5 |      PX SEND RANGE            | :TQ10003 |     4 | 00:00:01 |  Q1,03 | P->P | RANGE      |
    |   6 |       HASH GROUP BY           |          |     4 | 00:00:01 |  Q1,03 | PCWP |            |
    |   7 |        PX RECEIVE             |          |    14 | 00:00:01 |  Q1,03 | PCWP |            |
    |   8 |         PX SEND HASH          | :TQ10002 |    14 | 00:00:01 |  Q1,02 | P->P | HASH       |
    |*  9 |          HASH JOIN BUFFERED   |          |    14 | 00:00:01 |  Q1,02 | PCWP |            |
    |  10 |           PX RECEIVE          |          |     4 | 00:00:01 |  Q1,02 | PCWP |            |
    |  11 |            PX SEND HASH       | :TQ10000 |     4 | 00:00:01 |  Q1,00 | P->P | HASH       |
    |  12 |             PX BLOCK ITERATOR |          |     4 | 00:00:01 |  Q1,00 | PCWC |            |
    |* 13 |              TABLE ACCESS FULL| DEPT     |     4 | 00:00:01 |  Q1,00 | PCWP |            |
    |  14 |           PX RECEIVE          |          |    14 | 00:00:01 |  Q1,02 | PCWP |            |
    |  15 |            PX SEND HASH       | :TQ10001 |    14 | 00:00:01 |  Q1,01 | P->P | HASH       |
    |  16 |             PX BLOCK ITERATOR |          |    14 | 00:00:01 |  Q1,01 | PCWC |            |
    |* 17 |              TABLE ACCESS FULL| EMP      |    14 | 00:00:01 |  Q1,01 | PCWP |            |
    --------------------------------------------------------------------------------------------------

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

       9 - access("D"."DEPTNO"="E"."DEPTNO")
      13 - access(:Z>=:Z AND :Z=:Z)   17 - access(:Z>=:Z AND :Z=:Z)

    38 rows selected.


    您可能感兴趣的文章:
    • ORACLE数据库查看执行计划的方法
    • Oracle中获取执行计划的几种方法分析
    • 查看Oracle的执行计划一句话命令
    • Oracle中基于hint的3种执行计划控制方法详细介绍
    • Oracle中直方图对执行计划的影响详解
    上一篇:Oracle中基于hint的3种执行计划控制方法详细介绍
    下一篇:Oracle CBO优化模式中的5种索引访问方法浅析
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    Oracle中使用DBMS_XPLAN处理执行计划详解 Oracle,中,使用,DBMS,XPLAN,处理,