OracleSource

EXPLAIN PLAN Example

SQL> EXPLAIN PLAN to set statement_id = 'demo_01' for
3 select *
4 from classes
5 where status = 'CONF'
6 and loc_id = 22;

Explained.


Note : The EXPLAIN PLAN command does not actually execute the statement
This command inserts a row into the plan table for each step of the execution plan.

Displaying the Execution Plan

SQL> column "Query Plan" Format a60

SQL> SELECT ID,
2 LPAD (' ', 2 * LEVEL)
3 || operation
4 || DECODE (ID, 0, ' Cost = ' || POSITION)
5 || ' '
6 || options
7 || ' '
8 || object_name AS "Query Plan"
9 FROM plan_table
10 WHERE STATEMENT_ID = 'demo_01'
11 CONNECT BY PRIOR ID = parent_id
12 START WITH ID = 0;


The select statement in the slide is designed to display the steps that would be taken if the SQL statement were executed.
The following plan table columns are used:

STATEMENT_ID The value of the optional STATEMENT_ID parameter specified in the EXPLAIN PLAN statement
OPERATION The name of the operation performed at this step;
-- contains a value in the first row generated for each statement
OPTIONS The options for the operation (For example, this will say “remote” if a remote table is referenced in the SQL statement).
OBJECT_NAME The name of the object
ID The number of this step in the execution plan
PARENT_ID The ID of the step that operates on the
results of this step
POSITION The order in which steps with the same parent are processed

Note: Normally, you put this statement into a SQL script with a variable for the statement ID value. This makes using the explain plan much more feasible.

In that same script, you can also delete (or truncate) all rows from the plan table after you retrieved them.
Alternatively, use SQL*Plus AUTOTRACE to eliminate the need to issue queries against the plan table.

Some other useful plan table columns (not used in the example) are:

TIMESTAMP The date and time when the EXPLAIN PLAN statement was issued.
COST The cost-based optimizer’s estimate of the cost of performing the operation, including the cost of any operations that are required to complete this operation (The value of this column does not have a particular unit of measurement. It is merely a weighted value used to compare costs of execution plans.)
CARDINALITY The cost-based optimizer’s estimate of the number of rows processed
BYTES The cost-based optimizer’s estimate of the number of bytes
OTHER The SQL text for remote cursors and parallel query slaves, as well as parallel query dataflow-operation information
OTHER_TAG The function descriptions of the SQL text in the OTHER column
OPTIMIZER The current mode of the optimizer
OBJECT_OWNER The name of the schema that contains the table or index
OBJECT_TYPE A modifier that describes the object, such as NON-UNIQUE for an index