《Pro Oracle SQL》-Chapter 6-6.2 Execution Plans-之二

Collecting the Plan Statistics? 收集计划的统计(信息) (page 168)
??? The plan operations shown when no plan statistics are available is essentially the same as the
output from EXPLAIN PLAN.? To get to the heart of how well the plan worked, you need the plan’s
rowsource execution statistics.? These values tell you what actually happened for each operation in the plan.? This data is pulled from the V$SQL_PLAN_STATISTICS view.? This view links each operation row for a plan to a row of statistics data.? A composite view named V$SQL_PLAN_STATISTICS_ALL contains all the columns from V$SQL_PLAN plus the columns from V$SQL_PLAN_STATISTICS as well as a few additional columns containing information about memory usage.?? Listing 6-10 describes the
V$SQL_PLAN_STATISTICS_ALL view columns.
??? 当没有有效的计划统计(信息)，所展现的计划操作实际上同解释计划的输出是一样的。为了了解计划工作的核心，你需要计划的行源执行统计。这些值告诉你在计划中的每一步实际发生了什么。这些数据取自于V$SQL_PLAN_STATISTICS视图。该视图联接计划的每一操作行到统计数据的一行。组合视图V$SQL_PLAN_STATISTICS_ALL包含V$SQL_PLAN plus 和V$SQL_PLAN_STATISTICS的所有列，还有关于内存使用信息的列。列表6-10 描述了V$SQL_PLAN_STATISTICS_ALL视图的列。
Listing 6-10. The V$SQL_PLAN_STATISTICS_ALL View Description
SQL>desc v$sql_plan_statistics_all
?Name???????????????????????????????????????????? Null?????????????????????????? Type
?-----------------------------?????????????????? --------???????????????????? --------------------
?ADDRESS??????????????????????????????????????????????????????????????????? RAW(4)
?HASH_VALUE????????????????????????????????????????????????????????????? NUMBER
?SQL_ID????????????????????????????????????????????????????????????????????????? VARCHAR2(13)
?PLAN_HASH_VALUE??????????????????????????????????????????????????? NUMBER
?CHILD_ADDRESS????????????????????????????????????????????????????? ? RAW(4)
?CHILD_NUMBER???????????????????????????????????????????????????????? NUMBER
?TIMESTAMP??????????????????????????????????????????????????????????????? DATE
?OPERATION?????????????????????????????????????????????????????????????? VARCHAR2(30)
?OPTIONS?????????????????????????????? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?? VARCHAR2(30)
?OBJECT_NODE???????????????????????????????????????????????????????? VARCHAR2(40)
?OBJECT#?????????????????????????????????????????????????????????????????? NUMBER
?OBJECT_OWNER???????????????????????????????????????????????????? VARCHAR2(30)
?OBJECT_NAME?????????????????????????? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? VARCHAR2(30)
?OBJECT_ALIAS????????????????????????????????????????????????????????? VARCHAR2(65)
?OBJECT_TYPE????????????????????????????????????????????????????????? VARCHAR2(20)
?OPTIMIZER??????????????????????????????????????????????????????????????? VARCHAR2(20)
?ID???????????????????????????????????????????????????????????????????????????????? NUMBER
?PARENT_ID??????????????????????????????????????????????????????????????? NUMBER
?DEPTH??????????????????????????????????????????????????????????????????????? NUMBER
?POSITION?????????????????????????????????????????????????????????????????? NUMBER
?SEARCH_COLUMNS???????????????????????????????????????????????? NUMBER
?COST????????????????????????????????????????????????????????????????????????? NUMBER
?CARDINALITY???????????????????????????????????????????????????????????? NUMBER
?BYTES??????????????????????????????????????????????????????????????????????? NUMBER
?OTHER_TAG????????????????????????????????????????????????????????????? VARCHAR2(35)
?PARTITION_START?????????????????????????????????????????????????? VARCHAR2(64)
?PARTITION_STOP???????????????????????????????????????????????????? VARCHAR2(64)
?PARTITION_ID??????????????????????????????????????????????????????????? NUMBER
?OTHER?????????????????????????????????????????????????????????????????????? VARCHAR2(4000)
?DISTRIBUTION????????????????????????????????????????????????????????? VARCHAR2(20)
?CPU_COST????????????????????????????? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? NUMBER
?IO_COST??????????????????????????????????????????????????????????????????? NUMBER
?TEMP_SPACE?????????????????????????????????????????????????????????? NUMBER
?ACCESS_PREDICATES??????????????????????????????????????????? VARCHAR2(4000)
?FILTER_PREDICATES????????????????????????????????????????????? VARCHAR2(4000)
?PROJECTION??????????????????????????????????????????????????????????? VARCHAR2(4000)
?TIME????????????????????????????????????????????????????????????????????????? NUMBER
?QBLOCK_NAME?????????????????????????????????????????????????????? VARCHAR2(30)
?REMARKS??????????????????????????????????????????????????????????????? VARCHAR2(4000)
?OTHER_XML?????????????????????????????????????????????????????????? CLOB
?EXECUTIONS????????????????????????????????????????????????????????? NUMBER
?LAST_STARTS??????????????????????????????????????????????????????? NUMBER
?STARTS?????????????????????????????????????????????????????????????????? NUMBER
?LAST_OUTPUT_ROWS????????????????????????????????????????? NUMBER
?OUTPUT_ROWS???????????????????????????????????????????????????? NUMBER
?LAST_CR_BUFFER_GETS???????????????????????????????????? NUMBER
?CR_BUFFER_GETS??????????????????????????????????????????????? NUMBER
?LAST_CU_BUFFER_GETS???????????????????????????????????? NUMBER
?CU_BUFFER_GETS??????????????????????? ? ? ? ? ? ? ? ? ? ? ? ? NUMBER
?LAST_DISK_READS??????????????????????????????????????????????? NUMBER
?DISK_READS?????????????????????????????????????????????????????????? NUMBER
?LAST_DISK_WRITES????????????????????????????????????????????? NUMBER
?DISK_WRITES???????????????????????????????????????????????????????? NUMBER
?LAST_ELAPSED_TIME?????????????????????????????????????????? NUMBER
?ELAPSED_TIME????????????????????????????????????????????????????? NUMBER
?POLICY??????????????????????????????????????????????????????????????????? VARCHAR2(10)
?ESTIMATED_OPTIMAL_SIZE???????????????????????????????? NUMBER
?ESTIMATED_ONEPASS_SIZE??????????????????????????????? NUMBER
?LAST_MEMORY_USED????????????????????????????????????????? NUMBER
?LAST_EXECUTION???????????????????????????????????????????????? VARCHAR2(10)
?LAST_DEGREE?????????????????????????????????????????????????????? NUMBER
?TOTAL_EXECUTIONS????????????????????? ? ? ? ? ? ? ? ? ? ? ? NUMBER
?OPTIMAL_EXECUTIONS??????????????????????????????????????? NUMBER
?ONEPASS_EXECUTIONS????????????????????????????????????? NUMBER
?MULTIPASSES_EXECUTIONS????????????????????????????? NUMBER
?ACTIVE_TIME???????????????????????????????????????????????????????? NUMBER
?MAX_TEMPSEG_SIZE?????????????????????????????????????????? NUMBER
?LAST_TEMPSEG_SIZE???????????????????????????????????????? NUMBER
??? The columns containing the pertinent statistics information that relates to the output from
dbms_xplan.display_cursor all begin with the prefix LAST_.? When you use the format option of ALLSTATS LAST, the plan shows these column values for each row in the plan.? So, for each operation, you will know exactly how many rows it returned (LAST_OUTPUT_ROWS is shown in the A-Rows column), how many consistent reads occurred (LAST_CR_BUFFER_GETS is shown in the Buffers column), how many physical reads occurred (LAST_DISK_READS is shown in the Reads column), and number of times a step was executed (LAST_STARTS is shown in the Starts column).?? There are several other columns that will display depending on the operations that take place, but these are the most common.
?????? 包含中肯的统计信息的列，与dbms_xplan.display_cursor的输出关联，都以LAST_前缀开头。当你使用格式化选项ALLSATS LAST，计划将逐行显示这些列值。这样每一步操作，你都能准确的知道返回了多少行(LAST_OUTPUT_ROWS 显示在A-Rows列),发生了多少逻辑读(LAST_CR_BUFFER_GETS在Buffers列)，发生了多少物理读(LAST_DISK_READS 在 Reads 列)，每步执行的次数(LAST_STARTS 在Starts列)。还有几项其它的列将依照发生的操作而展示，但是上述是最常用的。

The dbms_xplan.display_cursor call signature is??????
dbms_xplan.display_cursor的调用原型 (也就是 DESC dbms_xplan.display_cursor )
?
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

In the example from Listing 6-8, the three parameters used were SQL_ID => null, CURSOR_CHILD_NO
=> null, and FORMAT => ALLSTATS LAST.? The use of nulls for the SQL_ID and CURSOR_CHILD_NO
parameters indicates that the plan for the last executed statement should be retrieved.? Therefore, you should be able to execute a statement, then execute
??????? select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
This will give you the plan output as shown in Listing 6-8.??

在列表6-8的例子中，三个参数使用了 SQL_ID => null, CURSOR_CHILD_NO => null, 和 FORMAT => ALLSTATS LAST。前两个使用null标示计划检索的是最后一条执行语句。因此你要先执行你的语句，再执行
???????????? select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
这样才能得到列表6-8的计划输出。
---------------------------------------------------------------------------
CAUTION? You may have noticed that I executed the SQL*Plus command SET SERVEROUTPUT OFF before executing the call to dbms_xplan.display_cursor.? This is a slight oddity that might catch you off-guard if you don’t know about it.? Whenever you execute a statement and SERVEROUTPUT is ON, a call to dbms_output is implicitly executed.? If you don’t turn SERVEROUTPUT OFF, then the last statement executed will be this dbms_output call. Using nulls for the first two parameters will not give you the SQL statement you executed, but instead will attempt to give you the plan for the dbms_output call.? Simply turning this setting OFF will stop the implicit call and ensure you get the plan for your most recently executed statement.
小心：你可能注意到我在执行调用dbms_xplan.display_cursor之前执行了SQL*Plus 命令SET SERVEROUTPUT OFF。这看上去有些奇怪，若你不知道可能你会忽略它。无论何时你执行语句且SERVEROUTPUT是ON，dbms_output将被隐式的调用。如果你没有设置SERVEROUTPUT OFF，最后的语句执行将是对dbms_output的调用。使用null作为头两个参数不能给出你所执行的SQL,而是企图给出你调用dbms_output的计划。简单的设置其为OFF将阻止隐式的调用确保你获得最近执行语句的计划。
----------------------------------------------------------------------------