Oracle EXPLAIN PLAN Utility

Generating Execution Plans

The EXPLAIN PLAN statement enables you to examine the execution plan that the optimizer chose for a SQL statement. When the statement is issued, the optimizer chooses an execution plan and then inserts data describing the plan into a database table. Issue the EXPLAIN PLAN statement and then query the output table.

Executing EXPLAIN PLAN for a Single Statement

The basics of using the EXPLAIN PLAN statement are as follows:

Use the SQL script CATPLAN.SQL to create a sample output table called PLAN_TABLE in your schema.
Include the EXPLAIN PLAN FOR clause before the SQL statement.
After issuing the EXPLAIN PLAN statement, use a script or package provided by Oracle Database to display the most recent plan table output.
The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right. The next step is the parent of that line. If two lines are indented equally, then the top line is normally executed first.

To explain a SQL statement, use the EXPLAIN PLAN FOR clause immediately before the statement. For example:

EXPLAIN PLAN FOR
SELECT last_name FROM employees;

1) The first section deletes all rows from the plan table

set echo off
set verify off 
delete from plan_table;
commit

2) The main part of the SQL populates the plan table with the access path information.

set echo off
select
lpad(' ', 2*(level-1)) ||
operation ||'  '||
decode(id, 0, 'Cost = '||position) "OPERATION",
options, object_name
from plan_table
START WITH id=0
connect by prior id= parent_id;

The final step displays the access information from the plan table.

4) The query produces the following output. Here we see the actual access path to the data. We will bring re-visiting this topic in detail in a later lesson to expand your understanding of outputs like the one above.