Oracle SQL*Plus: The Definitive Guide, 2nd Edition

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, DELETE, or MERGE statement. An execution plans 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 to see the results. The plan table must exist; 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 re-create it to be sure you have the most recent version.

Creating the Plan Table

If you're running Oracle Database 10g, the good news is you don't need to create a plan table. Instead, you can let the EXPLAIN PLAN statement create the table for you. If no plan table exists, EXPLAIN PLAN will create a global temporary plan table. The EXPLAIN PLAN results in such a ...