Results for DBMS_XPLAN


1 to 14 of 14 results.


Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_XPLAN, 1 of 3

90 DBMS_XPLANThe DBMS_XPLAN package provides an easy way to format the output of the EXPLAIN PLAN command. For … ). This chapter discusses the following topics: Using DBMS_XPLAN Summary of DBMS_XPLAN Subprograms Usage Notes

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_XPLAN, 1 of 3

Using DBMS_XPLANThe DBMS_XPLAN package supplies a table function, DISPLAY, to format and display the contents of a plan table, as shown in the following example.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_XPLAN, 2 of 3

Summary of DBMS_XPLAN SubprogramsTable 90-1 DBMS_XPLAN Package Subprograms Subprogram Description DISPLAY Function Displays the contents of the plan table.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_XPLAN, 2 of 3

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

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_XPLAN, 2 of 3

ParametersTable 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

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_XPLAN, 3 of 3

Using a View to Display Output: Example# define plan view create view plan as select * from table(dbms_xplan.display); # display the output of the last explain plan command select * from plan; Copyright © 2000, 2002 Oracle Corporation. All Rights Reserved. Home Book List Contents Index Master Index Feedback

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_XPLAN, 3 of 3

Usage NotesBy default, only relevant information is reported by the display table function. In \"Displaying a Plan Table Using DBMS_XPLAN.DISPLAY: Example\", 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.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_XPLAN, 2 of 3

Displaying Results: ExamplesTo 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', , 'basic')); To display the plan for a statement identified

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_XPLAN, 3 of 3

Displaying a Plan Table with Parallel Information: ExampleRem Rem Execute an explain plan command for a parallel query Rem ALTER TABLE emp PARALLEL; EXPLAIN PLAN for SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename ='benoit' ORDER BY e.empno; Rem Rem Display the plan using the dbms_xplan.display() table function Rem SET LINESIZE 130 SET PAGESIZE 0 SELECT * FROM table(DBMS_XPLAN.DISPLAY); The above EXPLAIN PLAN produces output as follows:

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_XPLAN, 2 of 3

DISPLAY FunctionThis function displays the contents of the plan table.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_XPLAN, 1 of 3

Displaying a Plan Table Using DBMS_XPLAN.DISPLAY: ExampleRem Rem Execute an explain plan command on a SELECT statement Rem EXPLAIN PLAN FOR SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename='benoit'; Rem Rem Display the plan using the DBMS_XPLAN.DISPLAY() table function Rem SET LINESIZE 130 SET PAGESIZE 0 SELECT * FROM table(DBMS_XPLAN.DISPLAY); This query produces the following output: --------------------------------------------------------------------

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

Oracle9i Supplied PL/SQL Packages and Types Reference -- Contents

Oracle9i Supplied PL/SQL Packages and Types Reference -- ContentsConstants of DBMS_XMLSCHEMA Procedures and Functions of DBMS_XMLSCHEMA Catalog Views 90 DBMS_XPLAN Using … DBMS_XPLAN Summary of DBMS_XPLAN Subprograms Usage Notes 91 DBMS_XSLPROCESSOR Description of

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

What's New in Supplied PL/SQL Packages and Types?

Oracle9 i Release 2 (9.2) Beta New Features in Supplied PL/SQL Packages and TypesDBMS_XPLAN DBMS_XSLPROCESSOR JMS Types Logical Change Record Types Rule Types This release includes

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

Introduction, 2 of 2

Summary of Oracle Supplied PL/SQL PackagesXML schemas. Chapter 89 DBMS_XPLAN Describes how to format the output of the EXPLAIN PLAN command


Was this page helpful?