dbms_xplan之display函数的使用

    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包中的函数
  1. SQL> desc dbms_xplan         --> 列出几个常用的  
  2. FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE  
  3.  Argument Name                  Type                    In/Out Default?  
  4.  ------------------------------ ----------------------- ------ --------  
  5.  TABLE_NAME                     VARCHAR2                IN     DEFAULT  
  6.  STATEMENT_ID                   VARCHAR2                IN     DEFAULT  
  7.  FORMAT                         VARCHAR2                IN     DEFAULT  
  8.  FILTER_PREDS                   VARCHAR2                IN     DEFAULT  
  9. FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE  
  10.  Argument Name                  Type                    In/Out Default?  
  11.  ------------------------------ ----------------------- ------ --------  
  12.  SQL_ID                         VARCHAR2                IN  
  13.  PLAN_HASH_VALUE                NUMBER(38)              IN     DEFAULT  
  14.  DB_ID                          NUMBER(38)              IN     DEFAULT  
  15.  FORMAT                         VARCHAR2                IN     DEFAULT  
  16. FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE  
  17.  Argument Name                  Type                    In/Out Default?  
  18.  ------------------------------ ----------------------- ------ --------  
  19.  SQL_ID                         VARCHAR2                IN     DEFAULT  
  20.  CURSOR_CHILD_NO                NUMBER(38)              IN     DEFAULT  
  21.  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加载预估的执行计划        
  1. SQL> EXPLAIN PLAN SET STATEMENT_ID='TSH' FOR  
  2.           2  SELECT *  
  3.           3  FROM   emp e, dept d  
  4.           4  WHERE  e.deptno = d.deptno  
  5.           5  AND    e.ename  = 'SMITH';  
  6.           
  7.         Explained.  
2、使用display函数查看执行计划     
  1. /*------------- 使用了basic模式,且指定了table_name,statement_id -----------------*/   
  2. /**************************************************/  
  3. /* Author: Robinson Cheng                         */  
  4. /* Blog:   http://blog.csdn.net/robinson_0612     */  
  5. /* MSN:    robinson_0612@hotmail.com              */  
  6. /* QQ:     645746311                              */  
  7. /**************************************************/  
  8. SQL> SET LINESIZE 130  
  9. SQL> SELECT *   
  10.   2  FROM   TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','TSH','BASIC'));     
  11.   
  12. PLAN_TABLE_OUTPUT  
  13. -----------------------------------------------------------------------------  
  14. Plan hash value: 351108634  
  15.   
  16. ------------------------------------------------  
  17. | Id  | Operation                    | Name    |  
  18. ------------------------------------------------  
  19. |   0 | SELECT STATEMENT             |         |  
  20. |   1 |  NESTED LOOPS                |         |  
  21. |   2 |   TABLE ACCESS FULL          | EMP     |  
  22. |   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |  
  23. |   4 |    INDEX UNIQUE SCAN         | PK_DEPT |  
  24. ------------------------------------------------  
  25.   
  26. 11 rows selected.  
  27.   
  28. /*---------------- 使用basic +predicate模式 --------------------*/  
  29. SQL> set pagesize 0  
  30. SQL> select * from table(dbms_xplan.display(null,'TSH','basic +predicate'));   
  31. Plan hash value: 351108634  
  32.   
  33. ------------------------------------------------  
  34. | Id  | Operation                    | Name    |  
  35. ------------------------------------------------  
  36. |   0 | SELECT STATEMENT             |         |  
  37. |   1 |  NESTED LOOPS                |         |  
  38. |*  2 |   TABLE ACCESS FULL          | EMP     |  
  39. |   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |  
  40. |*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |  
  41. ------------------------------------------------  
  42.   
  43. Predicate Information (identified by operation id):  
  44. ---------------------------------------------------  
  45.   
  46.    2 - filter("E"."ENAME"='SMITH')  
  47.    4 - access("E"."DEPTNO"="D"."DEPTNO")  
  48.   
  49. 17 rows selected.  
  50.   
  51. /*--------------- 使用typical模式当format为null时的缺省模式   ------------*/  
  52. SQL> select * from table(dbms_xplan.display(null,'TSH','typical'));   
  53. Plan hash value: 351108634  
  54.   
  55. ----------------------------------------------------------------------------------------  
  56. | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |  
  57. ----------------------------------------------------------------------------------------  
  58. |   0 | SELECT STATEMENT             |         |     1 |   117 |     4   (0)| 00:00:01 |  
  59. |   1 |  NESTED LOOPS                |         |     1 |   117 |     4   (0)| 00:00:01 |  
  60. |*  2 |   TABLE ACCESS FULL          | EMP     |     1 |    87 |     3   (0)| 00:00:01 |  
  61. |   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    30 |     1   (0)| 00:00:01 |  
  62. |*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |  
  63. ----------------------------------------------------------------------------------------  
  64.   
  65. Predicate Information (identified by operation id):  
  66. ---------------------------------------------------  
  67.   
  68.    2 - filter("E"."ENAME"='SMITH')  
  69.    4 - access("E"."DEPTNO"="D"."DEPTNO")  
  70.   
  71. Note  
  72. -----  
  73.    - dynamic sampling used for this statement  
  74.   
  75. 21 rows selected.  
  76.   
  77. /*------------- 查看plan_table中STATEMENT_ID为TSH的PLAN_ID值 -------------------*/  
  78. SQL> select statement_id,plan_id from plan_table where rownum<2;    
  79.   
  80. STATEMENT_ID                      PLAN_ID  
  81. ------------------------------ ----------  
  82. TSH                                   223  
  83.   
  84. /*-------------- 使用了advanced -bytes模式,且指定了filter_preds为223 -------------*/  
  85. SQL> select * from table(dbms_xplan.display(null,null,'advanced -bytes','plan_id = 223'));   
  86. Plan hash value: 351108634                                                                  
  87.   
  88. --------------------------------------------------------------------------------  
  89. | Id  | Operation                    | Name    | Rows  | Cost (%CPU)| Time     |  
  90. --------------------------------------------------------------------------------  
  91. |   0 | SELECT STATEMENT             |         |     1 |     4   (0)| 00:00:01 |  
  92. |   1 |  NESTED LOOPS                |         |     1 |     4   (0)| 00:00:01 |  
  93. |*  2 |   TABLE ACCESS FULL          | EMP     |     1 |     3   (0)| 00:00:01 |  
  94. |   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |     1   (0)| 00:00:01 |  
  95. |*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |     0   (0)| 00:00:01 |  
  96. --------------------------------------------------------------------------------  
  97.   
  98. Query Block Name / Object Alias (identified by operation id):  
  99. -------------------------------------------------------------  
  100.   
  101.    1 - SEL$1  
  102.    2 - SEL$1 / E@SEL$1  
  103.    3 - SEL$1 / D@SEL$1  
  104.    4 - SEL$1 / D@SEL$1  
  105.   
  106. Outline Data  
  107. -------------  
  108.   
  109.   /*+  
  110.       BEGIN_OUTLINE_DATA  
  111.       USE_NL(@"SEL$1" "D"@"SEL$1")  
  112.       LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")  
  113.       INDEX_RS_ASC(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))  
  114.       FULL(@"SEL$1" "E"@"SEL$1")  
  115.       OUTLINE_LEAF(@"SEL$1")  
  116.       ALL_ROWS  
  117.       OPTIMIZER_FEATURES_ENABLE('10.2.0.3')  
  118.       IGNORE_OPTIM_EMBEDDED_HINTS  
  119.       END_OUTLINE_DATA  
  120.   */  
  121.   
  122. Predicate Information (identified by operation id):  
  123. ---------------------------------------------------  
  124.   
  125.    2 - filter("E"."ENAME"='SMITH')  
  126.    4 - access("E"."DEPTNO"="D"."DEPTNO")  
  127.   
  128. Column Projection Information (identified by operation id):  
  129. -----------------------------------------------------------  
  130.   
  131.    1 - (#keys=0) "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],  
  132.        "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],  
  133.        "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],  
  134.        "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14],  
  135.        "D"."LOC"[VARCHAR2,13]  
  136.    2 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],  
  137.        "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],  
  138.        "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]  
  139.    3 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14],  
  140.        "D"."LOC"[VARCHAR2,13]  
  141.    4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]  
  142.   
  143. Note  
  144. -----  
  145.    - dynamic sampling used for this statement  
  146.   
  147. 60 rows selected.  
  148.   
  149. /*---------------- 既有"+"也有"-"修饰符的情形 -----------------------*/  
  150. SQL> select * from table(dbms_xplan.display(null,null,'typical +alias -bytes -cost'));   
  151. Plan hash value: 351108634  
  152.   
  153. -------------------------------------------------------------------  
  154. | Id  | Operation                    | Name    | Rows  | Time     |  
  155. -------------------------------------------------------------------  
  156. |   0 | SELECT STATEMENT             |         |     1 | 00:00:01 |  
  157. |   1 |  NESTED LOOPS                |         |     1 | 00:00:01 |  
  158. |*  2 |   TABLE ACCESS FULL          | EMP     |     1 | 00:00:01 |  
  159. |   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 | 00:00:01 |  
  160. |*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 | 00:00:01 |  
  161. -------------------------------------------------------------------  
  162.   
  163. Query Block Name / Object Alias (identified by operation id):  
  164. -------------------------------------------------------------  
  165.   
  166.    1 - SEL$1  
  167.    2 - SEL$1 / E@SEL$1  
  168.    3 - SEL$1 / D@SEL$1  
  169.    4 - SEL$1 / D@SEL$1  
  170.   
  171. Predicate Information (identified by operation id):  
  172. ---------------------------------------------------  
  173.   
  174.    2 - filter("E"."ENAME"='SMITH')  
  175.    4 - access("E"."DEPTNO"="D"."DEPTNO")  
  176.   
  177. Note  
  178. -----  
  179.    - dynamic sampling used for this statement  
  180.   
  181. 29 rows selected.  
四、总结
        1、display函数仅仅针对预估的执行计划,而不是实际的执行计划
        2、display函数显示了预估的执行计划且显示格式灵活,可以以不同的输出格式呈现
        3、当所有参数为null的情况下,显示执行计划表中(缺省为plan_table)最后一条语句的执行计划
        4、尽管可以通过SQL语句查询plan_table来获取执行计划,建议直接使用display函数,这足够说明一切问题    
        5、当SQL语句中使用了绑定变量时,由explain plan获得的执行计划是不可靠的
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页