Oracle SQL: the Essential Reference

EXPLAIN PLAN

EXPLAIN PLAN SET STATEMENT_ID = 'text' [INTO [schema.]tablename[@dblink]] FOR SQL_statement

Creates an explanation of the execution plan for a SQL statement.

Keywords

SET STATEMENT_ID

Specifies a text string used to identify the result of this EXPLAIN PLAN statement. The default is NULL.

INTO

Specifies the name and location of the plan table. The default is to use a table named PLAN_TABLE in your current schema.

FOR

Specifies the SQL statement for which the plan is to be generated.

You must have the INSERT privilege on the destination table (specified by INTO) before issuing this command. The destination table is usually called PLAN_TABLE, and can be created by running the script utlxplan.sql. The value specified in the SET clause appears in the STATEMENT_ID column of the destination table.

Example

The following example generates an execution plan for a SQL statement. The output will be placed in the plan_table table in the current schema:

EXPLAIN PLAN
   SET STATEMENT_ID = 'Plan1'
   FOR 
   SELECT ename, sal, comm, loc 
   FROM emp,dept
   WHERE emp.deptno = dept.deptno;

See Chapter 8 for a discussion of the plan table.