Database Reference
In-Depth Information
----------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
|* 2 | HASH JOIN | | 1 | 79800 | 100 |
|* 3 | HASH JOIN | | 1 | 40000 | 40000 |
| 4 | TABLE ACCESS FULL| T1 | 1 | 20000 | 20000 |
| 5 | TABLE ACCESS FULL| T2 | 1 | 40000 | 40000 |
| 6 | TABLE ACCESS FULL | T3 | 1 | 80000 | 80000 |
----------------------------------------------------------------
2 - access("T2"."ID"="T3"."T2_ID")
3 - access("T1"."ID"="T2"."T1_ID")
When you encounter such problems, there's little you can do. In fact, there are no object statistics describing the
relationship between two tables. One possible way to correct a situation of this type is to use a SQL profile. Applying
one in this case would give you the following execution plan. (I cover what a SQL profile is, and how it works, in
Chapter 11.) For the moment, it's just important to realize that a solution exists. Notice that not only has the order of
the join changed (t2
➤
t3
➤
t1), but the access to the
t1
table is also different:
--------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
| 2 | NESTED LOOPS | | 1 | | 100 |
| 3 | NESTED LOOPS | | 1 | 100 | 100 |
|* 4 | HASH JOIN | | 1 | 100 | 100 |
| 5 | TABLE ACCESS FULL | T2 | 1 | 40000 | 40000 |
| 6 | TABLE ACCESS FULL | T3 | 1 | 80000 | 80000 |
|* 7 | INDEX UNIQUE SCAN | T1_PK | 100 | 1 | 100 |
| 8 | TABLE ACCESS BY INDEX ROWID| T1 | 100 | 1 | 100 |
--------------------------------------------------------------------------
4 - access("T2"."ID"="T3"."T2_ID")
7 - access("T1"."ID"="T2"."T1_ID")
On to Part 4
This chapter describes how to obtain execution plans through the
EXPLAIN PLAN
statement, dynamic performance
views, Automatic Workload Repository, Statspack, and some tracing facilities. As discussed for the first four
techniques, the
dbms_xplan
package is the tool of choice for extracting and formatting execution plans. With it,
you're able to get all the information you need simply, enabling you to understand execution plans. Some rules for
interpreting execution plans and for recognizing whether they're efficient are discussed as well.
Clearly, inefficient execution plans that cause performance problems should be optimized. The first chapter of
Part 4 starts covering this topic by describing the SQL optimization techniques available for that purpose. Note that
there are several techniques, since each of them can be applied in specific circumstances or just for the optimization
of particular problems.
