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;


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

Plan hash value: 568005898

| Id  | Operation                    | Name    |
|   0 | SELECT STATEMENT             |         |
|   1 |  NESTED LOOPS                |         |
|   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
           Operation 1
          (NESTED LOOPS)
              /  \
             /    \
            /      \
           /        \
          /          \
         /            \
        /              \
   Operation 2      Operation 4
   Operation 3
  • 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

3 thoughts on “How to read an Oracle SQL Execution Plan?”

  1. With this explanation, can I say that is better if the TABLE ACCESS FULL , is on of the last operations? If it came first or in the middle of the plan, it will cost more?

    1. You can not generalize. Cost in general depends on your SQL (for example, joins, filters, sort you want to perform before returning the rows), supporting index access structures, data volume, statistics (that is, information about the data itself) available to optimizer and etc…

Leave a Comment

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.