Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
SQL Statements (continued), 30 of 30
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. See Oracle8i Designing and Tuning for Performance for information on the output of EXPLAIN
PLAN
.
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. For information on how to use the SQL trace facility, as well as a detailed discussion of how to generate and interpret execution plans, see Oracle8i Designing and Tuning for Performance.
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 "INSERT".
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.
|
specifies the value of the |
|
specifies 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. For information on referring to database links, see the section, "Referring to Objects in Remote Databases". 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. |
|
If you omit |
|
specifies a |
|
Note: If statement includes the parallel_clause, the resulting execution plan will indicate parallel execution. However, |
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.
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;
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|