Getting an Oracle DB Explain Plan

An explain plan can tell you how efficiently a SQL statement is retreiving data from the database. In particular it can highlight if changes to the index or the query will help. Today I wanted to see how I could get an explain plan from an Oracle DB so I could improve query performance. At a simple level you need to predicate your SQL statement with the words explain plan for and then your SQL statement. See below:

explain plan for your SQL statement

The above statement will put the explain plan into a table called plan_table

This table is cumulative and each individual plan is identified by the timestamp column. So, if you have the luxury you can empty the table before you generate the explain plan.

Then thanks to Renew Nyffenegger you can use this SQL to get a simple view of the explain plan.

select
      substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation",
      object_name "Object"
from
      plan_table
start with id = 0
connect by prior id=parent_id;

Below is an example output from a simple select * from, as you expect it does a full table scan.

There are other columns held in the explain plan table so have a look at those as well.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s