DBMS_XPLAN, 2 of 3

Summary of DBMS_XPLAN Subprograms

Table 90-1 DBMS_XPLAN Package Subprograms

Subprogram	Description
DISPLAY Function	Displays the contents of the plan table.

DISPLAY Function

This function displays the contents of the plan table.

Syntax

DBMS_XPLAN.DISPLAY(
   table_name    IN  VARCHAR2  DEFAULT 'PLAN_TABLE',
   statement_id  IN  VARCHAR2  DEFAULT  NULL, 
   format        IN  VARCHAR2  DEFAULT  'TYPICAL');

Parameters

Table 90-2 DISPLAY Function Parameters

Parameter Description

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 `EXPLAIN` `PLAN` command.
statement_id	Specifies the `statement_id` of the plan to be displayed. This parameter defaults to `NULL,` which is the default when the `EXPLAIN` `PLAN` command is executed without a `set` `statement_id` clause.
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 the 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.

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 EXPLAIN PLAN command.

statement_id

Specifies the statement_id of the plan to be displayed. This parameter defaults to NULL, which is the default when the EXPLAIN PLAN command is executed without a set statement_id clause.

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

Displaying Results: Examples

To display the result of the last EXPLAIN PLAN 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'));