The EXPLAIN PLAN Command

Creating the Output Table

• You can 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 may vary depending on your operating system. PLAN_TABLE is the default table into which the EXPLAIN PLAN statement inserts rows describing execution plans.

• You can issue a CREATE TABLE statement to create an output table with any name you choose. In this case, you can issue an EXPLAIN PLAN statement and direct its output to this 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,
    other            LONG);

Output Table Columns

STATEMENT_ID	The value of the option 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. Table A - 1 beginning shows values for this column.
	In the first row generated for a statement, the column contains one of these values, depending on the type of the statement:
	'DELETE STATEMENT' 'INSERT STATEMENT' 'SELECT STATEMENT' 'UPDATE STATEMENT'
OPTIONS	A variation on the operation described in the OPERATION column. Table A - 1 beginning shows values for this column.
OBJECT_NODE	The name of the database link used to reference the object (a table name or view name). For local queries using the parallel query option, this column describes the order in which output from operations is consumed.
OBJECT_OWNER	The name of the user that 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. Note that 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. For a description of how ID and PARENT_ID show the order of processing of the execution plan, see the section "Example of EXPLAIN PLAN Output" .
POSITION	The order of processing for steps that all have the same PARENT_ID.
	In the first row generated for a SQL statement, this column contains the cost of the statement as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is null for the first generated row.
OTHER	Other information that is specific to the execution step that a user may find useful. For example, for distributed queries, OTHER might contain the text of the SQL statement sent to a remote database. For queries performed in parallel, OTHER describes the text of the query that is used by query servers for each operation.

Table A - 1 lists each combination of OPERATION and OPTION values produced by the EXPLAIN PLAN command and its meaning within an execution plan.

OPERATION	OPTION	Description
AND-EQUAL		An operation that accepts multiple sets of ROWIDS and returns the intersection of the sets, eliminating duplicates. This operation is used for the single-column indexes access path.
CONNECT BY		A retrieval of rows in a hierarchical order for a query containing a CONNECT BY clause.
CONCATENATION		An operation that accepts multiple sets of rows and returns the union-all of the sets.
COUNT		An operation that counts the number of rows selected from a table.
	STOPKEY	A count operation where the number of rows returned is limited by the ROWNUM expression in the WHERE clause.
FILTER		An operation that accepts a set of rows, eliminates some of them, and returns the rest.
FIRST ROW		A retrieval on only the first row selected by a query.
FOR UPDATE		An operation that retrieves and locks the rows selected by a query containing a FOR UPDATE clause.
INDEX*	UNIQUE SCAN	A retrieval of a single ROWID from an index.
	RANGE SCAN	A retrieval of one or more ROWIDs from an index. Indexed values are scanned in ascending order.
	RANGE SCAN DESCENDING	A retrieval of one or more ROWIDs from an index. Indexed values are scanned in descending order.
INTERSECTION		An operation that accepts two sets of rows and returns the intersection of the sets, eliminating duplicates.
MERGE JOIN+		An operation that accepts two sets of rows, each sorted by a specific value, combines each row from one set with the matching rows from the other, and returns the result.
	OUTER	A merge join operation to perform an outer join statement.
CONNECT BY		A retrieval of rows in hierarchical order for a query containing a CONNECT BY clause.
MINUS		An operation that accepts two sets of rows and returns rows that appear in the first set but not in the second, eliminating duplicates.
NESTED LOOPS+		An operation that accepts two sets of rows, an outer set and an inner set. Oracle compares each row of the outer set with each row of the inner set and returns those rows that satisfy a condition.
NESTED LOOPS+	OUTER	A nested loops operation to perform an outer join statement.
PROJECTION		An internal operation.
REMOTE		A retrieval of data from a remote database.
SEQUENCE		An operation involving accessing values of a sequence.
SORT	AGGREGATE	A retrieval of a single row that is the result of applying a group function to a group of selected rows.
	UNIQUE	An operation that sorts a set of rows to eliminate duplicates.
	GROUP BY	An operation that sorts a set of rows into groups for a query with a GROUP BY clause.
	JOIN	An operation that sorts a set of rows before a merge-join operation.
	ORDER BY	An operation that sorts a set of rows for a query with an ORDER BY clause.
TABLE ACCESS*	FULL	A retrieval of all rows from a table.
	CLUSTER	A retrieval of rows from a table based on a value of the key of an indexed cluster.
	HASH	A retrieval of rows from a table based on a value of the key of hash cluster.
	BY ROWID	A retrieval of a row from a table based on its ROWID.
UNION		An operation that accepts two sets of rows and returns the union of the sets, eliminating duplicates.
VIEW		An operation that performs a view's query and then returns the resulting rows to another operation.

Table A - 1. OPERATION and OPTION Values Produced by the EXPLAIN PLAN Command.

^* These operations are access methods. ⁺ These operations are join operations.

Both access methods and join operations are discussed in Chapter 5, "The Optimizer".

Example of EXPLAINPLAN Output

This 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 losal AND hisal);

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);

Table Format of EXPLAIN PLAN Output

SELECT operation, options, object_name, id, parent_id, position

    FROM plan_table

    WHERE statement_id = 'Emp_Sal'

    ORDER BY id;

OPERATION         OPTIONS   OBJECT_NAME   ID  PARENT_ID   POSITION
------------------------------------------------------------------
SELECT STATEMENT                           0                     5
FILTER                                     1         0           0
NESTED LOOPS                               2         1           1
TABLE ACCESS      FULL      EMP            3         2           1
TABLE ACCESS      FULL      DEPT           4         2           2
TABLE ACCESS      FULL      SALGRADE       5         1           3

The ORDER BY clause returns the steps of the execution plan sequentially by ID value. However, Oracle does not perform the steps in this order. Since PARENT_ID receives information from ID, observe that more than one ID step fed PARENT_ID. For example, step 2, a merge join, and step 7, a table access, both fed step 1. A nested, visual representation of the processing sequence is shown in the next section.

The value of the POSITION column for the first row of output indicates that the optimizer estimates the cost of executing the statement with this execution plan to be 5.

Nested Format of EXPLAIN PLAN Output

SELECT LPAD(' ',2*(LEVEL-1))||operation||' '||options
   ||' '||object_name
   ||' '||DECODE(id, 0, 'Cost = '||position) "Query Plan"
   FROM plan_table
   START WITH id = 0 AND statement_id = 'Emp_Sal'
   CONNECT BY PRIOR id = parent_id AND statement_id ='Emp_Sal';
 
Query Plan
------------------------------
SELECT STATEMENT   Cost = 5
   FILTER
      NESTED LOOPS
         TABLE ACCESS FULL EMP
         TABLE ACCESS FULL DEPT
      TABLE ACCESS FULL SALGRADE  

Figure A - 1. Tree Structure of an Execution Plan

The tree structure illustrates how operations that occur during the execution of a SQL statement feed one another. Each step in the execution plan is assigned a number (representing the ID column of the PLAN_TABLE) and is depicted by a "node". The result of each node's operation passes to its parent node, which uses it as input.