dbms_xplan之display函数的使用

来源：互联网发布：mac word简繁转换编辑：程序博客网时间：2024/05/06 01:20

        DBMS_XPLAN包包括一系列函数，主要是用于显示SQL语句的执行计划，且不同的情形下使用不同的函数来显示，如预估的执行计划则使用
display函数,而实际的执行计划则是用display_cursor函数，对于awr中的执行计划，则是用display_awr函数，而SQL tuning集合中的执行计划
则由display_sqlset来完成。本文主要描述DBMS_XPLAN包中display函数的使用，尽管可以通过SQL语句来查询缺省表plan_table来获得执行计划，
事实上，使用display函数更便捷，且display函数提供了多种不同的显示格式。

       有关执行计划中各字段模块的描述请参考：执行计划中各字段各模块描述
       有关由SQL语句来获取执行计划请参考：     使用 EXPLAIN PLAN 获取SQL语句执行计划
       有关使用autotrace来获取执行计划请参考：启用 AUTOTRACE 功能
       有关display_cursor函数的使用请参考：     dbms_xplan之display_cursor函数的使用

一、DBMS_XPLAN包中的函数

SQL> desc dbms_xplan         --> 列出几个常用的FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- TABLE_NAME                     VARCHAR2                IN     DEFAULT STATEMENT_ID                   VARCHAR2                IN     DEFAULT FORMAT                         VARCHAR2                IN     DEFAULT FILTER_PREDS                   VARCHAR2                IN     DEFAULTFUNCTION 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     DEFAULTFUNCTION 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

二、display函数
1、display函数的几个参数
       table_name
              指定计划表的名字，缺省值为 'PLAN_TABLE'.
       statement_id
               SQL语句的ID号，是在生成执行计划时使用set statement_id名令设定的值，默认值为NULL,当使用默认值时，将显示最近
               插入计划表中的执行计划(filter_preds参数的值为空时)
       format
               用于控制display函数输出的内容。其常用取值为basic,typical,serial,all,advanced。其中typical为缺省值
               除了上述几个取值外，还可以配合一些额外的修饰符来显示不同的内容。如：
                       alias、bytes、cost、note、outline、parallel、paration、predicate等
               常用取值组合修饰符的例子：
                       basic +predicate、basic +outline(需要某个修饰符使用"+"号来连接)
                       typical -bytes、typical +alias -bytes -cost(不需要某个修饰符使用"-"号来连接)
                       注："+"号与"-"号前面应保留空格
       filter_preds
               过滤谓词。用于过滤从plan_table表中返回的记录。当该值为NULL时，执行计划显示最近插入计划表中的执行计划。
                       如：filter_preds=>'plan_id = 223'
2、format参数常用值描述
       basic     仅仅显示最少的信息。基本上包括操作和操作的对象
       typical   显示大部分信息。基本上包括除了别名，提纲和字段投影外的所有信息，此为缺省值。
        serial        类似于typical，但不显示并行操作
      all         显示除提纲之外的所有信息
      advanced    显示所有信息
3、format参数修饰符
       alias       控制包含查询块与别名的显示部分
       bytes        控制执行计划表中字段bytes的显示
       cost         控制执行计划表中字段cost的显示
       note         控制包含注释信息的显示部分
       outline      控制包含提纲信息的显示部分
       parallel     控制包含并行处理信息的提示
       partition    控制并行处理信息的显示，尤其是执行计划表中字段TQ、IN-OUT、PQ Distrib的显示
       peeked_binds 控制包含绑定变量窥探部分的显示。仅当生成执行计划时使用了绑定变量是可见
       predicate    控制包含谓词filter和access显示部分
       projection   控制包含投影信息的显示部分
       remote       控制远程执行的SQL语句的显示
       rows         控制执行计划表中字段rows的显示

三、演示使用display函数获取执行计划(演示版本Oracle 10g R2)
1、使用EXPLAIN PLAN加载预估的执行计划

SQL> EXPLAIN PLAN SET STATEMENT_ID='TSH' FOR          2  SELECT *          3  FROM   emp e, dept d          4  WHERE  e.deptno = d.deptno          5  AND    e.ename  = 'SMITH';                Explained.

2、使用display函数查看执行计划

/*------------- 使用了basic模式，且指定了table_name，statement_id -----------------*/ /**************************************************//* Author: Robinson Cheng                         *//* Blog:   http://blog.csdn.net/robinson_0612     *//* MSN:    robinson_0612@hotmail.com              *//* QQ:     645746311                              *//**************************************************/SQL> SET LINESIZE 130SQL> SELECT *   2  FROM   TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','TSH','BASIC'));   PLAN_TABLE_OUTPUT-----------------------------------------------------------------------------Plan hash value: 351108634------------------------------------------------| Id  | Operation                    | Name    |------------------------------------------------|   0 | SELECT STATEMENT             |         ||   1 |  NESTED LOOPS                |         ||   2 |   TABLE ACCESS FULL          | EMP     ||   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    ||   4 |    INDEX UNIQUE SCAN         | PK_DEPT |------------------------------------------------11 rows selected./*---------------- 使用basic +predicate模式 --------------------*/SQL> set pagesize 0SQL> select * from table(dbms_xplan.display(null,'TSH','basic +predicate')); Plan hash value: 351108634------------------------------------------------| Id  | Operation                    | Name    |------------------------------------------------|   0 | SELECT STATEMENT             |         ||   1 |  NESTED LOOPS                |         ||*  2 |   TABLE ACCESS FULL          | EMP     ||   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    ||*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("E"."ENAME"='SMITH')   4 - access("E"."DEPTNO"="D"."DEPTNO")17 rows selected./*--------------- 使用typical模式当format为null时的缺省模式   ------------*/SQL> select * from table(dbms_xplan.display(null,'TSH','typical')); Plan hash value: 351108634----------------------------------------------------------------------------------------| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT             |         |     1 |   117 |     4   (0)| 00:00:01 ||   1 |  NESTED LOOPS                |         |     1 |   117 |     4   (0)| 00:00:01 ||*  2 |   TABLE ACCESS FULL          | EMP     |     1 |    87 |     3   (0)| 00:00:01 ||   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    30 |     1   (0)| 00:00:01 ||*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("E"."ENAME"='SMITH')   4 - access("E"."DEPTNO"="D"."DEPTNO")Note-----   - dynamic sampling used for this statement21 rows selected./*------------- 查看plan_table中STATEMENT_ID为TSH的PLAN_ID值 -------------------*/SQL> select statement_id,plan_id from plan_table where rownum<2;  STATEMENT_ID                      PLAN_ID------------------------------ ----------TSH                                   223/*-------------- 使用了advanced -bytes模式,且指定了filter_preds为223 -------------*/SQL> select * from table(dbms_xplan.display(null,null,'advanced -bytes','plan_id = 223')); Plan hash value: 351108634                                                                --------------------------------------------------------------------------------| Id  | Operation                    | Name    | Rows  | Cost (%CPU)| Time     |--------------------------------------------------------------------------------|   0 | SELECT STATEMENT             |         |     1 |     4   (0)| 00:00:01 ||   1 |  NESTED LOOPS                |         |     1 |     4   (0)| 00:00:01 ||*  2 |   TABLE ACCESS FULL          | EMP     |     1 |     3   (0)| 00:00:01 ||   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |     1   (0)| 00:00:01 ||*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |     0   (0)| 00:00:01 |--------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - SEL$1   2 - SEL$1 / E@SEL$1   3 - SEL$1 / D@SEL$1   4 - SEL$1 / D@SEL$1Outline Data-------------  /*+      BEGIN_OUTLINE_DATA      USE_NL(@"SEL$1" "D"@"SEL$1")      LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")      INDEX_RS_ASC(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))      FULL(@"SEL$1" "E"@"SEL$1")      OUTLINE_LEAF(@"SEL$1")      ALL_ROWS      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')      IGNORE_OPTIM_EMBEDDED_HINTS      END_OUTLINE_DATA  */Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("E"."ENAME"='SMITH')   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 - "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]   3 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14],       "D"."LOC"[VARCHAR2,13]   4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]Note-----   - dynamic sampling used for this statement60 rows selected./*---------------- 既有"+"也有"-"修饰符的情形 -----------------------*/SQL> select * from table(dbms_xplan.display(null,null,'typical +alias -bytes -cost')); Plan hash value: 351108634-------------------------------------------------------------------| Id  | Operation                    | Name    | Rows  | Time     |-------------------------------------------------------------------|   0 | SELECT STATEMENT             |         |     1 | 00:00:01 ||   1 |  NESTED LOOPS                |         |     1 | 00:00:01 ||*  2 |   TABLE ACCESS FULL          | EMP     |     1 | 00:00:01 ||   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 | 00:00:01 ||*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 | 00:00:01 |-------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - SEL$1   2 - SEL$1 / E@SEL$1   3 - SEL$1 / D@SEL$1   4 - SEL$1 / D@SEL$1Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("E"."ENAME"='SMITH')   4 - access("E"."DEPTNO"="D"."DEPTNO")Note-----   - dynamic sampling used for this statement29 rows selected.

四、总结
       1、display函数仅仅针对预估的执行计划，而不是实际的执行计划
       2、display函数显示了预估的执行计划且显示格式灵活，可以以不同的输出格式呈现
       3、当所有参数为null的情况下，显示执行计划表中(缺省为plan_table)最后一条语句的执行计划
       4、尽管可以通过SQL语句查询plan_table来获取执行计划，建议直接使用display函数，这足够说明一切问题
       5、当SQL语句中使用了绑定变量时，由explain plan获得的执行计划是不可靠的