dbms_xplan之display函数的使用

DBMS_XPLAN包包括一系列函数,主要是用于显示SQL语句的执行计划,且不同的情形下使用不同的函数来显示,如预估的执行计划则使用
display函数,而实际的执行计划则是用display_cursor函数,对于awr中的执行计划,则是用display_awr函数,而SQL tuning集合中的执行计划
则由display_sqlset来完成。本文主要描述DBMS_XPLAN包中display函数的使用,尽管可以通过SQL语句来查询缺省表plan_table来获得执行计划,
事实上,使用display函数更便捷,且display函数提供了多种不同的显示格式。
 
一、DBMS_XPLAN包中的函数
[sql] view plaincopyprint?
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     DEFAULT 
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 
二、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] view plaincopyprint?
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函数查看执行计划    
[sql] view plaincopyprint?
/*------------- 使用了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 130 
SQL> 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 0 
SQL> 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 statement 
 
21 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$1 
 
Outline 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 statement 
 
60 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$1 
 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
 
   2 - filter("E"."ENAME"='SMITH') 
   4 - access("E"."DEPTNO"="D"."DEPTNO") 
 
Note 
----- 
   - dynamic sampling used for this statement 
 
29 rows selected. 
四、总结
        1、display函数仅仅针对预估的执行计划,而不是实际的执行计划
        2、display函数显示了预估的执行计划且显示格式灵活,可以以不同的输出格式呈现
        3、当所有参数为null的情况下,显示执行计划表中(缺省为plan_table)最后一条语句的执行计划
        4、尽管可以通过SQL语句查询plan_table来获取执行计划,建议直接使用display函数,这足够说明一切问题   
        5、当SQL语句中使用了绑定变量时,由explain plan获得的执行计划是不可靠的
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值