EXPLAIN PLAN

Purpose

Use the EXPLAIN PLAN statement to determine the execution plan Oracle follows to execute a specified SQL statement. This statement inserts a row describing each step of the execution plan into a specified table. If you are using cost-based optimization, this statement also determines the cost of executing the statement. If any domain indexes are defined on the table, user-defined CPU and I/O costs will also be inserted.

The definition of a sample output table PLAN_TABLE is available in a SQL script on your distribution media. Your output table must have the same column names and datatypes as this table. The common name of this script is UTLXPLAN.SQL. The exact name and location depend on your operating system.

You can also issue the EXPLAIN PLAN statement as part of the SQL trace facility.

See Also: Oracle8i Performance Guide and Reference for information on the output of EXPLAIN PLAN Oracle8i Performance Guide and Reference for information on how to use the SQL trace facility, as well as a detailed discussion of how to generate and interpret execution plans

Prerequisites

To issue an EXPLAIN PLAN statement, you must have the privileges necessary to insert rows into an existing output table that you specify to hold the execution plan.

You must also have the privileges necessary to execute the SQL statement for which you are determining the execution plan. If the SQL statement accesses a view, you must have privileges to access any tables and views on which the view is based. If the view is based on another view that is based on a table, you must have privileges to access both the other view and its underlying table.

To examine the execution plan produced by an EXPLAIN PLAN statement, you must have the privileges necessary to query the output table.

The EXPLAIN PLAN statement is a data manipulation language (DML) statement, rather than a data definition language (DDL) statement. Therefore, Oracle does not implicitly commit the changes made by an EXPLAIN PLAN statement. If you want to keep the rows generated by an EXPLAIN PLAN statement in the output table, you must commit the transaction containing the statement.

See Also: INSERT and SELECT and subquery information on the privileges you need to populate and query the plan table

Syntax

Keywords and Parameters

SET STATEMENT_ID = 'text'

Specify the value of the STATEMENT_ID column for the rows of the execution plan in the output table. You can then use this value to identify these rows among others in the output table. Be sure to specify a STATEMENT_ID value if your output table contains rows from many execution plans. If you omit this clause, the STATEMENT_ID value defaults to null.

INTO table

Specify the name of the output table, and optionally its schema and database. This table must exist before you use the EXPLAIN PLAN statement.

If you omit schema, Oracle assumes the table is in your own schema.

The dblink can be a complete or partial name of a database link to a remote Oracle database where the output table is located. You can specify a remote output table only if you are using Oracle's distributed functionality. If you omit dblink, Oracle assumes the table is on your local database.

See Also: "Referring to Objects in Remote Databases" for information on referring to database links

If you omit INTO altogether, Oracle assumes an output table named PLAN_TABLE in your own schema on your local database.

FOR statement

Specify a SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, CREATE INDEX, or ALTER INDEX ... REBUILD statement for which the execution plan is generated.

Notes: If statement includes the parallel_clause, the resulting execution plan will indicate parallel execution. However, EXPLAIN PLAN actually inserts the statement into the plan table, so that the parallel DML statement you submit is no longer the first DML statement in the transaction. This violates the Oracle restriction of one parallel DML statement per transaction, and the statement will be executed serially. To maintain parallel execution of the statements, you must commit or roll back the EXPLAIN PLAN statement, and then submit the parallel DML statement. To determine the execution plan for an operation on a temporary table, EXPLAIN PLAN must be run from the same session, because the data in temporary tables is session specific.

Examples

EXPLAIN PLAN Examples

The following statement determines the execution plan and cost for an UPDATE statement and inserts rows describing the execution plan into the specified output table with the STATEMENT_ID value of 'Raise in Chicago':

EXPLAIN PLAN 
    SET STATEMENT_ID = 'Raise in Chicago' 
    INTO output 
    FOR UPDATE emp 
        SET sal = sal * 1.10 
        WHERE deptno =  (SELECT deptno 
                  FROM dept
                  WHERE loc = 'CHICAGO'); 

The following SELECT statement queries the output table and returns the execution plan and the cost:

SELECT LPAD(' ',2*(LEVEL-1))||operation operation, options, 
object_name, position 
    FROM output 
    START WITH id = 0 AND statement_id = 'Raise in Chicago'
    CONNECT BY PRIOR id = parent_id AND 
    statement_id = 'Raise in Chicago'; 

The query returns this execution plan:

OPERATION             OPTIONS     OBJECT_NAME     POSITION
----------------------------------------------------------
UPDATE STATEMENT                                         1
    FILTER                                               0
      TABLE ACCESS    FULL        EMP                    1
      TABLE ACCESS    FULL        DEPT                   2

The value in the POSITION column of the first row shows that the statement has a cost of 1.

EXPLAIN PLAN: Partitioned Example

Assume that stocks is a table with eight partitions on a stock_num column, and that a local prefixed index stock_ix on column stock_num exists. The partition HIGHVALUES are 1000, 2000, 3000, 4000, 5000, 6000, 7000, and 8000.

Consider the query:

SELECT * FROM stocks WHERE stock_num BETWEEN 3800 AND :h;

(where :h represents a bind variable). EXPLAIN PLAN executes this query with PLAN_TABLE as the output table. The basic execution plan, including partitioning information, is obtained with the query:

SELECT id, operation, options, object_name,
  partition_start, partition_stop, partition_id FROM plan_table;