2011-03-15Getting an Oracle Execution Plan

In the Oracle database, getting an execution plan consists of two steps:

Explaining a Statement

The first step is to explain the statement. Explaining an SQL is as easy as executing it because it needs to be prefixed with “explain plan for” only. The following example creates the execution plan for a very trivial select:

EXPLAIN PLAN FOR select * from dual;

You should be able to execute this statement like any other select using the tool of your choice. The explain command itself will not return the plan, instead it will insert the plan into a table called PLAN_TABLE, if that exists. This is the little gotcha; that table does potentially not exist in your schema. In recent releases you will automatically have the required tables, however, for elder installations you should ask your DBA to provide the following file that includes the required CREATE TABLE statement:

$ORACLE_HOME/rdbms/admin/utlxplan.sql

Once the PLAN_TABLE is available, you should be able to execute the explain statement without error.

Formatting the Execution Plan

The explain plan for statement stores the execution plan in the PLAN_TABLE. You can access that table with an ordinary select, if you want. However, that is not going to provide you the plan as I used it in this book.

Since release 9iR2, a PL/SQL package called DBMS_XPLAN is shipped with the database. That package allows you to retrieve the formatted execution plan with an ordinary select statement and your favorite tool:

select * from table(dbms_xplan.display);

Once again, if that statement doesn’t work out of the box, you should probably ask your DBA for assistance.

The select will return the execution plan for the most recently explained statement:

--------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)|.
--------------------------------------------------------------
|  0 | SELECT STATEMENT  |      |    1 |     2 |     2   (0)|.
|  1 |  TABLE ACCESS FULL| DUAL |    1 |     2 |     2   (0)|.
--------------------------------------------------------------

You probably note that this plan has more columns than the plans shown in the book—I removed the not-so-valuable columns for a better fit on the page.