The DBMS_XPLAN package provides an easy way to display the output of the EXPLAIN PLAN command in several, predefined formats. You can also use the DBMS_XPLAN package to display the plan of a statement stored in the Automatic Workload Repository (AWR).It further provides a way to display the SQL execution plan and SQL execution runtime statistics for cached SQL cursors based on the information stored in the V$SQL_PLAN and V$SQL_PLAN_STATISTICS_ALL fixed views.

Using DBMS_XPLAN

Overview

DISPLAY_CURSOR, to format and display the contents of the execution plan of any loaded cursor.

DISPLAY_AWR to format and display the contents of the execution plan of a stored SQL statement in the AWR.

Security Model

This package runs with the privileges of the calling user, not the package owner (SYS). The table function DISPLAY_CURSOR requires to have select privileges on the following fixed views: V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL.

Using the DISPLAY_AWR function requires to have SELECT privileges on DBA_HIST_SQL_PLAN, DBA_HIST_SQLTEXT, and V$DATABASE.

All these privileges are automatically granted as part of the SELECT_CATALOG role.

You can also use the table function DISPLAY_CURSOR to display the execution plan for any loaded cursor stored in the cursor cache. In that case, you must supply a reference to the child cursor to the table function. This includes the SQL ID of the statement and optionally the child number.

Displaying a Plan Table with Parallel Information

By default, only relevant information is reported by the display and display_cursor table functions. In Displaying a Plan Table Using DBMS_XPLAN.DISPLAY, the query does not execute in parallel. Hence, information related to the parallelization of the plan is not reported. As shown in the following example, parallel information is reported only if the query executes in parallel.

By default, if several plans in the plan table match the statement_id parameter passed to the display table function (default value is NULL), only the plan corresponding to the last EXPLAINPLAN command is displayed. Hence, there is no need to purge the plan table after each EXPLAINPLAN. However, you should purge the plan table regularly (for example, by using the TRUNCATETABLE command) to ensure good performance in the execution of the DISPLAY table function.

For ease of use, you can define a view on top of the display table function and then use that view to display the output of the EXPLAINPLAN command:

DISPLAY_AWR Function

This table function displays the contents of an execution plan stored in the AWR.

Syntax

DBMS_XPLAN.DISPLAY_AWR(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER DEFAULT NULL,
db_id IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT TYPICAL);

Parameters

Table 120-2 DISPLAY_AWR Table Function Parameters

Parameter

Description

sql_id

Specifies the SQL_ID of the SQL statement. You can retrieve the appropriate value for the SQL statement of interest by querying the column SQL_ID in DBA_HIST_SQLTEXT.

plan_hash_value

Specifies the PLAN_HASH_VALUE of a SQL statement. This parameter is optional. If omitted, the table function will return all stored execution plans for a given SQL_ID.

db_id

Specifies the database_id for which the plan of the SQL statement, identified by SQL_ID should be displayed. If not supplied, the database_id of the local database will be used, as shown in V$DATABASE.

format

Controls the level of details for the plan. It has the same set of values than the table function DISPLAY, that is, BASIC, TYPICAL, SERIAL and ALL.

Usage Notes

To use the DISPLAY_AWR functionality, the calling user must have SELECT privilege on DBA_HIST_SQL_PLAN. DBA_HIST_SQLTEXT, and V$DATABASE, otherwise it will show an appropriate error message.

Examples

To display the execution plan of all children associated to the SQL ID 'atfwcg8anrykp':

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('atfwcg8anrykp'));

To display the execution plan of all stored SQL statements containing the string 'TOTO':

Syntax

Parameters

Table 120-3 DISPLAY Table Function Parameters

Parameter

Description

table_name

Specifies the table name where the plan is stored. This parameter defaults to PLAN_TABLE, which is the default plan table for the EXPLAINPLAN command. If NULL is specified it also defaults to PLAN_TABLE.

statement_id

Specifies the statement_id of the plan to be displayed. This parameter defaults to NULL, which is the default when the EXPLAINPLAN command is executed without a setstatement_id clause.If no statement_id is specified, the function will show you the plan of the most recent explained statement.

format

Controls the level of details for the plan. It accepts four values:

BASIC: Displays the minimum information in the plan--the operation ID, the object name, and the operation option.

TYPICAL: This is the default. Displays the most relevant information in the plan. Partition pruning, parallelism, and predicates are displayed only when available.

ALL: Maximum level. Includes information displayed with the TYPICAL level and adds projection information as well as SQL statements generated for parallel execution servers (only if parallel).

SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel.

Examples

To display the result of the last EXPLAINPLAN command stored in the plan table:

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

To display from other than the default plan table, "my_plan_table":

SELECT * FROM table(DBMS_XPLAN.DISPLAY('my_plan_table'));

To display the minimum plan information:

SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', null, 'basic'));

To display the plan for a statement identified by 'foo', such as statement_id='foo':

SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'foo'));

DISPLAY_CURSOR Function

This table function displays the explain plan of any cursor loaded in the cursor cache.

Syntax

Parameters

Table 120-4 DISPLAY_CURSOR Function Parameters

Parameter

Description

sql_id

Specifies the SQL_ID of the SQL statement in the cursor cache. You can retrieve the appropriate value by querying the column SQL_ID in V$SQL or V$SQLAREA. Alternatively, you could choose the column PREV_SQL_ID for a specific session out of V$SESSION. This parameter defaults to NULL in which case the plan of the last cursor executed by the session will be displayed.

child_number

Child number of the cursor to display. If not supplied, the execution plan of all cursors matching the supplied sql_id parameter are displayed. The child_number can be specified only if sql_id is specified.

format

Controls the level of details for the plan. It has the same set of values than the table function 'DISPLAY', that is, 'BASIC', 'TYPICAL', 'SERIAL' and 'ALL'. Two additional values are also supported to display run-time statistics for the cursor:

RUNSTATS_LAST: Displays the runtime statistics for the last execution of the cursor.

RUNSTATS_TOT: Displays the total aggregated runtime statistics for all executions of a specific SQL statement since the statement was first parsed and executed.

Format options 'RUNSTATS_LAST' and 'RUNSTATS_TOT' can only be used if the target cursor was compiled and executed with the initialization parameter 'statistics_level' set to 'ALL'.

Usage Notes:

To use the DISPLAY_CURSOR functionality, the calling user must have SELECT privilege on the fixed views V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN, otherwise it will show an appropriate error message.

Examples

To display the execution plan of the last SQL statement executed by the current session:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);

To display the execution plan of all children associated to the SQL ID 'atfwcg8anrykp':

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp'));

To display runtime statistics for the cursor included in the preceding statement: