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.
