How to read an Oracle SQL Execution Plan?

To execute any SQL statement Oracle has to derive an ‘execution plan’ . The execution plan of a query is a description of how Oracle will implement the retrieval of data to satisfy a given SQL statement. It is nothing but a tree which contains the order of steps and relationship between them

The basic rules of execution plan tree is below:

1. An excution plan will contain a root, which has no parents
2. A parent can have one or more children, and its ID will be less than the child(s) ID
3. A child can have only one parent, it is indented to the right; in case of many childs, it will have the same indentation.

The following is a sample execution plan.

SQL> explain plan for
  2  select e.empno, e.ename, d.dname
  3  from emp e, dept d
  4  where e.deptno = d.deptno
  5  and e.deptno = 10;

Explained.

SQL> SELECT * FROM table(dbms_xplan.display(null,null,'basic'));

PLAN_TABLE_OUTPUT
------------------------------------------------
Plan hash value: 568005898

------------------------------------------------
| Id  | Operation                    | Name    |
------------------------------------------------
|   0 | SELECT STATEMENT             |         |
|   1 |  NESTED LOOPS                |         |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |
|   3 |    INDEX UNIQUE SCAN         | PK_DEPT |
|   4 |   TABLE ACCESS FULL          | EMP     |
------------------------------------------------

Using the rules above, you could say;

• Operation 0 is the root of the tree; it has one child, Operation 1
• Operation 1 has two children, which is Operation 2 and 4
• Operation 2 has one child, which is Operation 3

Below is the graphical representation of the execution plan. If you read the tree; In order to perform Operation 1, you need to perform Operation 2 and 4. Operation 2 comes first; In order to perform 2, you need to perform its Child Operation 3. In order to perform Operation 4, you need to perform Operation 2

           Operation 0
        (SELECT STATEMENT)
               |
               |
               |
           Operation 1
          (NESTED LOOPS)
               /\
              /  \
             /    \
            /      \
           /        \
          /          \
         /            \
        /              \
   Operation 2      Operation 4
  (TABLE ACCESS    (TABLE ACCESS FULL)
  BY INDEX ROWID)
       |
       |
       |
   Operation 3
  (INDEX UNIQUE SCAN)

• Operation 3 accesses DEPT table using INDEX UNIQUE SCAN and passes the ROWID to Operation 2
• Operation 2 returns all the rows from DEPT table to Operation 1
• Operation 1 performs Operation 4 for each row returned by Operation 2
• Operation 4 performs a full table scan (TABLE ACCESS FULL) scan and applies the filter E.DEPTNO=10 and returns the rows to Operation 1
• Operation 1 returns the final results to Operation 0