The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to execute the statement. An execution plan defines how Oracle finds or writes the data. The components of execution plans include:
- An ordering of the tables referenced by the statement
- An access method for each table mentioned in the statement
- A join method for tables affected by join operations in the statement
The general syntax of EXPLAIN PLAN is: explain plan for your- sql-statement;
EXPLAIN PLAN output shows how Oracle executes SQL statements. 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. EXPLAIN PLAN results alone, however, cannot differentiate between well-tuned statements and those that perform poorly. For example, if EXPLAIN PLAN output shows that a statement uses an index, this does not mean the statement runs efficiently. Sometimes using indexes can be extremely inefficient. It is thus best to use EXPLAIN PLAN to determine an access plan and later prove it is the optimal plan through testing. If you execute an EXPLAIN PLAN, Oracle will analyze the statement and fill a special table with the Execution plan for that statement. Before issuing an EXPLAIN PLAN statement, verify whether PLAN_TABLE exits or create a table to hold its output . Use one of the following approaches:
1)Run the SQL script UTLXPLAN.SQL to create a sample output table called PLAN_TABLE in your schema. The exact name and location of this script depends on your operating system. PLAN_TABLE is the default table into which the EXPLAIN PLAN statement inserts rows describing execution plans.
2)Issue a CREATE TABLE statement to create an output table with any name you choose. When you issue an EXPLAIN PLAN statement you can direct its output to this table.
Any table used to store the output of the EXPLAIN PLAN statement must have the same column names and datatypes as the PLAN_TABLE: CREATE TABLE plan_table (statement_id VARCHAR2(30), timestamp DATE, remarks VARCHAR2(80), operation VARCHAR2(30), options VARCHAR2(30), object_node VARCHAR2(128), object_owner VARCHAR2(30), object_name VARCHAR2(30), object_instance NUMERIC, object_type VARCHAR2(30), optimizer VARCHAR2(255), search_columns NUMERIC, id NUMERIC, parent_id NUMERIC, position NUMERIC, cost NUMERIC, cardinality NUMERIC, bytes NUMERIC, other_tag VARCHAR2(255) other LONG); 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. To examine the execution plan produced by an EXPLAIN PLAN statement, you must have the privileges necessary to query the output table. EXPLAIN PLAN SET STATEMENT_ID (optional) INTO TABLE_NAME FOR YOUR- SQL-STATEMENT; If you omit the INTO TABLE_NAME clause, Oracle fills a table named PLAN_TABLE by default.
Keywords and Parameters :
SET STATEMENT_ID : Specifies 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 : Specifies name of the output table, and optionally its schema and database. This table must exist before you use the EXPLAIN PLAN statement.
FOR statement : Specifies a SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, or CREATE INDEX statement for which the execution plan is generated. Note: 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.
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. The Plan Table: The plan table is the table that Oracle fills when you issue an execution plan for an SQL statement. You must make sure such a plan table exists. Oracle ships with the script UTLXPLAN.SQL which creates this table, named PLAN_TABLE (which is the default name used by EXPLAIN PLAN.You can also choose any other name for the plan table, as long as you have been granted insert on it and its table structure should be similar to Plan Table.
The PLAN_TABLE used by the EXPLAIN PLAN statement contains the following columns: PLAN_TABLE Columns :
STATEMENT_ID : The value of the optional STATEMENT_ID parameter specified in the EXPLAIN PLAN statement.
TIMESTAMP : The date and time when the EXPLAIN PLAN statement was issued.
REMARKS : Any comment (of up to 80 bytes) you wish to associate with each step of the explained plan. If you need to add or change a remark on any row of the PLAN_TABLE, use the UPDATE statement to modify the rows of the PLAN_TABLE.
OPERATION : The name of the internal operation performed in this step. In the first row generated for a statement, the column contains one of the following values: DELETE STATEMENT ,INSERT STATEMENT ,SELECT STATEMENT ,UPDATE STATEMENT .
OPTIONS : A variation on the operation described in the OPERATION column.
OBJECT_NODE : The name of the database link used to reference the object (a table name or view name). For local queries using parallel execution, this column describes the order in which output from operations is consumed.
OBJECT_OWNER : The name of the user who owns the schema containing the table or index.
OBJECT_NAME : The name of the table or index.
OBJECT_INSTANCE : A number corresponding to the ordinal position of the object as it appears in the original statement. The numbering proceeds from left to right, outer to inner with respect to the original statement text. View expansion will result in unpredictable numbers.
OBJECT_TYPE : A modifier that provides descriptive information about the object; for example, NON-UNIQUE for indexes.
OPTIMIZER : The current mode of the optimizer.
SEARCH_COLUMNS : Not currently used.
ID : A number assigned to each step in the execution plan.
PARENT_ID : The ID of the next execution step that operates on the output of the ID step.
POSITION : The order of processing for steps that all have the same PARENT_ID.
OTHER : Other information that is specific to the execution step that a user may find useful.
OTHER_TAG : Describes the contents of the OTHER column
DISTRIBUTION : Stores the method used to distribute rows from "producer" query servers to "consumer" query servers.
Pstart : The start partition of a range of accessed partitions. It can take one of the following values: n indicates that the start partition has been identified by the SQL compiler and its partition number is given by n. KEY indicates that the start partition will be identified at execution time from partitioning key values. ROW LOCATION indicates that the start partition (same as the stop partition) will be computed at execution time from the location of each record being retrieved. The record location is obtained by a user or from a global index. INVALID indicates that the range of accessed partitions is empty.
Pstop : The stop partition of a range of accessed partitions. It can take one of the following values: n indicates that the stop partition has been identified by the SQL compiler and its partition number is given by n. KEY indicates that the stop partition will be identified at execution time from partitioning key values. ROW LOCATION indicates that the stop partition (same as the start partition) will be computed at execution time from the location of each record being retrieved. The record location is obtained by a user or from a global index. INVALID indicates that the range of accessed partitions is empty.
PID : The step that has computed the pair of values of the Pstart and Pstop columns.
COST : The cost of the operation as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is null. Cost is not determined for table access operations. The value of this column does not have any particular unit of measurement, it is merely a weighted value used to compare costs of execution plans.
CARDINALITY : The estimate by the cost-based approach of the number of rows accessed by the operation.
BYTES : The estimate by the cost-based approach of the number of bytes accessed by the operation.
Examples
Using the EXPLAIN PLAN Statement:
The following example shows a SQL statement and its corresponding execution plan generated by EXPLAIN PLAN. The sample query retrieves names and related information for employees whose salary is not within any range of the SALGRADE table:
SELECT ename, job, sal, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND NOT EXISTS (SELECT * FROM salgrade WHERE emp.sal BETWEEN lowsal AND highsal);
This EXPLAIN PLAN statement generates an execution plan and places the output in PLAN_TABLE:
EXPLAIN PLAN SET STATEMENT_ID = 'Emp_Sal' FOR SELECT ename, job, sal, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND NOT EXISTS (SELECT * FROM salgrade WHERE emp.sal BETWEEN losal AND hisal);
Selecting PLAN_TABLE Output in Table Format
SELECT operation, options, object_name, id, parent_id, position, cost, cardinality, other_tag, optimizer FROM plan_table WHERE statement_id = 'Emp_Sal' ORDER BY id;
OPERATION OPTIONS OBJECT_NAME ID PARENT_ID POSITION COST CARDINALITY BYTES OTHER_TAG OPTIMIZER
----------------------------------------------------------------------------------------------- SELECT STATEMENT 0 2 2 1 62
TABLE ACCESS FULL EMP 3 2 1 1 1 40
ANALYZED TABLE ACCESS FULL DEPT 4 2 2 4 88 ANALYZED TABLE ACCESS FULL SALGRADE 5 1 2 1 1 13 ANALYZED
