Using Explain Plan

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 run the statement.

The row source tree is the core of the execution plan. It shows the following information:

• 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
• Data operations like filter, sort, or aggregation

In addition to the row source tree, the plan table contains information about the following:

• Optimization, such as the cost and cardinality of each operation
• Partitioning, such as the set of accessed partitions
• Parallel execution, such as the distribution method of join inputs

Examining an explain plan lets you look for throw-away in cases such as the following:

• Full scans
• Unselective range scans
• Late predicate filters
• Wrong join order
• Late filter operations

The PLAN_TABLE is automatically created as a global temporary table to hold the output of an EXPLAIN PLAN statement for all users. PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans

Creating a PLAN_TABLE
CONNECT HR/your_password
@$ORACLE_HOME/rdbms/admin/utlxplan.sql

Table created.

Running EXPLAIN PLAN
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;

Displaying PLAN_TABLE Output

UTLXPLS.SQL
This script displays the plan table output for serial processing.
EXPLAIN PLAN Output

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 189 | 10 (10)|
| 1 | NESTED LOOPS | | 3 | 189 | 10 (10)|
| 2 | NESTED LOOPS | | 3 | 141 | 7 (15)|
|* 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 60 | 4 (25)|
| 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (50)|
|* 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | |
| 6 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 432 | 2 (50)|
|* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."EMPLOYEE_ID"<103)
5 - access("E"."JOB_ID"="J"."JOB_ID")
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")


UTLXPLP.SQL
This script displays the plan table output including parallel execution columns.

DBMS_XPLAN.DISPLAY procedure
This procedure accepts options for displaying the plan table output. You can specify:
A plan table name if you are using a table different than PLAN_TABLE
A statement Id if you have set a statement Id with the EXPLAIN PLAN
A format option that determines the level of detail: BASIC, SERIAL, and TYPICAL, ALL,

Some examples of the use of DBMS_XPLAN to display PLAN_TABLE output are:

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'st1','TYPICAL'));