Example 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 executeion 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

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 display the plan table output for serial processing.
- UTLXPLP.SQL
This script displays the plan table output including parallel execution columns.
- DBMS_XPLAN.DISPLAY procedure
Some examples > SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());



Example =>

SQL> EXPLAIN PLAN FOR SELECT * FROM EMP WHERE ID=1;

Expalined.

SQL> SLECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3604348766

-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 186 | 3 (0)| 00:00:01 | | |
| 1 | PARTITION HASH SINGLE | | 1 | 186 | 3 (0)| 00:00:01 | 4 | 4 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| EMP | 1 | 186 | 3 (0)| 00:00:01 | 4 | 4 |
|* 3 | INDEX RANGE SCAN | EMP_PK | 1 | | 2 (0)| 00:00:01 | 4 | 4 |
-------------------------------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("ID"=1)

15 rows selected.