Oracle7 Server SQL Reference

EXPLAIN PLAN

Purpose

To determine the execution plan Oracle7 follows to execute a specified SQL statement. This command inserts a row describing each step of the execution plan into a specified table. If you are using cost-based optimization, this command also determines the cost of executing the statement.

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. For information on these privileges, see the INSERT command

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. For more information on these privileges, see the SELECT command .

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the output table's creation label or you must satisfy one of the following criteria:

If the output table's creation label is higher than your DBMS label, you must have READUP and WRITEUP system privileges.

If the output table's creation label and your DBMS label are not comparable, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

Syntax

Keywords and Parameters

SET

specifies the value of the STATEMENT_ID column for the rows of the execution plan in the output table. If you omit this clause, the STATEMENT_ID value defaults to null.

INTO

specifies the schema, name, and database containing the output table. This table must exist before you use the EXPLAIN PLAN command. If you omit schema, Oracle7 assumes the table is in your own schema.

The dblink can be a complete or partial name of a database link to a remote Oracle7 database where the output table is located. For information on referring to database links, see the section, "Referring to Objects in Remote Objects," . You can only specify a remote output table if you are using Oracle7 with the distributed option. If you omit dblink, Oracle7 assumes the table is on your local database.

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

FOR

specifies a SELECT, INSERT, UPDATE, or DELETE statement for which the execution plan is generated.

Usage Notes

The definition of a sample output table PLAN_TABLE is available in 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 , although the exact name and location may vary depending on your operating system.

The value you specify in the SET clause appears in the STATEMENT_ID column in the rows of the execution plan. 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.

Since the EXPLAIN PLAN command is a Data Manipulation Language command, rather than a Data Definition Language command, Oracle7 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.

You should not use the EXPLAIN PLAN command to determine the execution plans of SQL statements that access data dictionary views or dynamic performance tables.

You can also issue the EXPLAIN PLAN command as part of the SQL trace facility. For information on how to use the SQL trace facility and how to interpret execution plans, see Appendix A "Performance Diagnostic Tools" of Oracle7 Server Tuning.

Example

This EXPLAIN PLAN 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')

This 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