Explain Plan

Explain Plan is the name Oracle (and most other RDBMSs) gives to a tool that describes the algorithm the database will use to run a SQL statement. With Oracle, there are a number of ways of running Explain Plan. Some of these are:

See a sample Explain Plan

Oracle Enterprise Manager

Oracle enterprise manager provides a facility called SQL Scratch Pad. Scratch Pad has a series of buttons down the left hand side to perform functions such as Execute SQL, Save SQL, and – of course – Explain Plan. Type (or paste) your SQL statement into Scratch Pad and hit the Explain Plan button.


TOAD is great. If your site uses it, count your blessings. The "Ambulance" button in TOAD runs Explain Plan. Just position your cursor on the SQL you want to explain, and hit the ambulance button.


This is the low-tech method for those with no access to a nice GUI tool. This is a two step process:


The is the easy low-tech method. In SQL*Plus, type:

Now, every SQL you run will generate and display a plan. NOTE however that using this method, the SQL actually executes, whereas with the previous method it did not. If your SQL takes ages to run, then you will have to wait for it to finish for the plan to display. For this reason, the previous low-tech method is relatively superior.

©Copyright 2003