DBMS_XPLAN Package

The DBMS_XPLAN package displays the execution plan for a statement

The package is defined in $ORACLE_HOME/rdbms/admin/dbmsxpln.sql

The package was introduced in Oracle 9.2 and has been enhanced in subsequent releases.

Subroutines include:

FUNCTION DISPLAY	Display a plan from a PLAN_TABLE
FUNCTION DISPLAY_AWR	Display a plan from the AWR
FUNCTION DISPLAY_CURSOR	Display a plan from the library cache
FUNCTION DISPLAY_PLAN	Return the plan as a CLOB
FUNCTION DISPLAY_SQLSET	Display a plan from a SQL tuning set
FUNCTION DISPLAY_SQL_PLAN_BASELINE	Display a plan from a SQL baseline

For example to display the execution plan for the statement most recently executed by the current session:

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR);

All DISPLAY subroutines include a FORMAT column which can be:

BASIC
- Minimum information
- Display operation ID, name and option
TYPICAL
- Default
- Display operation ID, name and option, #rows, #bytes and optimizer cost
- Includes pruning, parallel and predicate information where applicable
ALL
- Maximum information
- As TYPICAL plus projection, alias and remote SQL if applicable
SERIAL
- Same as TYPICAL except parallel information is not displayed even if the statement executes in parallel

FORMAT column options can be fine-tuned using the following keywords:

If keyword is prefixed by a minus sign, then the specified information is excluded.

Examples:

BASIC ROWS displays basic information plus number of rows
ALL -PROJECTION -NOTE displays everything except the projection and note sections

For DISPLAY_CURSOR and DISPLAY_SQLSET, additional data can be output using the following keywords:

IOSTATS, ALLSTATS and LAST require basic plan statistics which are collected by:

MEMSTATS require PGA memory management to be enabled by: