Oracle SQL*Plus: The Definitive Guide

Using EXPLAIN PLAN

EXPLAIN PLAN is a SQL statement that causes Oracle to report the execution plan it would choose for any SELECT, INSERT, UPDATE, or DELETE statement. An execution plan refers to the approach Oracle will take to retrieve the necessary data for a statement. One example of a plan would be to use an index to find the required rows. Another example of an execution plan would be to sequentially read all rows in the table. If you have a poorly-performing SQL statement, you can use EXPLAIN PLAN to find out how Oracle is processing it. With that information, you may be able to take some corrective action to improve performance.

When you use EXPLAIN PLAN, Oracle doesn’t display its execution strategy on the screen; instead, it inserts rows into a table. This table is referred to as the plan table , and you must query it properly in order to see the results. Of course, the plan table must exist, so if you’ve never used EXPLAIN PLAN before, you may need to create the plan table first.

Tip

Oracle occasionally adds columns to the plan table. If you have a plan table created using a previous version of Oracle, you may want to drop and recreate it, just to be sure you have the most up-to-date version.

Creating the Plan Table

Oracle provides a script to create the plan table. It is named UTLXPLAN.SQL , and it resides in the RDBMS/ADMIN directory for your database. Under Windows 95, for example, the script to create the plan table for Oracle8 will be C:\ORAWIN95\RDBMS80\ADMIN\UTLXPLAN.SQL ...