Skip Headers

Oracle9i Supplied PL/SQL Packages and Types Reference
Release 2 (9.2)

Part Number A96612-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to beginning of chapter Go to next page

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

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'));

Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 2000, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback