《Pro Oracle SQL》-Chapter 6-6.1 Explain Plans-之三
Reading the Plan 解读计划 (page163)
Before I dive further into capturing actual execution plan data, I want to make sure you are
comfortable with reading a plan. I’ve already discussed the importance of the PARENT_ID column in
making it easier for you to break a long, complex plan down into smaller, more manageable sections.
Breaking a plan down into smaller chunks will help you read it, but you need to know how to approach
reading a whole plan from start to finish.
在我深入讲解实际执行计划之前，我想确认你有自信解读计划。我已经讨论了用PARENT_ID列将一个长而复杂的计划分解成小段可管理段的重要性。将计划分解成小块有利于你读它，但是你(更)需要知道如何从头到尾解读整个计划。
There are three ways that will help you read and understand any plan: 1) learn to identify and
separate parent-child groupings, 2) learn the order in which the plan operations execute, and 3) learn
to read the plan in narrative form. I have learned to do these three things so that when I look at a plan, my eye moves through the plan easily and I notice possible problem areas quickly. It can be
frustrating and a bit slow at first, but given time and practice, it will become second nature.
有三种方法帮助你解读和理解任何计划：1)学会标示和分离父-子组。2)学会(查看)计划操作执行的顺序。3)学会用叙述的形式解读计划。我掌握了这三种
方法，所以当我看一个计划时，我的眼睛能轻松的扫视计划，迅速的定位可能的问题区域。刚开始的时候肯定会有些郁闷和慢，但是假以时日不断的练习，就成了习
惯。
The first place to start is with execution order. The plan is displayed in order by the sequential ID
of operations. However, the order in which each operation executes isn’t accomplished in a precise
top-down fashion. Using the visual cues of the indentation of the operations, you can quickly scan a
plan and look for the operations that are the most indented. The operation that is most indented is
actually the first operation that will be executed. If there are multiple operations at that same level, the operations are executed in a top-down order.
第一步是从执行顺序入手。计划是根据操作的序列ID展示的。然而，每一步操作的执行顺序不完全按照从上到下的风格排列。通过操作的缩进的视觉线索，你能快
速的扫描一个计划，查找缩进最深的操作。缩进最深的操作实际上是最先执行的。如果有多个操作具有相同的(缩进)级别，将按照从上到下的顺序执行。
For reference, I’m going to re-list the example plan here in Listing 6-5 so that you don’t have to
flip back a few pages to the original example in Listing 6-1.
为了引述方便，我重新把例子计划列在列表6-5中，你就不需要翻回前面几页找原来在列表6-1中的例子。
Listing 6-5. EXPLAIN PLAN Example (Repeated)
-----------------------------------------------------
| 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")
At a glance, you can see that lines 6 and 8 are the most deeply indented. Line 6 will execute first
and pass the rowids from the index full scan to its parent (line 5). Line 8 will execute next and pass its row source to its parent (line 7). Steps will continue to execute from most indented to least indented with each step passing row source data to its parent until all steps complete. In order to help see the execution order more clearly, Listing 6-6 executes a query similar to the query used in Listing 6-3 that reads from the P LAN_TABLE and orders the output in execution order.
第一眼，你就能看出第6,8行缩进的最深。第6行最先执行，把全盘索引扫描的rowid传递给它的上一级。第8行接着执行，把它的行源传递给它的上一
级。(操作)步骤由里向外，每一步都将行源传递给它的上一步，直到所有步骤完成。为了让执行顺序看的更清楚。列表6-6执行了同列表6-3相似的查询，读
取PLAN_TABLE按执行顺序排序输出。
Listing 6-6. Plan Operations Displayed in Execution Order 按执行顺序显示计划操作
SQL>select id, parent_id, operation
2 from (
3 select level lvl, id, parent_id, lpad(' ',level) || operation || ' ' || options
4 || ' ' || object_name as operation
5 from plan_table
6 start with id = 0
7 connect by prior id = parent_id
8 )
9 order by lvl desc, id;
ID PARENT_ID OPERATION
---------- ---------- --------------------------------------------------
6 5 INDEX FULL SCAN DEPT_LOCATION_IX
8 7 TABLE ACCESS FULL LOCATIONS
5 4 TABLE ACCESS BY INDEX ROWID DEPARTMENTS
7 4 SORT JOIN
4 3 MERGE JOIN
9 3 INDEX UNIQUE SCAN COUNTRY_C_ID_PK
3 2 NESTED LOOPS
10 2 TABLE ACCESS FULL REGIONS
2 1 HASH JOIN
11 1 TABLE ACCESS FULL EMPLOYEES
1 0 HASH JOIN
0 SELECT STATEMENT
I often use an analogy between parent-child relationships in a plan and real life parent-child
relationships. A real child doesn’t just spontaneously combust into being; a parent is required to
“instantiate” the child into being. But, like most any parent will tell you, one of the greatest things
about kids is that (sometimes) you can get them to do work for you. This applies to parent-child
operations in a plan. The child takes direction from its parent and goes to do a piece of work. When
the child completes that work, it reports back to the parent with the result. So, even though an index
operation occurs before its parent (for example, step 6 executes before its parent in step 5), the child
wouldn’t have meaning or existence without its parent. This is why it’s important to always keep the
parent-child relationships in mind as it helps make sense of the execution order.
我经常拿计划中的父子关系同现实生活中的父子关系做类比。实际中的“子”不会自发而存在，而是需要由“父”先初始化“子”(子才能用)。但是，像大多数父
亲告诉你的，关于小孩最了不起的事情就是你可以让他为你打酱油。这同样适用于计划操作中的父子关系。“子”从“父”获得指令，做一小部分工作。当“子”完
成工作，它返回给“父”结果。这样，即使一个索引操作发生在它的“父”之前(例如，第6步执行在它的“父”第5步之前)，“子”没有“父”就没有意义，或
者不能存在。这就是为什么总是铭记父子关系是如此重要，有助于你理解执行顺序。
One of the most helpful sections of the explained output is the section named Predicate
Information. In this section, the ACCESS_PREDICATES and FILTER_PREDICATES columns are displayed. These columns are associated with a line (denoted by the ID column) in the list of plan operations. You’ll notice that for each plan operation that has an access or filter predicate associated with it, there is an asterisk (*) next to the ID. When you see the asterisk, you know to look for that ID number in the Predicate Information section to see which predicate (condition in the WHERE clause) was related to that operation. Using this information you can confirm that columns were correctly (or not) used for index access and also to determine where a condition was filtered.
解释输出最有帮助的一段就是被称之为谓词信息的那段。在该段中有展示ACCESS_PREDICATES和FILTER_PREDICATES列。这些列
关联操作计划列表中的行号(标记成ID列)。你可以注意到每一个执行计划操作都有一个访问或者过滤谓词关联着它，这是挨着ID的星号(*)。当你看到星
号，通过查找在谓词信息中的ID号去看(在WHERE子句的条件中)哪个谓词关联到了那个操作。用这个信息你能确认那些列是否正确的使用了索引访问且判断
条件在哪过滤的。
Filtering late is a common performance inhibitor. For example, if you wanted to move a pile of 100
rocks from the front yard to your back yard but only needed rocks that weighed 5-10 pounds, would you want to move all 100 rocks and then remove the ones you needed, or would you simply want to carry the ones that were the correct weight? In general, you’d want to only take the rocks you need, right?
“晚”过滤通常是性能的阻化剂。例如，如果你想搬1堆100块的石头，从你的前院到后院，但是你想要的石块重量大约5-10磅。你将搬动所有的100块(到后院)，再取出你需要的？还是只是简单的搬动你想要重量的石块？一般而言，你只是搬动你需要的，对吧？
Using the filter predicate information can help you verify that unneeded rows are filtered out of
your result set as early as possible in the plan. Just like it wouldn’t make much sense to carry a whole
bunch of extra rocks to the back yard, it wouldn’t make much sense to carry rows through a whole set of plan operations that ultimately will not be included in the final result set. You will use the filter
information to verify that each condition is applied as early in the plan as possible. If a filter is applied
too late, you can adjust your SQL or take other steps (like verifying statistics are up to date) to ensure
your plan isn’t working harder than it needs to.
使用过滤器谓词信息能帮助你确认结果集中不需要的行是否尽可能早的在计划中就过滤了。就好像你通常不会搬动而外的石块去后院一样，也通常不会搬动最终不包
含在结果集的行贯穿整个计划操作。你将用过滤信息确认每一个条件在计划中应用的越早越好。如果过滤器应用的太晚，你能调整你的SQL或者采用其他的步骤
(像确认统计是否最新)来确保你的计划是否做了很多垃圾工作。
Finally, learning to read the plan as if it were a narrative can be extremely helpful. For many
people, converting the set of plan operations into a paragraph of text can facilitate understanding how the plan executes better than any other method. Let’s convert your example plan into a narrative and see if it makes it easier for you to read and understand. The following paragraph is a sample narrative for the example plan.
最后，学会解读计划成记叙(文)将是非常有帮助的。对于许多人来说，把一套计划操作转换成一段文本，相比其他方法而言，能够更容易理解计划操作是如何执行的。让我转换例子计划成一段记叙文，看是否更容易让你读和理解。下面一段就是例子计划的简单记叙文。
In order to produce the result set for this SELECT statement, rows from the DEPARTMENTS table
will be accessed utilizing a full scan of the index on the DEPARTMENTS.LOCATION_ID column.
Using a full scan of the LOCATIONS table, rows will be retrieved and sorted by LOCATION_ID and
then merged with the rows from DEPARTMENTS to produce a joined result set of matching rows
containing both DEPARTMENTS and LOCATIONS data. This row set, which I’ll call DEPT_LOC, will be
joined to the COUNTRIES table and will iteratively match one row from DEPT_LOC using the
COUNTRY_ID to find a matching row in COUNTRIES. This result set, which I’ll call DEPT_LOC_CTRY,
now contains data from DEPARTMENTS, LOCATIONS, and COUNTRIES and will be hashed into
memory and matched with the REGIONS table data using the REGION_ID. This result set,
DEPT_LOC_CTRY_REG, will be hashed into memory and matched with the EMPLOYEES table using
the DEPARTMENT_ID to produce the final result set of rows.
为了生成这条SELECT语句的结果集，将通过全扫描在DEPARTMENTS.LOCATION_ID列的索引访问DEPARTMENTS表的行。使用全扫描LOCATIONS表得到的行集用LOCATION_ID排序，且同来自DEPARTMENTS的行集融合，生成一连接的匹配行集，包含
DEPARTMENTS和LOCATIONS数据。我把这个行集称之为DEPT_LOC,将用来连接COUNTRIES表。通过COUNTRY_ID在
CONTRIES找到匹配的行，迭代匹配
DEPT_LOC中的行。这个行集称之为
DEPT_LOC_CTRY, 它包含来至
DEPARTMENTS, LOCATIONS, 和COUNTRIES 的数据。将哈希入内存再通过REGION_ID匹配来至REGIONS表的数据。得到结果集
DEPT_LOC_CTRY_REG，再次被哈希，再通过
DEPARTMENT_ID匹配EMPLOYEES表生成最终的结果集。
To produce this narrative, I simply walk through the steps of the plan in execution order and write
out the description of the steps and how they link (join) to each other. I progress through each set of
parent-child operations until all the steps are complete. You may find that creating a narrative helps
you grasp the overall plan with a bit more clarity. For more complex plans, you may find that breaking
out just a few key portions of the whole plan and writing it out in narrative form will help you better
understand the flow of operations. The key is to use the narrative to help make better sense of the
plan. If you find it harder to do this, then just stick with the plan as it is. But, taking time to learn to
convert a plan into a narrative form is a good skill to learn as it can help you describe what your query
is doing in a way that doesn’t require anyone even looking at plan output. It’s similar to giving verbal
directions on how to get to the nearest shopping mall. You don’t necessarily have to have the map to
be able to get from point A to point B.
为了生成这段记叙文，我简单的按执行顺序过了一遍步骤，写出每步的描述，以及他们之间如何连接的。我梳理每一组父子关系直到所有的步骤完成。你将发现创建
一篇记叙文对于你非常清晰的掌握整篇计划是非常有帮助的。越复杂的计划，你会发现，将其分解成几个关键的部分，然后写成记叙文将使你更好的理解操作流程。
关键是使用记叙文能让你更好的理解计划。如果你发现这样做本身就很烦，那还不如直接看计划。但是花时间学会转换计划成记叙文是一个好的技能。因为它能帮你
描述你的查询做什么，而不需要其他人去看计划输出。就如同给出一口头指令，如何到达最近的超市。你没有必要有一张地图才能从A点走到B点。