dbms_xplan之display函数的使用

男娘i 2022-05-15 11:18 125阅读 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 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获得的执行计划是不可靠的

[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