When Codd and Date created the relational data model, the
execution plan was an afterthought, largely because the SQL
optimizer was always supposed to generate the best execution
plan, and hence, there was not real need to understand the
internal machinations of Oracle execution plans.
However,
in the real world, all SQL tuning experts must be proficient in
reading Oracle execution plans and understand the steps within a
explain plans and the sequence that the steps are executed.
To successfully understand an explain plan you must be able to
know the order that the plan steps are executed.
Reading an explain plan is important for many
reasons, and Oracle SQL tuning experts reveal the explain plans
to check many things:
·
Ensure that the tables will be
joined in optimal order.
·
Determine the most restrictive
indexes to fetch the rows.
·
Determine the best internal join
method to use (e.g. nested loops, hash join).
·
Determine that the SQL is
executing the steps in the optimal order.
Reading SQL execution plans has always been
difficult, but there are some tricks to help determine the
correct order that the explain plan steps are executed.
Ordering the
sequence of execution plan steps
By Robert Freeman:
SQL execution plans are interpreted using a
preorder traversal (reverse transversal) algorithm which you
will see below.
Preorder traversal is a fancy way of saying:
1.
That
to read an execution plan, look for the innermost indented
statement. That is generally the first
statement executed but NOT always! (see
example here where the innermost step is not the first step
executed).
2. In
most cases, if there are two statements at the same level, the
first statement is executed first.
In other words, 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, take a look at this
plan. Which operation is first to execute?
---------------------------------------------------------------------------
| Id
| Operation
| Name | Rows
| Bytes | Cost (%CPU)| Time
|
---------------------------------------------------------------------------
|
0 | SELECT STATEMENT
|
|
10 |
650 |
7 (15)|
00:00:01 |
|*
1 | HASH
JOIN
|
|
10 |
650 |
7 (15)|
00:00:01 |
|
2 |
TABLE ACCESS FULL| JOB
|
4 |
160 |
3
(0)| 00:00:01 |
|
3 |
TABLE ACCESS FULL| EMP
|
10 |
250 |
3
(0)| 00:00:01 |
---------------------------------------------------------------------------
The answer is that the full table scan
operation on the job
table will execute first.
Let's look at another example plan and read it…
ID Par
Operation
0
SELECT STATEMENT Optimizer=FIRST_ROWS
1
0
TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2
1
NESTED LOOPS
3
2
TABLE ACCESS (FULL) OF 'DEPT'
4
2
INDEX (RANGE SCAN) OF 'IX_EMP_01' (NON-UNIQUE)
By reviewing this hierarchy of
SQL execution steps, we see that the order of operations is 3,4,
2, 1.
Here is the graph for
this execution plan:
To see how this query executes,
we traverse the tree in reverse order. From the left most,
deepest child, traverse the tree moving up, and to the right
through each branch.
By reviewing this hierarchy of SQL execution steps, we see that the order of operations is 3,4,
2, 1:
SEQ ID
Par Operation
0
SELECT STATEMENT Optimizer=CHOOSE
3
1
0
TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
4
2
1 NESTED
LOOPS
2
3
2
TABLE ACCESS (FULL) OF 'DEPT'
1
4
2
INDEX (RANGE SCAN) OF 'IX_EMP_01' (NON-UNIQUE)
Understanding the sequence of
explain plan steps is a critical skill, so let's try some more
examples:
Consider this SQL query:
select
a.empid,
a.ename,
b.dname
from
emp a,
dept b
where
a.deptno=b.deptno;
We get this execution plan:
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=40
Card=150000 Bytes=3300000)
1
0 HASH
JOIN (Cost=40 Card=150000 Bytes=3300000)
2
1
TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1
Bytes=10)
3
1
TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=150000
Bytes=1800000)
What is the order of operations here?
Answer: Execution
plan steps are 2, 3, 1
Consider this query:
select
a.empid,
a.ename,
b.dname
from
emp a,
dept b
where
a.deptno=b.deptno;
We get this execution plan:
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=864
Card=150000 Bytes=3300000)
1
0
HASH JOIN (Cost=864 Card=150000 Bytes=3300000)
2
1
TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=826
Card=1 Bytes=10)
3
2
INDEX (FULL SCAN) OF 'IX_DEPT_01' (NON-UNIQUE)
(Cost=26 Card=1)
4
1
TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=150000 Bytes=1800000)
What is the order of operations
here?
Answer: Execution
plans steps are 3, 2, 4, 1
Here is the same query, but slightly different
plan:
select
a.empid,
a.ename,
b.dname
from
emp a,
dept b
where
a.deptno=b.deptno;
We get this execution plan:
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE
(Cost=39 Card=150000 Byte=3300000)
1 0
NESTED LOOPS (Cost=39 Card=150000 Bytes=3300000)
2
1
TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1
Bytes=10)
3
1
TABLE ACCESS (FULL) OF 'EMP' (Cost=37 Card=150000 Bytes=1800000)
What is the order of operations
here?
Answer: Execution
plans steps are
2,
3, 1
Let's find the SQL execution steps for a three
table join:
select
a.ename,
a.salary,
b.dname,
c.bonus_amount,
a.salary*c.bonus_amount
from
emp a,
dept b,
bonus c
where
a.deptno=b.deptno
and
a.empid=c.empid;
What is the order of operations here?
Execution
Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=168 Card=82
Bytes=3936)
1
0
TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=1
Bytes=12)
2
1
NESTED LOOPS (Cost=168 Card=82 Bytes=3936)
3
2
MERGE JOIN (CARTESIAN) (Cost=4 Card=82 Bytes=2952)
4
3
TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1
Bytes=10)
5
3
BUFFER (SORT) (Cost=2 Card=82 Bytes=2132)
6
5
TABLE ACCESS (FULL) OF 'BONUS' (Cost=2 Card=82 Bytes=2132)
7
2
INDEX (RANGE SCAN) OF 'IX_EMP_01' (NON-UNIQUE)
(Cost=1 Card=1)
This is a little tougher….
The execution order is
4,6,5,3,7,2,1.
Let's diagram it!
Here we see that step 2 has two children, three and seven,
and step 3 has two children, four and five. Step 5 has
a lone child, step 6.
Following our rules for preorder traversal, the
execution plan steps start at step 4.
Final Exam!
What are the steps for this execution plan?
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=2871 Card=2
Bytes=143)
1
0
UNION-ALL
2
1
SORT (GROUP BY) (Cost=2003 Card=1 Bytes=59)
3
2
FILTER
4
3
HASH JOIN (Cost=1999 Card=1 Bytes=59)
5
4
INDEX (FAST FULL SCAN) OF 'XIN8OPS_FLT_LEG' (UNIQUE)
6
4
INDEX (RANGE SCAN) OF 'XIN3BAG_TAG_FLT_LEG' (UNIQUE)
7
1
SORT (GROUP BY) (Cost=868 Card=1 Bytes=84)
8
7
FILTER
9
8
NESTED LOOPS (Cost=864 Card=1 Bytes=84)
10 9
HASH JOIN (Cost=862 Card=1 Bytes=57)
11 10
INDEX (FAST FULL SCAN) OF 'XIN1SCHED_FLT_LEG'
(UNIQUE)
12 10
INDEX (FAST FULL SCAN) OF 'XIN8OPS_FLT_LEG' (UNIQUE)
13 9
INDEX (RANGE SCAN) OF 'XIN2BAG_TAG_FLT_LEG' (UNIQUE)
Answer: The order
of operations is 5, 6, 4, 3, 2, 11, 12, 10, 13, 9, 8, 7, 1.
Another exception to the rule, by Steve Karam:
The Oracle documentation says that the most indented line
is the first to be executed, but as we have already noted that
not always correct.
It's the first indented line required to satisfy the parent step
without further requirements.
When a query is run against an Oracle database, Oracle must
determine the access path to fetch the required data. It does
this by analyzing the requirements of the query and calculating
dependencies to return the data requested in a timely fashion.
Optimizer statistics are used to choose the most optimal access
path, and the final result is returned as an execution plan. The
execution plan lists all operations required to satisfy the
requirements of the query. This includes table access, index
access, joins, filters, sorting, and other operations. The
execution plan is ordered based upon the required steps to
return the requested data. Two steps on the same indented level
are part of the same parent operation. For instance:
1 HASH JOIN
2 TABLE ACCESS FULL TABLE1
3 TABLE ACCESS FULL TABLE2
In the example above, the two full table scans (TABLE ACCESS
FULL) are on the same indented level, and are therefore both
required to satisfy the parent requirement which is in this case
a hash join. Since execution plans are ordered by required
operation and ID 1 cannot complete without first completing its
indented requirements, the first step to be executed is ID 2
(the table scan on TABLE1) followed by ID 3 (the table scan on
TABLE2). Once these are both executed we can execute the HASH
JOIN, as all of its requirements have been met. The order of
operations is therefore 2, 3, 1.
To read the explain plan, we must start with the first operation
and analyze its dependencies in order. Whenever a dependency is
met, the parent operation of that dependency is checked to see
if all of its requirements are met. In the example above this
was very easy because there was only one parent step with two
child steps. But what about the example below?
1 HASH JOIN
2 TABLE
ACCESS FULL TABLE1
3 TABLE ACCESS BY INDEX ROWID
TABLE2
4 INDEX RANGE SCAN TABLE2_IDX
In this case the parent HASH JOIN requires
two inputs: a full table scan on TABLE1 and the results of an
index access on TABLE2. To read this explain plan, we start at
the beginning which is the HASH JOIN. In order to satisfy this
join, the first required child step (as noted by indentation) is
a full table scan on TABLE1, so the full table scan is
executed. The next requirement of the join is an index read on
TABLE2; however, this step cannot yet be executed because it has
its own dependencies. First an index range scan on TABLE2_IDX
is required. This index read satisfies the parent operation
which can then be run. Now that the full table scan and indexed
table scan are complete, the join operation can complete.
The order of operations here is therefore: 2, 4, 3, 1.
Generally the rule of thumb is that you can start with the
most indented operation and start from there. However, this is
not always true. The most indented item is simply the deepest
child requirement overall. To read an explain plan, we must go
down the list of steps from top to bottom and observe each
step's requirements. If an operation has requirements (indented
operations below it) then those steps are the next to execute.
If those steps have further requirements (indented operations
below it) then those steps must be executed. Whenever a
requirement can be met without further requirements, that
operation is completed. Once all required operations under a
parent operation are complete, the parent itself can be
executed. Consider the below explain plan, a much more complex
plan than the previous examples.
-------------------------------------------------------------------------
| Id | Operation |
Name | A-Time |
-------------------------------------------------------------------------
| 1 | SORT ORDER BY
| |00:00:30.33 |
| 2 |
HASH GROUP BY | |00:00:30.30 |
|* 3 | FILTER
| |00:00:28.28 |
|* 4
| HASH JOIN RIGHT OUTER |
|00:00:27.12 |
| 5 | TABLE
ACCESS FULL | DIM_E |00:00:00.01 |
|* 6 | HASH JOIN RIGHT OUTER
| |00:00:23.63 |
| 7
| TABLE ACCESS FULL | DIM_D
|00:00:00.01 |
|* 8 | HASH
JOIN | |00:00:20.72 |
| 9 | TABLE ACCESS BY INDEX ROWID |
DIM_C |00:00:00.87 |
| 10
| NESTED LOOPS |
|00:00:00.04 |
| 11 | NESTED
LOOPS | |00:00:00.01 |
|* 12 | TABLE ACCESS FULL |
DIM_A |00:00:00.01 |
|* 13
| TABLE ACCESS BY INDEX ROWID| DIM_B
|00:00:00.01 |
|* 14 | INDEX
RANGE SCAN | IDX_DIM_B_1 |00:00:00.01 |
|* 15 | INDEX RANGE SCAN |
IDX_DIM_C_1 |00:00:00.02 |
|* 16
| TABLE ACCESS FULL | FACT
|00:00:13.41 |
-------------------------------------------------------------------------
So in the example above, the "most
indented" rule is only a general rule...it doesn't apply on all
complex explain plans. In this example explain plan, there
are many steps required in order to return the final query
result. Let's look at the order of operations starting at the
top:
1. ID 1 SORT ORDER BY - This operation requires one input,
which in this case is a HASH GROUP BY (Id 2)
2. ID 2 HASH GROUP BY - This operation requires one input,
which in this case is a FILTER (Id 3)
3. ID 3 FILTER - This operation requires one input, which in
this case is a HASH JOIN RIGHT OUTER (Id 4)
4. ID 4 HASH JOIN RIGHT OUTER - This operation is a join and
requires two inputs. The inputs for this operation are TABLE
ACCESS FULL of DIM_E (Id 5) and a HASH JOIN RIGHT OUTER (Id 6)
5. ID 5 TABLE ACCESS FULL -This operation requires no
additional input and is required to satisfy the parent
operation. This operation is executed. ID 5 COMPLETE
6. ID 6 HASH JOIN RIGHT OUTER - This operation is a join and
requires two inputs. The inputs for this operation are TABLE
ACCESS FULL of DIM_D (Id 7) and a HASH JOIN (Id 8)
7. ID 7 TABLE ACCESS FULL - This operation requires no
additional input and is required to satisfy the parent
operation. This operation is executed. ID 7 COMPLETE
8. ID 8 HASH JOIN - This is a join operation requiring two
inputs. The inputs for this operation are TABLE ACCESS BY INDEX
ROWID of DIM_C (Id 9) and TABLE ACCESS FULL of FACT (Id 16)
9. ID 9 TABLE ACCESS BY INDEX ROWID - This operation requires
one input, which in this case is a NESTED LOOP (Id 10)
10. ID 10 NESTED LOOPS - This operation is a join and requires
two inputs. The inputs for this operation are another NESTED
LOOP (Id 11) and an INDEX RANGE SCAN (id 15)
11. ID 11 NESTED LOOPS - This operation is a join and requires
two inputs. The inputs for this operation are TABLE ACCESS FULL
of DIM_A (Id 12) and TABLE ACCESS BY INDEX ROWID on DIM_B (Id
13)
12. ID 12 TABLE ACCESS FULL - This operation requires no
additional input and is required to satisfy the parent
operation. This operation is executed. ID 12 COMPLETE
13. ID 13 TABLE ACCESS BY INDEX ROWID - This operation is a
table read based on an index result and requires one input,
which in this case is an INDEX RANGE SCAN on IDX_DIM_B_1 (Id 14)
14. ID 14 INDEX RANGE SCAN - This operation requires no
additional input and is required to satisfy the parent
operation. This operation is executed. ID 14 COMPLETE
Now that we have gone all the way to the most indented
child operation, we can start going backwards to see which
parent operations have been satisfied.
15. The
completion of ID 14 satisfies all input requirements for ID 13.
ID 13 is executed. ID 13 COMPLETE
16. The completion of IDs 12 and 13 satisfy all requirements
for ID 11. ID 11 is executed. ID 11 COMPLETE
17. ID 11 is now complete, but ID 10 still requires ID 15 to be
executed.
18. ID 15 is an INDEX RANGE SCAN on IDX_DIM_C_1 and requires no
additional input. The operation is executed. ID 15 COMPLETE
19. Now that ID 11 and 15 are complete, ID 10 has all required
inputs to continue and is executed. ID 10 COMPLETE
20. The completion of ID 10 satisfies the only input
requirement of ID 9 which is executed. ID 9 COMPLETE
21. The completion of ID 9 satisfies one of two requirements
for the HASH JOIN in ID 8. However, ID 16 is still required.
22. ID 16 is a TABLE ACCESS FULL on FACT and requires no
additional input. The operation is executed. ID 16 COMPLETE
23. Now that IDs 9 and 16 are complete, ID 8 requires no
additional input and is executed. ID 8 COMPLETE
24. ID 7 was already complete and ID 8 is now complete, so all
requirements of ID 6 are now complete. ID 6 is executed. ID
6 COMPLETE
25. ID 5 was already complete and ID 6 is now complete, so all
requirements of ID 4 are now complete. ID 4 is executed. ID
4 COMPLETE
26. ID 4 was the only required input of ID 3. Since ID 4 is
complete, ID 3 is executed. ID 3 COMPLETE
27. ID 3 was the only required input of ID 2. Since ID 3 is
complete, ID 2 is executed. ID 2 COMPLETE
28. ID 2 was the only required input of ID 1. Since ID 2 is
complete, ID 1 is executed. ID 1 COMPLETE
29. Results of the final step are the final output of the
execution plan.
The final order of operations by ID is:
5, 7, 12, 14, 13, 11, 15, 10, 9, 16, 8, 6, 4, 3, 2, 1
A simpler example is this:
1 SELECT
2 NESTED LOOP
3
TABLE ACCESS FULL (TABLE1)
4
TABLE ACCESS BY INDEX ROWID (TABLE2)
5
INDEX RANGE SCAN (TABLE2_IDX)
Even though 5 is the most indented, it is not first. 3
is first because it is the first indented step which
satisfies a parent step yet has no further requirements
(children)
The order of this one is 3, 5, 4, 2, 1
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
|
|
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
