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:

• Oracle Enterprise Manager - SQL Scratch Pad
• TOAD
• SQL*Plus - EXPLAIN
• SQL*Plus - AUTOTRACE

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

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.

SQL*Plus EXPLAIN

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

• Add the following line BEFORE your SQL statement

EXPLAIN PLAN SET STATEMENT_ID = ‘YOUR USERID’ FOR

Eg.

EXPLAIN PLAN SET STATEMENT_ID = ‘JOEBLOGGS’ FOR
SELECT *
FROM EMP;

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

Explained.

If you get an error telling you that ‘PLAN_TABLE does not exist’, see your DBA; you don’t have the privileges to run Explain Plan.

• Now you have to retrieve the plan. This involves running a lengthy SQL (see Attachment 1). Download the atached rplan.sql and run:

@rplan

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 filename

plan.sql will pick up your file, run it through Explain Plan, and display the plan to the screen.

SQL*Plus AUTOTRACE

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.