by Markus Winand.

Getting an Execution Plan


Viewing an execution plan in the Oracle database involves two steps:

  1. explain plan for — saves the execution plan in the PLAN_TABLE.

  2. Format and display the execution plan.

Creating and Saving an Execution Plan

To create an execution plan, you just have to prefix the respective SQL statement with explain plan for:

EXPLAIN PLAN FOR select * from dual

You can execute the explain plan for command in any development environment or SQL*Plus. It will, however, not show the plan but save it into a table named PLAN_TABLE. Starting with release 10g, this table is automatically available as a global temporary table. With previous releases, you have to create it in each schema as needed. Ask your database administrator to create it for you or to provide the create table statement from the Oracle database installation:

$ORACLE_HOME/rdbms/admin/utlxplan.sql

You can execute this statement in any schema you like to create the PLAN_TABLE in this schema.

Warning

The explain plan for command does not necessarily create the same execution plan as though it would when executing the statement.

Support My Work

I offer SQL training, tuning and consulting. Buying my book “SQL Performance Explained” (from €9.95) also supports my work on this website.

Showing Execution Plans

The package DBMS_XPLAN was introduced with release 9iR2 and can format and display execution plans from the PLAN_TABLE. The following example shows how to display the last execution plan that was explained in the current database session:

select * from table(dbms_xplan.display)

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

The query will display the execution plan as shown in the book:

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

Some of the columns shown in this execution plan were removed in the book for a better fit on the page.

Previous pageNext page

You can’t learn everything in one day. Subscribe the newsletter via E-Mail, Twitter or RSS to gradually catch up. Have a look at modern-⁠sql.com as well.

About the Author

Photo of Markus Winand

Markus Winand provides insights into SQL and shows how different systems support it at modern-sql.com. Previously he made use-the-index-luke.com, which is still actively maintained. Markus can be hired as trainer, speaker and consultant via winand.at.

Buy the Book

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy now!
(paperback and/or PDF)

Paperback also available at Amazon.com.

Hire Markus

Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR | CC-BY-NC-ND 3.0 license