Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02 |
|
|
View PDF |
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. You can also issue the EXPLAIN
PLAN
statement as part of the SQL trace facility.
If you are using cost-based optimization, then this statement also determines the cost of executing the statement. If any domain indexes are defined on the table, then 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.
See Also:
|
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, then 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, then 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, then you must commit the transaction containing the statement.
See Also:
INSERT and SELECT for information on the privileges you need to populate and query the plan table |
explain_plan::=
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, then 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 EXPLAIN
PLAN
statement.
If you omit schema
, then 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
, then 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, then Oracle assumes an output table named PLAN_TABLE
in your own schema on your local database.
Specify a SELECT
, INSERT
, UPDATE
, DELETE
, CREATE
TABLE
, CREATE
INDEX
, or ALTER
INDEX
... REBUILD
statement for which the execution plan is generated.
statement
includes the parallel_clause
, then 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 in a single 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.EXPLAIN
PLAN
must be run from the same session, because the data in temporary tables is session specific.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);
The following 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 2 UPDATE EMPLOYEES 1 TABLE ACCESS FULL EMPLOYEES 1 VIEW index$_join$_00 1 2 HASH JOIN 1 INDEX RANGE SCAN DEPT_LOCATION_I 1 X INDEX FAST FULL SCAN DEPT_ID_PK 2
The value in the POSITION
column of the first row shows that the statement has a cost of 1.
The sample table sh.sales
is partitioned on the time_id
column. Partition sales_q3_2000
contains time values less than Oct. 1, 2000, and there is a local index sales_time_bix
on the time_id
column.
Consider the query:
EXPLAIN PLAN FOR SELECT * FROM sales WHERE time_id BETWEEN :h AND '01-OCT-2000';
where :h
represents an already declared 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 following query:
SELECT operation, options, partition_start, partition_stop, partition_id FROM plan_table;