《Pro Oracle SQL》--Chapter 6--6.1 Explain Plans--之一

Chapter 6--SQL Execution Plans    SQL执行计划
6.1 Explain Plan 解释计划
Karen Morton

You’ve seen quite a few execution plans in the first chapters of this book, but in this chapter I’m going
to go into detail about how to produce and read plans correctly.  I’ve built the foundation of knowledge you need to understand the most common operations you’ll see used in execution plans, but you need to put that knowledge into practice. 
    在本书最开始的几章里你已经见过很多执行计划了，在本章中我将继续深入讲解计划如何生成的和怎样正确的读它。我已经建立了理解执行计划大多数操作的基础知识，你需要将这些知识付诸实践。
    By the end of this chapter, I want you to feel confident that you can break down even the most
complex execution plan and understand how any SQL statement you write is being executed.  With the prevalence of development tools such as SQL Developer, SQL Navigator, and TOAD (just to name a
few), that can produce explain plan output, it is fairly easy to generate explain plans.  What isn’t as
easy is to get execution plans.  You may be wondering what the difference is between an explain plan
and an execution plan.  As you’ll see throughout this chapter, there can be a significant difference.
I’ll walk through the differences between explain plan output and actual execution plan
information.  You’ll learn how to compare the estimated plans with the actual plans and how to
interpret any differences that are present.  This is “where the rubber meets the road,” as race car
drivers would say.  
    在这章结束，我希望你充满自信，你能分解甚至最复杂的执行计划和理解你所写的SQL是怎么执行的。随着像SQL Developer,SQL Navigator,和TOAD(只列出几个)等开发工具的普及，它们能生成解释计划输出，相当容易的生成解释计划。为什么不是容易的获得执行计划。你可能会奇怪解释计划和执行计划有什么区别。贯穿整章你将看到它们有显著的区别。我将详细的阐述在解释计划同执行计划之间的区别。你将学会怎么比较评估计划和 实际计划以及怎么解释它们所呈现的不同。这就是赛车手所说的“轮子上路”了。

6.1 Explain Plans  (解释计划)  (page 153)
    The EXPLAIN PLAN statement is used to display the plan operations chosen by the optimizer for a SQL statement.  The first thing I want to clarify is that when you have EXPLAIN PLAN output, you have the estimated execution plan that should be used when the SQL statement is actually executed.  You do not have the actual execution plan and its associated rowsource execution statistics.  You have estimates only—not the real thing.  Throughout this chapter, I will make the distinction between actual and estimated plan output by referring to estimated information as explain plan output and terming
actual information as execution plan output.
    EXPLAIN PLAN 语句(命令)用于展示优化器对SQL语句所选择的计划操作，首先我要澄清的是当你有了解释计划输出，你就有了在SQL语句实际执行时会被用到的评估执行计 划。你不能得到实际的执行计划和它相关联的行资源执行统计(信息)。你仅仅是评估，而不是实际的事。贯穿本章，我对实际的和评估的计划输出区别开来，评估信息是指解释计划输出 而实际信息是指执行计划输出 。

Using Explain Plan
When using EXPLAIN PLAN to produce the estimated execution plan for a query, the output will show:
•  Each of the tables referred to in the SQL statement.
•  The access method used for each table.
•  The join methods for each pair of joined row sources.
•  An ordered list of all operations to be completed.
•  A list of predicate information related to steps in the plan.
•  For each operation, the estimates for number of rows and bytes manipulated by that step.
•  For each operation, the computed cost value.
•  If applicable, information about partitions accessed.
•  If applicable, information about parallel execution.
使用解释计划
当使用EXPLAIN PLAN来生成查询的评估执行计划，输出将展示：
• SQL语句中引用的每张表
• 每张表的访问方法
• 每一对连接行源的连接方法
• 需完成的所有操作的排序列表
• 关于计划每一步的谓词信息列表
• 对每一步操作，评估的行数和操作的字节数
• 对每一步操作，计算的成本值
• 如果可用，被访问的分区信息
• 如果可用，并行执行的信息
Listing 6-1 shows the explain plan output produced for a query that joins five tables.
列表6-1 显示一个连接5张表查询所生成的解释计划输出
Listing 6-1. EXPLAIN PLAN Example
SQL> explain plan for
  2  select e.last_name || ', ' || e.first_name as full_name,
  3         e.phone_number, e.email, e.department_id,
  4         d.department_name, c.country_name, l.city, l.state_province,
  5         r.region_name
  6    from hr.employees e, hr.departments d, hr.countries c,
  7         hr.locations l, hr.regions r
  8   where e.department_id = d.department_id
  9     and d.location_id = l.location_id
 10     and l.country_id = c.country_id
 11     and c.region_id = r.region_id;
 
Explained.
 
SQL>
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 2498281325

-----------------------------------------------------------------------------------------
| Id  | Operation                                              | Name                        | Rows  | Bytes    | Cost (%CPU)|
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                |                                   |   106   | 11872 |    13  (16)    |
|*  1 |  HASH JOIN                                            |                                   |   106   | 11872 |    13  (16)    |
|*  2 |   HASH JOIN                                           |                                   |    27    |  1917   |    10  (20)   |
|   3 |    NESTED LOOPS                                   |                                   |    27    |  1539   |     6  (17)     |
|   4 |     MERGE JOIN                                        |                                   |    27    |  1134   |     6  (17)    |
|   5 |      TABLE ACCESS BY INDEX ROWID       | DEPARTMENTS             |    27   |   513     |     2   (0)   |
|   6 |       INDEX FULL SCAN                             | DEPT_LOCATION_IX    |    27   |             |     1   (0)    |
|*  7 |      SORT JOIN                                        |                                     |    23   |   529    |     4  (25)   |
|   8 |       TABLE ACCESS FULL                         | LOCATIONS                 |    23   |   529     |     3   (0)   |
|*  9 |     INDEX UNIQUE SCAN                         | COUNTRY_C_ID_PK    |     1   |    15       |     0   (0)    |
|  10 |    TABLE ACCESS FULL                           | REGIONS                     |     4   |    56       |     3   (0)   |
|  11 |   TABLE ACCESS FULL                            | EMPLOYEES                |   107 |  4387      |     3   (0)   |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
   2 - access("C"."REGION_ID"="R"."REGION_ID")
   7 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
       filter("D"."LOCATION_ID"="L"."LOCATION_ID")
   9 - access("L"."COUNTRY_ID"="C"."COUNTRY_ID")

SQL> set autotrace traceonly explain
SQL>
SQL> l
  1  select e.last_name || ', ' || e.first_name as full_name,
  2                 e.phone_number, e.email, e.department_id,
  3                 d.department_name, c.country_name, l.city, l.state_province,
  4                 r.region_name
  5            from hr.employees e, hr.departments d, hr.countries c,
  6                 hr.locations l, hr.regions r
  7           where e.department_id = d.department_id
  8             and d.location_id = l.location_id
  9             and l.country_id = c.country_id
 10*            and c.region_id = r.region_id
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 2498281325
 
-----------------------------------------------------------------------------------------
| Id  | Operation                                                | Name                         | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                  |                                  |   106  | 11872 |    13  (16)    |
|*  1 |  HASH JOIN                                              |                                  |   106  | 11872 |    13  (16)    |
|*  2 |   HASH JOIN                                             |                                  |    27   |  1917  |    10  (20)    |
|   3 |    NESTED LOOPS                                      |                                  |    27   |  1539  |     6  (17)      |
|   4 |     MERGE JOIN                                          |                                  |    27  |  1134  |     6  (17)       |
|   5 |      TABLE ACCESS BY INDEX ROWID         | DEPARTMENTS          |    27   |   513   |     2   (0)        |
|   6 |       INDEX FULL SCAN                               | DEPT_LOCATION_IX |    27   |            |     1   (0)       |
|*  7 |      SORT JOIN                                          |                                  |    23   |   529   |     4  (25)      |
|   8 |       TABLE ACCESS FULL                           | LOCATIONS               |    23    |   529   |     3   (0)      |
|*  9 |     INDEX UNIQUE SCAN                           | COUNTRY_C_ID_PK   |     1    |    15    |     0   (0)       |
|  10 |    TABLE ACCESS FULL                             | REGIONS                    |     4    |    56    |     3   (0)       |
|  11 |   TABLE ACCESS FULL                              | EMPLOYEES                |   107  |  4387   |     3   (0)     |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
   2 - access("C"."REGION_ID"="R"."REGION_ID")
   7 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
       filter("D"."LOCATION_ID"="L"."LOCATION_ID")
   9 - access("L"."COUNTRY_ID"="C"."COUNTRY_ID")

    For this example, I used both the EXPLAIN PLAN command and the SQL*Plus AUTOTRACE command to generate the explain plan output.  Using AUTOTRACE automates the steps to generate a plan so that all you have to do is turn on AUTOTRACE (using the TRACEONLY EXPLAIN option) and execute a query.  The plan is generated and the output is displayed all in one step.  When using this method to generate a plan, neither the EXPLAIN PLAN command nor the TRACEONLY EXPLAIN option actually executes the query.  It only generates the plan that is estimated to be executed.  The development tool you use (SQL Developer, TOAD, etc.) should also have an option to generate explain plans.  I may be a bit old fashioned, but I find the text output often easier to read than the semi-graphical trees some of these common development tools use.  I don’t particularly need or care to see any little graphical symbols so I’m very happy with text output without any of the extra icons and such.  But, don’t feel you have to generate explain plans using these methods if you prefer to use your tool.  
    在这个例子中，我使用了EXPLAIN PLAN命令和SQL*Plus AUTOTRACE 命令来生成解释计划输出。使用AUTOTRACE自动展现生成一个计划的每一步，你只需打开AUTOTRACE(使用TRACEONLY EXPLAIN选项)然后执行查询。计划的生成和输出的展现一步实现。当用这种方法生成一个计划，既不是EXPLAIN PLAN命令也不是TRACEONLY EXPLAIN选项实际执行了查询。它仅仅生成了评估的执行计划。你用的开发工具(SQL Developer,TOAD,等)也有个选项生成执行计划。也许我有些过时了，但是我发现文本输出相比这些工具使用的半图形化树结构的(输出)更容易阅 读。我不必特别关注任何小的图形符号，文本输出没有这些而外的图标。如果你更偏好于你的工具(那就用吧)不是一定要你使用这些生成解释计划的方法。
    The information you see in explain plan output is generated by the EXPLAIN PLAN command and
stored in a table named PLAN_TABLE by default.  The AUTOTRACE command calls the display function from the supplied package named dbms_xplan to format the output automatically; you have to manually execute the query when using EXPLAIN PLAN (I’ll discuss dbms_xplan in more detail shortly).  For reference, Listing 6-2 shows the table description for the Oracle 11R2 PLAN_TABLE.
    你看到的解释计划输出是用EXPLAIN PLAN命令输出的，默认存储在一张叫PLAN_TABLE的表中。AUTOTRACE命令调用，自带的名为dbms_xplan的包中的，显示函数自动 的格式化输出，当你使用EXPLAIN PLAN时，你必须手动的执行查询(我待会详细讨论dbms_xplan)。例如，列表6-2显示了ORACLE 11R2 中PLAN_TABLE的表描述
Listing 6-2. PLAN _TABLE
SQL> desc plan_table
 Name                          Null?    Type
 ----------------------------- -------- ------------------
 STATEMENT_ID                           VARCHAR2(30)
 PLAN_ID                                      NUMBER
 TIMESTAMP                               DATE
 REMARKS                                  VARCHAR2(4000)
 OPERATION                              VARCHAR2(30)
 OPTIONS                                   VARCHAR2(255)
 OBJECT_NODE                         VARCHAR2(128)
OBJECT_OWNER                           VARCHAR2(30)
 OBJECT_NAME                            VARCHAR2(30)
 OBJECT_ALIAS                           VARCHAR2(65)
 OBJECT_INSTANCE                        NUMBER(38)
 OBJECT_TYPE                            VARCHAR2(30)
 OPTIMIZER                                 VARCHAR2(255)
 SEARCH_COLUMNS                         NUMBER
 ID                                                 NUMBER(38)
 PARENT_ID                                NUMBER(38)
 DEPTH                                       NUMBER(38)
 POSITION                                   NUMBER(38)
 COST                                           NUMBER(38)
 CARDINALITY                              NUMBER(38)
 BYTES                                          NUMBER(38)
 OTHER_TAG                               VARCHAR2(255)
 PARTITION_START                    VARCHAR2(255)
 PARTITION_STOP                      VARCHAR2(255)
 PARTITION_ID                              NUMBER(38)
 OTHER                                        LONG
 OTHER_XML                              CLOB
 DISTRIBUTION                           VARCHAR2(30)
 CPU_COST                                 NUMBER(38)
 IO_COST                                      NUMBER(38)
 TEMP_SPACE                             NUMBER(38)
 ACCESS_PREDICATES               VARCHAR2(4000)
 FILTER_PREDICATES                VARCHAR2(4000)
 PROJECTION                             VARCHAR2(4000)
 TIME                                             NUMBER(38)
 QBLOCK_NAME                            VARCHAR2(30)
    I’m not going to review every column listed but I wanted to provide a table description from which
you can do further study if you desire.  You’ll find more information in the Oracle documentation.
    我不打算查看列出每一列但是提供一个表描述供你进一步学习。你可以在Oracle文档找到更多的信息。
    The columns from the PLAN_TABLE shown in the explain plan output in Listing 6-1 are only a few of
the columns from the table.  One of the nice things about the dbms_xplan.display function is that it has the intelligence built in so that it will display the appropriate columns based on the specific plan
generated for each SQL statement.  For example, if the plan used partition operations, the
PARTITION_START, PARTITION_STOP, and PARTITION_ID columns would appear in the display.  The ability of dbms_xplan.display to automatically determine the columns that should be shown is a super feature that beats using the old do-it-yourself query against the PLAN_TABLE hands down.
     在列表6-1中的解释计划输出中展现的来至于PLAN_TABLE的列，只是其中的一小部分。最爽的的是dbms_xplan.display函数可以智 能的输出，根据每个SQL语句生成的特定计划展现特定的列。例如，如果计划中使用了分区操 作，PARTITION_START,PARTITION_STOP,和PARTITION_ID列将会展示，dbms_xplan.display自动 确定应该展现哪些列的能力，是超级特性，相比于老式的手动查询PLAN_TABLE来说。
    The columns shown in the display for the example query plan are: ID, OPERATION, OPTIONS,
OBJECT_NAME, CARDINALITY, BYTES, COST, TIME (this was included but elided in order to save space),
ACCESS_PREDICATES, and FILTER_PREDICATES.  These are the most typical display columns.  Table 6-1 provides a brief definition of each of these common columns.
    在例子中查询计划展现的列 有：ID,OPERATION,OPTIONS,OBJECT_NAME,CARDINALITY,BYTES,COST,TIME(包含了这列为节省空 间省略了)ACCESS_PREDICATES, 和 FILTER_PREDICATES。这些都是大部分典型展示的列。列表6-1提供了这些通用列的简短定义。
Table 6-1. Most Commonly Used PLAN_TABLE Columns
Column                                Description
ID                                         Unique number assigned to each step.
OPERATION                        Internal operation performed by the step.
OPTIONS                             Additional specification for the operation column (appended to OPERATION).
OBJECT_NAME                   Name of the table or index.
CARDINALITY                      Estimated rows accessed by the operation.
BYTES                                  Estimated bytes accessed by the operation.
COST                                   Weighted cost value for the operation as determined by the optimizer.
TIME                                     Estimated elapsed time in seconds for the operation.
ACCESS_PREDICATES     Conditions used to locate rows in an access structure (typically an index).
FILTER_PREDICATES        Conditions used to filter rows after they have been accessed. 

     One of the columns from the PLAN_TABLE that is not displayed in the plan display output when using the dbms_xplan.display function is the PARENT_ID column.  Instead of displaying this column value, the output is indented to provide a visual cue for the parent-child relationships within the plan.  I think it would be helpful to include the PARENT_ID column value as well for clarity, but you’ll have to write your own query against the PLAN_TABLE to produce the output to include that column if you want it.  I created a simple query that I use to display the PARENT_ID for each step and keep it handy for cases when the plan is complicated enough that the visual indentions are harder to line up and follow.  I still use the indentation but limit it to a single space per level.  Listing 6-3 shows using this for the same query executed for Listing 6-1.
    用dbms_xplan.display函数展示的计划输出中，有PLAN_TABLE的一列没有展示，PARENT_ID列。取代显示这列的值，输出打 算提供在计划中的父子关系的视觉线索。我认为输出PARENT_ID列值对于清晰(展示)而言更有益。但是你必须针对PLAN_TABLE写自己的查询输 出想要的列。我创建了一个简单的查询，用来展示每步的PARENT_ID，当计划非常复杂，视觉缩进难于对齐和跟随时，它就很有用了。我还是使用缩进，但 是限制每层(缩进)一个空格。列表6-3展示了这种(样式)的使用，同列表6-1执行一样的查询。
Listing 6-3. Displaying the PARENT_ID
SQL>select id, parent_id,
  2        lpad(' ',level) || operation || ' ' || options || ' ' || 
  3         object_name as operation
  4    from plan_table
  5   start with id = 0
  6   connect by prior id = parent_id ;
 
        ID  PARENT_ID          OPERATION
---------- ----------                   -----------------------------------------------
         0                                SELECT STATEMENT
         1          0                       HASH JOIN
         2          1                          HASH JOIN
         3          2                             NESTED LOOPS
         4          3                                MERGE JOIN
         5          4                                    TABLE ACCESS BY INDEX ROWID DEPARTMENTS
         6          5                                        INDEX FULL SCAN DEPT_LOCATION_IX
         7          4                                SORT JOIN
         8          7                                   TABLE ACCESS FULL LOCATIONS
         9          3                              INDEX UNIQUE SCAN COUNTRY_C_ID_PK
        10         2                           TABLE ACCESS FULL REGIONS
        11         1                        TABLE ACCESS FULL EMPLOYEES
     The PARENT_ID is helpful as operations in a plan are easiest to read if you keep in mind the parent-
child relationships involved in the plan.  Each step in the plan will have from zero to two children.  If
you break the plan down into smaller chunks of parent-child groupings, it will make it easier for you to
read and understand.
    如果你熟知计划中的父子关系，PARENT_ID有助于(理解)计划操作，是最容易读的(部分)。计划中的每一步都包含0-2个子(节点)。如果你分解整个计划成更小的父子组关系块，将更容易读和理解。
     In the example plan, you have operations with 0, 1, and 2 children.  A full table scan operation, for
example, doesn’t have any children.  See the line for in Listing 6-3.  Another example of an
operation with zero children is line 6.  If you glance down the PARENT_ID column, you’ll notice that
neither steps 6 nor 8 show up.  This means that these operations do not depend on any other operation in order to complete.  Both operations are children of other operations, however, and will pass the data they access to their parent step.  When an operation has no children, the rows (CARDINALITY column in the PLAN_TABLE) estimate shown represents the number of rows that a single iteration of that operation will acquire.  This can be a bit confusing when the operation is providing rows to an iterative parent.  For example, step 9 is an index unique scan operation which shows a row estimate of 1 row (see listing 6-1).  But the estimate doesn’t indicate the total number of rows accessed in that step.  The total is determined by the parent operation.  I’ll delve into this is more detail shortly.
    在例子计划中，操作们有0,1,2个子节点。全表扫描操作，不会有任何节点。看列表6-3.另一个零个子节点的例子是第6行。如果你看下 PARENT_ID列，你将注意到6和8都没有展现(子节点)。这意味着这些操作完全独立于其它任何操作。这些操作都是其它操作的子节点，而只会将它们访 问的数据传给上一级。当一个操作没有子节点，(PLAN_TABLE表的CARDINALITY列)评估的行数代表那级操作单步迭代所需要的行数。这里有 点混乱，操作提供行给迭代的上一级。例如，第9步是一个唯一索引扫描操作，显示评估1行(见列表6-1)。但是评估没有指出那一步所访问的总行数。总数由 上一步操作确定。等会我将深入详细讲解这点。
     The parent steps for steps 6 and 8—steps 5 and 7—are examples of single child operations.  In
general, operations with only one child can be divided into three categories:
•  Working operations receive a row set from the child operation and manipulate it further
before passing it on to its parent.
•  Pass-thru operations act simply as a pass-thru and don’t alter or manipulate the data from
the child in any way.  They basically serve to identify an operation characteristic.  The VIEW
operation is a good example of a pass-thru operation.
•  Iterative operations indicate that there are multiple executions of the child operation.  You’ll
typically see the word ITERATOR, INLIST, or ALL in these types of operation names.
     第6,8步的上一步，5,7步，是(操作只包含)单个子操作的例子。通常来说，只包含一个子操作的操作能分为三类：
• 加工操作：从子操作接收一行集且在将其传给上一级前进一步处理它(行集数据)
• 传递操作： 简单的把来至子操作的数据传递给上一级操作，不改变或者处理它。本质上用来标示一个操作的特性。VIEW操作就是传递操作的好例子。
• 迭代操作：标示子操作执行了多次。通常你将能在这些操作的名称中看到ITERATOR,INLIST或者ALL等字样。
    Both step 5 and step 7 are working operations.  They take the row sets from their children (steps 6
and 8) and do some additional work.  In step 5, the rowids returned from the index full scan are used to retrieve the DEPARTMENT table data blocks.  In step 7, the rows returned from the full scan of the
LOCATIONS table are sorted in order by the join column.
   第5,7步都是加工操作。他们把从子操作(6,8行)取得数据做一些加工。在第5步，从全索引扫描返回的rowids用来检索DEPARTMENT表的数据块。在第7步，LOCATIONS表的全表扫描返回的行用来挑选连接列。
    Finally, operations that have two children operate either iteratively or in succession.  When the
parent type is iterative, the child row sources are accessed such that for each row in row source A, B is accessed.  For a parent operation that works on the children in succession, the first child row source is accessed followed by an access of the second row source.  Join types such as NESTED LOOPS and MERGE JOIN CARTESIAN are iterative, as is the FILTER operation.  All other operations with two children will work in succession on their child row sources.
    最后，有两个子操作的操作，要么是迭代的要么是连续的。当上一级是迭代的，子级行源被访问时每一行源A,B都要被访问。当上一级操作对子集是连续的，第一 个子集行源被访问后，接着访问第二个行源。像NESTED LOOPS和MERGE JOIN CARTESIAN这样的连接类型是迭代的，就好像过滤操作一样。所有其他的带有两个子(操作)的操作对它们的子级行源都是以连接的方式工作的。
    The reason for this review is to highlight the importance of learning to take a “divide and
conquer” approach to reading and understanding plan output.  The larger and more complicated a
plan looks, the harder it often is to find the key problem areas.  If you learn to look for parent-child
relationships in the plan output and narrow your focus to smaller chunks of the plan, you’ll find it
much easier to work with what you see.
    这次复习的原因在于高亮的标示学习应用"分而治之"的方法去读和理解计划输出的重要性。一个看上去越大，复杂的计划，通常越难于找到关键问题区域。如果你学 会了在计划输出中查找父子关系，再缩小你的关注于计划中的某一小块。你将发现你所看到的(计划)非常容易(理解)。