Sequence of steps in Oracle SQL explain plan
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
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…
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:
a.empid,
a.ename,
b.dname
from
emp a,
dept b
where
a.deptno=b.deptno;
We get this 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:
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:
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!
Following our rules for preorder traversal, the execution plan steps start at step 4.
Final Exam! What are the steps for this 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.
to access non-Oracle systems you must use Oracle Heterogeneous Services.
Ida Bagus Enderajana
Recent Comments