|Oracle9i SQL Reference
Release 1 (9.0.1)
Part Number A90125-01
DROP SEQUENCE to ROLLBACK, 11 of 20
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
PLAN statement as part of the SQL trace facility.
For information on the execution plan for a cached cursor, query the
V$SQL_PLAN dynamic performance view.
To issue an
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
PLAN statement, you must have the privileges necessary to query the output table.
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
PLAN statement. If you want to keep the rows generated by an
PLAN statement in the output table, you must commit the transaction containing the statement.
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.
Specify the name of the output table, and optionally its schema and database. This table must exist before you use the
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.
"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.
REBUILD statement for which the execution plan is generated.
The following statement determines the execution plan and cost for an
UPDATE statement and inserts rows describing the execution plan into the specified
plan_table table with the
STATEMENT_ID value of 'Raise in Tokyo':
EXPLAIN PLAN SET STATEMENT_ID = 'Raise in Tokyo' INTO plan_table FOR UPDATE employees SET salary = salary * 1.10 WHERE department_id = (SELECT department_id FROM departments WHERE location_id = 1200);
SELECT statement queries the
plan_table table and returns the execution plan and the cost:
SELECT LPAD(' ',2*(LEVEL-1))||operation operation, options, object_name, position FROM plan_table START WITH id = 0 AND statement_id = 'Raise in Tokyo' CONNECT BY PRIOR id = parent_id AND statement_id = 'Raise in Tokyo';
The query returns this execution plan:
OPERATION OPTIONS OBJECT_NAME POSITION ------------------- --------------- ------------------ ---------- UPDATE STATEMENT UPDATE EMPLOYEES INDEX RANGE SCAN EMP_DEPARTMENT_IX TABLE ACCESS BY INDEX ROWID DEPARTMENTS INDEX RANGE SCAN DEPT_LOCATION_IX
The value in the
POSITION column of the first row shows that the statement has a cost of 1.
PLAN: Partitioned Example
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 high values are 1000, 2000, 3000, 4000, 5000, 6000, 7000, and 8000.
Consider the query:
where :h represents a bind variable.
PLAN executes this query with
PLAN_TABLE as the output table. The basic execution plan, including partitioning information, is obtained with the following query:
SELECT id, operation, options, object_name, partition_start, partition_stop, partition_id FROM plan_table;