Understanding execution plan and cost in Oracle

Hello everyone!

In Oracle an execution plan is created internally every time we fire an SQL query on the database. The plan thus created depends upon the type of query optimizer used in your Oracle database. For example, Oracle 10g by default uses the CBO (cost based optimizer) that generated the plan by computing the table statistics. On the other hand we have the RBO (rule based optimizer) that uses a set of pre-defined rules to create the plan.

How do I get the execution plan?

In Oracle it is possible to generate the execution plan for any SQL query. Using *SQL Plus, one can run the set autotrace command and then execute your SQL statement to see the execution plan as follows!

SQL> set autotrace traceonly explain;

SQL> select EmpName from Employee where EmpId=103;

Execution Plan
----------------------------------------------------------
Plan hash value: 3075387830

--------------------------------------------------------------------------------

------------

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)

| Time     |

--------------------------------------------------------------------------------

------------

|   0 | SELECT STATEMENT            |              |     1 |     9 |     1   (0)

| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEE     |     1 |     9 |     1   (0)

| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | SYS_C0013049 |     1 |       |     0   (0)

| 00:00:01 |

--------------------------------------------------------------------------------

------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPID"=103)

As seen above the execution plan is obtained for the query. Another way to obtain the execution plan is by using the EXPLAIN PLAN statement as follows!

SQL> analyze table Employee compute statistics;

Table analyzed.

SQL> explain plan for select EmpName from Employee where EmpId=103;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 3075387830

--------------------------------------------------------------------------------

------------

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)

| Time     |

--------------------------------------------------------------------------------

------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |              |     1 |     9 |     1   (0)

| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEE     |     1 |     9 |     1   (0)

| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | SYS_C0013049 |     1 |       |     0   (0)

| 00:00:01 |

--------------------------------------------------------------------------------

------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------



Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPID"=103)

14 rows selected.

How do I determine the execution cost?

If you observe closely, the plan generated includes a COST column. The COST column cannot be used to compare execution plans. Many people have the misconception that if a plan has a higher cost than another plan, it is worse. This isn’t true at all. This has been explained in detail over here.

The COST column is comparable provided you are evaluating two plans for the same SQL at the same time on the same database with the same parameter settings. It is possible to even write your own SQL script to generate execution plans. This post by Jonathan Lewis gives you a head start.

SQL> select cost from plan_table;

Execution Plan
----------------------------------------------------------
Plan hash value: 103984305

--------------------------------------------------------------------------------

-

| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time
|

--------------------------------------------------------------------------------

-

|   0 | SELECT STATEMENT  |             |     3 |    39 |     2   (0)| 00:00:01
|

|   1 |  TABLE ACCESS FULL| PLAN_TABLE$ |     3 |    39 |     2   (0)| 00:00:01
|

--------------------------------------------------------------------------------

-

Note
-----
   - dynamic sampling used for this statement (level=2)

Hope this Oracle database tip helps you in some way! Stay tuned for more! :)

Karan Balkar About Karan Balkar
self proclaimed extraterrestrial fandroid, computer engineer, amateur gamer and die hard punk rock fan!

Leave a Reply