 |
|
Reading an Execution
Plan
Oracle Tips by
Burleson
|
Execution plans are often very difficult to
interpret. While the beginner can scan the execution plan looking for
TABLE ACCESS FULL, the more sophisticated experts closely examine the
order of execution within an execution plan. In general, execution
plans are read inside-out, starting with the most indented operation.
Here are some general rules for reading an explain plan.
1. The first statement is
the one that has the most indentation.
2. If two statements appear
at the same level of indentation, the top statement is executed first.
To see how this works,
let’s reformat the execution plan from before to make the steps easier
to see. Before you read on, see if you can place the steps in the
actual order of execution.
SELECT
STATEMENT
SORT ORDER BY
NESTED LOOPS
NESTED
LOOPS
TABLE ACCESS BY INDEX ROWID
SUBSCRIPTION
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX FULL SCAN SUBSC_PUB_NAME_IDX
TABLE ACCESS BY INDEX ROWID
BOOK
INDEX UNIQUE SCAN BOOK_ISBN
TABLE ACCESS BY INDEX ROWID
PAGE
INDEX UNIQUE SCAN ISBN_SEQ_IDX
The indentation is clear, so we can work from
inside-out, top-down and arrive at the following sequence of steps:
1. Bitmap index full scan
on the subsc_pub_name_idx index
2. Bitmap conversion to
ROWIDs
3. Index unique scan on the
book_isbn index
4. Table access by ROWID on
the subscription table
5. Table access by ROWID on
the book table
6. Index unique scan on the
isbn_seq_idx index
7. Nested Loops scan of the
result set
8. Table access by ROWID on
the page table
9. Nested loop scan of the
result set
10. Sorting the final result
set
Hopefully, this will give you enough understanding to
accurately know the order of the execution of steps in a SQL execution
plan.
Also, please be aware that the execution plan
output from explain plan is very different than the execution plan as
stored in the ol$hints table. As you may know, in Oracle8i
we have optimizer plan stability that is implemented via the Oracle
create outline command. To see the difference, refer to Chapter
13, or execute the outline.sql script from the Oracle Press Web
site.
For now, do not be concerned if you do not
understand all of the different types of table access methods. I will
be covering these access methods on an as-needed basis when we delve
into the tuning details in subsequent chapters.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
