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包中的函数
二、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加载预估的执行计划
2、使用display函数查看执行计划
四、总结
1、display函数仅仅针对预估的执行计划，而不是实际的执行计划
2、display函数显示了预估的执行计划且显示格式灵活，可以以不同的输出格式呈现
3、当所有参数为null的情况下，显示执行计划表中(缺省为plan_table)最后一条语句的执行计划
4、尽管可以通过SQL语句查询plan_table来获取执行计划，建议直接使用display函数，这足够说明一切问题
5、当SQL语句中使用了绑定变量时，由explain plan获得的执行计划是不可靠的
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
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.
- /*------------- 使用了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获得的执行计划是不可靠的
扫一扫
1268
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
