《Pro Oracle SQL》 读书笔记-Chapter 6-6.2 Execution Plans-之4
Understanding DBMS_XPLAN in Detail 深入理解DBMS_XPLAN
(Page 174)
The DBMS_XPLAN package is supplied by Oracle and can be used to simplify the retrieval and display of plan output, as I have demonstrated. In order to use all the procedures and functions in this package fully, you’ll need to have privileges to certain fixed views. A single grant on SELECT_CATALOG_ROLE will ensure you have access to everything you need, but at a minimum, you should have select privileges for V$SQL, V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL in order to properly execute just the display and display_cursor functions. In this section, I want to cover a few more details about the use of this package and, in particular, the format options for the display and display_cursor functions.
The dbms_xplan package has grown since it first appeared in Oracle version 9. At that time, it contained only the display function. In Oracle 11 release 2, the package includes 21 functions, although only six of them are included in the documentation. These functions can be used to display not only explain plan output, but plans for statements stored in the AWR (Automatic Workload Repository), SQL tuning sets, cached SQL cursors, and SQL plan baselines. The five main table functions used to display plans from each of these areas are:
• DISPLAY
• DISPLAY_CURSOR
• DISPLAY_AWR
• DISPLAY_SQLSET
• DISPLAY_SQL_PLAN_BASELINE
正如我之前所述，DBMS_XPLAN包是由ORACLE提供的，用于简化检索和展示计划输出。为了使用这个包中的所有存储过程和函数，你需要有查看某些
固化视图(fixed
views)的权限。单独的授予SELECT_CATALOG_ROLE将确保你有访问你所需要的一切的(权利)，但是最基本，为了适当的执行
display和display_cursor函数，你应该要有对 V$SQL, V$SQL_PLAN,
V$SESSION和V$SQL_PLAN_STATISTICS_ALL的select权限。在本节中，我将更详细的讲解使用这个包，特别
是，display和display_cursor函数的格式化选项。
dbms_xplan 包在Oracle
9开始发布，那时仅仅包含display函数。在Oracle
11.2，该包包含了21个函数，而仅仅只有6个包含在文档中。这些函数不仅能用于解释计划输出，而且能用于输出存储于AWR(自动工作负载加载)中的语
句的，SQL调优集的，缓存的SQL游标的，以及SQL计划基线的计划。五个主表函数用于展示各自范围的计划。
• DISPLAY
• DISPLAY_CURSOR
• DISPLAY_AWR
• DISPLAY_SQLSET
• DISPLAY_SQL_PLAN_BASELINE
These five table functions all return the DBMS_XPLAN_TYPE_TABLE type, which is made up of 300 byte strings. This type accommodates the variable formatting needs of each table function to display the plan table columns dynamically as needed. The fact that these are table functions means that in order to call them you must use the TABLE function to cast the return type properly when used in a SELECT statement. A table function is simply a stored PL/SQL function that behaves like a regular query to a table would. The benefit is that you can write code in the function that performs transformations to data before it is returned in the result set. In the case of queries against the PLAN_TABLE or V$SQL_PLAN, the use of a table function makes it possible to do all the dynamic formatting needed to output only the columns pertinent for a given SQL statement instead of having to try and create multiple queries to handle different needs.
这五个表函数全都返回
DBMS_XPLAN_TYPE_TABLE类型，它就是300字节的字符串。这种类型适应于各个表函数变量格式的需要，动态的按需展示计划的表列。正是
因为他们是表函数，意味着当你使用SELECT语句时，为了调用他们，你必须用表函数强制转换(cast)适当的返回类型。一个表函数其实就是一个
PL/SQL函数，用起来就像一般的表查询。好处在于你能在结果集返回之前，在函数中写代码(调用)执行数据转换。比如查询PLAN_TABLE或者
V$SQL_PLAN，运用表函数就能按需要动态的格式化输出给定SQL语句的相关列，而不是必须试图和创建多个查询来处理不同的需要。
Each of the table functions accepts a FORMAT parameter as input. The FORMAT parameter controls
what information is included in the display output. The following is a list of documented values for this
parameter:
• BASIC displays only the operation name and its option.
• TYPICAL displays the relevant information and variably displays options like partition and
parallel usage only when applicable. This is the default.
• SERIAL is the same as TYPICAL but always excludes parallel information.
• ALL displays the maximum amount of information in the display.
每个表函数接收一个FORMAT的输入参数。FORMAT参数控制在展示输出中包含什么信息。下面的列表列出了该参数的文档值：
• BASIC展示操作名和它的选项。
• TYPICAL展示了相关的信息和可变的展示选项，比如分区和并行使用，仅当可用时。这是默认值。
• SERIAL同TYPICAL，但排除并行信息。
• ALL展示全部信息。
In addition to the basic format parameter values, there are several additional more granular
options that can be used to customize the default behavior of the base values. You can specify multiple keywords separated by a comma or a space and use the prefix of a plus sign (+) to indication inclusion or a minus sign (-) to indicate exclusion of that particular display element. All of these options will display the information only if relevant. The following is a list of optional keywords:
除了基本的格式化参数值，还有几个更细粒度的选项能用来自定义基本值的默认行为。你可以指定多个关键字，使用逗号，空格，用加号(+)表示包含，减号(-)表示排除，(连接)特别展示的元素。所的有的这些选项将仅仅展示相关信息。下面列出了可选的关键字：
• ADVANCED shows the same as ALL plus the Outline section and the peeked binds section.
• ALIAS shows the Query Block Name/Object Alias section.
• ALL shows the Query Block Name/Object Alias section, the predicate section, and the column
projection section.
• ALLSTATS* is equivalent to IOSTATS LAST.
• BYTES shows the estimated number of bytes.
• COST is the cost information computed by the optimizer.
• IOSTATS* show IO statistics for executions of the cursor.
• LAST* shows only the plan statistics for the last execution of the cursor (the default is ALL and
is cumulative).
• MEMSTATS* shows the memory management statistics for memory intensive operation like
hash-joins, sorts, or some bitmap operators.
• NOTE shows the Note section.
• OUTLINE shows the Outline section (set of hints that will reproduce the plan).
• PARALLEL shows parallel execution information.
• PARTITION shows partition pruning information.
• PEEKED_BINDS shows bind variable values.
• PREDICATE shows the predicate section.
• PROJECTION shows the column projection section (which columns have been passed to their
parent by each line and the size of those columns).
• REMOTE shows distributed query information.
• ADVANCE展示同ALL，还加上大纲(Outline)段和监视的绑定(变量)段。
• ALIAS展示查询块名/对象别名段。
• ALL展示查询块名/对象别名段，谓词段，还有列投影段。
• ALLSTATS*等同于IOSTATS LAST。
• BYTES展示评估的字节数。
• COST是优化器计算的成本信息。
• IOSTATS*显示游标运行的IO统计(信息)。
• LAST*仅仅展示上次游标运行的计划统计(默认是ALL且是累计的)。
• MEMSTATS* 展示像哈希-连接，排序，或一些位图操作等内存密集操作的内存管理统计(信息)。
• NOTE展示注意段。
• OUTLINE展示大纲段(提示集用于复制计划)。
• PARALLEL展示并行执行信息。
• PARTITION展示分区修剪信息。
• PEEKED_BINDS展示绑定变量的值。
• PREDICATE展示谓词段。
• PROJECTION展示列映射段(一行中的哪些列传递给他的上一级了以及这些列的大小)。
• REMOTE展示分布查询信息。
The keywords followed by an asterisk are not available for use with the DISPLAY function as they
utilize information from V$SQL_PLAN_STATISTICS_ALL that only exists after a statement has been
executed. Listing 6-13 shows several examples of the various options in use.
后面带星号的关键字对于DISPLAY函数不是有效的。因为它们使用来自V$SQL_PLAN_STATISTICS_ALL的信息，只在语句执行之后存在。列表6-13显示了各选项使用的几个例子。
Listing 6-13. Display Options Using the FORMAT Parameter 使用格式化参数的展示选项
SQL> explain plan for
2 select * from emp e, dept d
3 where e.deptno = d.deptno
4 and e.ename = 'JONES' ;
Explained.
SQL> select * from table(dbms_xplan.display(format=>'ALL'));
（在ORACLE 10g Express版本上运行上述语句报错“报错ORA-00907：丢失右括号”，只能用select * from table(dbms_xplan.display('PLAN_TABLE','','ALL')); ）
PLAN_TABLE_OUTPUT
-----------------------------------------------------
Plan hash value: 3625962092
----------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 59 | 4 (0) | 00:00:01 |
|
1 | NESTED LOOPS |
| | | | |
|
2 | NESTED LOOPS |
| 1 | 59 | 4 (0) | 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 39 | 3 (0) | 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0) | 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0) | 00:00:01 |
----------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------
1 - SEL$1
3 - SEL$1 / E@SEL$1
4 - SEL$1 / D@SEL$1
5 - SEL$1 / D@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."ENAME"='JONES')
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 - (#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".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
3 - "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]
4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
5 - "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
SQL> select empno, ename from emp e, dept d
2 where e.deptno = d.deptno
3 and e.ename = 'JONES' ;
EMPNO ENAME
---------- ----------
7566 JONES
1 row selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,format=>'ALLSTATS LAST -COST -
BYTES'));
（在ORACLE 10g Express版本上运行上述语句报错“报错ORA-00907：丢失右括号”
所以只能运行：select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -COST -BYTES'));
参见附件查看实际运行结果）
PLAN_TABLE_OUTPUT
--------------------------------------------------------
SQL_ID 3mypf7d6npa97, child number 0
-------------------------------------
select empno, ename from emp e, dept d where e.deptno = d.deptno and
e.ename = 'JONES'
(page 179)
Plan hash value: 2949544139
--------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------
|
0 | SELECT STATEMENT |
| | | 1 (100) | |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 39 | 1 (0) | 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0) | |
--------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :V_EMPNO (NUMBER): 7566
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=:V_EMPNO)
SQL> select /*+ parallel(d, 4) parallel (e, 4) */
2 d.dname, avg(e.sal), max(e.sal)
3 from dept d, emp e
4 where d.deptno = e.deptno
5 group by d.dname
6 order by max(e.sal), avg(e.sal) desc;
DNAME AVG(E.SAL) MAX(E.SAL)
-------------- --------------- ---------------
SALES 1723.3333333333 3135
RESEARCH 2392.5 3300
ACCOUNTING 3208.3333333333 5500
SQL> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL -BYTES -COST'));
（在ORACLE 10g Express版本上运行不出下列效果，参见附件查看实际运行结果）
PLAN_TABLE_OUTPUT
------------------------------------------------
SQL_ID gahr597f78j0d, child number 0
-------------------------------------
select /*+ parallel(d, 4) parallel (e, 4) */ d.dname, avg(e.sal),
max(e.sal) from dept d, emp e where d.deptno = e.deptno group by
d.dname order by max(e.sal), avg(e.sal) desc
(page 180)
Plan hash value: 3078011448
--------------------------------------------------
|
Id | Operation
| Name | Rows | TQ |IN-OUT
| PQ Distrib |
--------------------------------------------------
|
0 | SELECT STATEMENT
| | | |
| |
| 1 | PX COORDINATOR
| | |
| | |
| 2 | PX SEND QC
(ORDER) | :TQ10004 | 4 |
Q1,04 | P->S | QC (ORDER) |
| 3 | SORT ORDER
BY | |
4 | Q1,04 | PCWP | |
| 4 |
PX RECEIVE |
| 4 | Q1,04 | PCWP | |
|
5 | PX SEND RANGE |
:TQ10003 | 4 | Q1,03 | P->P | RANGE |
|
6 | HASH GROUP BY
| | 4 | Q1,03 | PCWP |
|
| 7 | PX
RECEIVE | | 14
| Q1,03 | PCWP | |
| 8
| PX SEND HASH |
:TQ10002 | 14 | Q1,02 | P->P | HASH |
|*
9 | HASH JOIN BUFFERED
| | 14 | Q1,02 | PCWP |
|
| 10 | PX
RECEIVE | | 4 |
Q1,02 | PCWP | |
| 11
| PX SEND HASH |
:TQ10000 | 4 | Q1,00 | P->P | HASH |
|
12 | PX BLOCK ITERATOR |
| 4 | Q1,00 | PCWC | |
|*
13 | TABLE ACCESS FULL |
DEPT | 4 | Q1,00 | PCWP | |
|
14 | PX RECEIVE
| | 14 | Q1,02 | PCWP |
|
| 15 | PX SEND
HASH | :TQ10001 | 14 | Q1,01 |
P->P | HASH |
| 16 | PX
BLOCK ITERATOR | | 14 |
Q1,01 | PCWC | |
|* 17 |
TABLE ACCESS FULL | EMP | 14 |
Q1,01 | PCWP | |
--------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("D"."DEPTNO"="E"."DEPTNO")
13 - access(:Z>=:Z AND :Z<=:Z)
17 - access(:Z>=:Z AND :Z<=:Z)