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 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:
EXPLAIN PLAN SET STATEMENT_ID = YOUR USERID FOR
Now run the SQL. Note that it will not execute the SQL itself, it will only generate the plan. The only output of running the SQL is
If you get an error telling you that PLAN_TABLE does not exist, see your DBA; you dont have the privileges to run Explain Plan.
The plan will be displayed on the screen. Depending on the width of your screen, the output may wrap over 2 or more lines. If so, you will need to add COLUMN commands to rplan.sql to format the output.
An alternative for Unix users with telnet sessions is plan.sql. plan.sql assumes that you have your SQL saved as a file in the current directory. From the SQL*Plus prompt, type:
plan.sql will pick up your file, run it through Explain Plan, and display the plan to the screen.
The is the easy low-tech method. In SQL*Plus, type:
SET AUTOTRACE ON EXPLAIN
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.