《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将阻止隐式的调用确保你获得最近执行语句的计划。
----------------------------------------------------------------------------------------------------------------------------------------------------