dbms xplan之display函数的使用

港控/mmm° 2022-03-18 05:42 100阅读 0赞

DBMS\_XPLAN包包括一系列函数，主要是用于显示SQL语句的执行计划，且不同的情形下使用不同的函数来显示，如预估的执行计划则使用  
display函数,而实际的执行计划则是用display\_cursor函数，对于awr中的执行计划，则是用display\_awr函数，而SQL tuning集合中的执行计划  
则由display\_sqlset来完成。本文主要描述DBMS\_XPLAN包中display函数的使用，尽管可以通过SQL语句来查询缺省表plan\_table来获得执行计划，  
事实上，使用display函数更便捷，且display函数提供了多种不同的显示格式。  
  
有关执行计划中各字段模块的描述请参考： [执行计划中各字段各模块描述][Link 1]  
有关由SQL语句来获取执行计划请参考： [使用 EXPLAIN PLAN 获取SQL语句执行计划][EXPLAIN PLAN _SQL]  
有关使用autotrace来获取执行计划请参考： [启用 AUTOTRACE 功能][AUTOTRACE]  
有关display\_cursor函数的使用请参考： [dbms\_xplan之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 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获得的执行计划是不可靠的

再分享一下我老师大神的人工智能教程吧。零基础！通俗易懂！风趣幽默！还带黄段子！希望你也加入到我们人工智能的队伍中来！[https://blog.csdn.net/jiangjunshow][https_blog.csdn.net_jiangjunshow]

[Link 1]: http://write.blog.csdn.net/postedit/6860007
[EXPLAIN PLAN _SQL]: http://blog.csdn.net/robinson_0612/article/details/6837771
[AUTOTRACE]: http://blog.csdn.net/robinson_0612/article/details/6030407
[dbms_xplan_display_cursor]: http://blog.csdn.net/robinson_0612/article/details/6866925
[https_blog.csdn.net_jiangjunshow]: https://blog.csdn.net/jiangjunshow/article/details/77338485