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包中的函数

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> 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获得的执行计划是不可靠的

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

SQLplus 下行预取特性

   通常情况下数据库引擎每访问一个数据块将产生至少一个逻辑读。而行预取与逻辑读息息相关。行预取是指当客户端从数据库获取数据时 可以采用单行也可以采用多行方式返...

7320
来自专栏乐沙弥的世界

使用 EXPLAIN PLAN 获取SQL语句执行计划

     SQL查询语句的性能从一定程度上影响整个数据库的性能。很多情况下,数据库性能的低下差不多都是不良SQL语句所引起。而SQL语句的执行 计划则决定了S...

12350
来自专栏乐沙弥的世界

FORALL 之 SAVE EXCEPTIONS 子句应用一例

     对于大批量的DML操作中出现的错误,除了使用DML error logging特性来记录在DML期间出现的错误之外,使用批量SQL语句FORALL的S...

7410
来自专栏乐沙弥的世界

Oracle ROWID 方式访问数据库

    和ROWNUM一样,ROWID是一个伪列,即是一个非用户定义的列,而又实际存储于数据库之中。每一个表都有一个ROWID列,一个ROWID值用于 唯一确定...

10420
来自专栏乐沙弥的世界

Oracle ROWID

  ROWID 是一个类似于rownum的伪列,用于定位数据库中一条记录的一个相对唯一地址值。通常情况下,该值在该行数据插入到数据库表时即 被确定且唯一。而...

17840
来自专栏文渊之博

利用PowerShell复制SQLServer账户的所有权限

问题   对于DBA或者其他运维人员来说授权一个账户的相同权限给另一个账户是一个很普通的任务。但是随着服务器、数据库、应用、使用人员地增加就变得很枯燥乏味又耗时...

30580
来自专栏数据和云

为什么预估执行计划与真实执行计划会有差异?

一 问题概要 对同一个 SQL 语句的 ExplainPlan 里显示的预估执行计划与通过 V$SQL_PLAN 视图获取的 Runtime Plan 真实执行...

47970
来自专栏数据库新发现

如何使用USE_CONCAT提示

USE_CONCAT提示强迫优化器扩展查询中的每一个OR谓词为独立的查询块. 最后合并所有查询块的结果,返回结果集给用户。

13820
来自专栏杨建荣的学习笔记

简单实用的sql小技巧(第一篇) (r3笔记第36天)

今天和大家简单分享几个实用的sql小技巧。还有一些还在整理中,会不断的分享出来。 有些其实也不算是sql的技巧,可能大家在写sql语句的时候没有意识到我们可以通...

31030
来自专栏乐沙弥的世界

ORA-00932: inconsistent datatypes: expected - got CLOB

      最近数据库从10.2.0.3升级到了10.2.0.5之后,一些对象无法编译通过。查看了这些对象主要表现在之前写法不严格的SQL语法导致了这些pack...

17130

扫码关注云+社区

领取腾讯云代金券