Database Reference
In-Depth Information
Plan hash value: 2966233522
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS FULL| T |
-----------------------------------
Several situations lead to multiple execution plans for a given SQL statement, such as when an index has been
added or simply because data (and therefore its object statistics) has changed. Basically, each time the environment
that the query optimizer evolves in changes, different execution plans may be generated. Such an output is useful
when you're questioning the performance of a SQL statement that you think has been running without problems for
some time. The idea is to check whether a SQL statement has been executed with several execution plans over a period
of time. If this is the case, infer what could be the reason leading to the change based on the available information.
Interpreting Execution Plans
I've always found it surprising how little documentation there is about how to read execution plans, especially
since there seem to be so many people who are unable to correctly read them. I attempt to address this problem by
describing the approach I use when reading an execution plan. Note that details about the different operations aren't
provided here; rather, I provide the basics you need in order to understand how to walk through execution plans.
I give detailed information about the most common operations in Part 4.
■
parallel processing makes the interpretation of execution plans more difficult. the reason is quite simple:
several operations run concurrently. this section, to keep the description as simple as possible, doesn't pretend to cover
parallel processing. information about execution plans processed in parallel is provided in Chapter 15.
Caution
Parent-Child Relationship
An execution plan is a tree describing not only which order the SQL engine executes operations in but also what
the relationship between operations is. Each node in the tree is a
row source operation
(actually implemented as a
function written in C)—for example, a table access, a join, or a sort. Between operations (nodes), there's a parent-
child relationship. Understanding those relationships is essential to correctly reading an execution plan. When an
execution plan is displayed in a textual form, the rules governing the parent-child relationship are the following:
•
A parent has one or multiple children.
•
A child has a single parent.
•
The only operation without a parent is the root of the tree.
•
Children are indented to the right, with respect to their parent. Depending on the method
used to display the execution plan, the indentation could be a single space character, two
spaces, or something else. It doesn't really matter. The essential point is that all children of a
specific parent have the very same indentation.
•
A parent is placed before its children (the ID of the parent is less than the ID of the children).
