1. That to read an execution plan, look for the innermost indented statement. That is generally the first statement 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 executed?
| 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.
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.
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)
(Cost=489 Card=14436 Bytes=404208)
6 4 INDEX (RANGE SCAN) OF 'XIN3BAG_TAG_FLT_LEG' (UNIQUE)
(Cost=1501 Card=10121 Bytes=313751)
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)
(Cost=371 Card=1426 Bytes=41354)
12 10 INDEX (FAST FULL SCAN) OF 'XIN8OPS_FLT_LEG' (UNIQUE)
(Cost=489 Card=14436 Bytes=404208)
13 9 INDEX (RANGE SCAN) OF 'XIN2BAG_TAG_FLT_LEG' (UNIQUE)
(Cost=2 Card=2 Bytes=54)
Answer: The order of operations is 5, 6, 4, 3, 2, 11, 12, 10, 13, 9, 8, 7, 1.
