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 next page

90
DBMS_XPLAN

The DBMS_XPLAN package provides an easy way to format the output of the EXPLAIN PLAN command. For more information on the EXPLAIN PLAN command, see Oracle9i Database Performance Tuning Guide and Reference.

This package runs with the privileges of the calling user, not the package owner (SYS).

This chapter discusses the following topics:

Using DBMS_XPLAN

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

Displaying a Plan Table Using DBMS_XPLAN.DISPLAY: Example

Rem
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:

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |    50 |     3 |
|*  1 | HASH JOIN            |             |     1 |    50 |     3 |
|*  2 | TABLE ACCESS FULL    | EMP         |     1 |    32 |     1 |
|   3 | TABLE ACCESS FULL    | DEPT        |     4 |    72 |     1 |
--------------------------------------------------------------------
Predicate Information (identified by operation id)
1 - access("E1"."DEPTNO"="D1"."DEPTNO")
2 - filter("E1"."ENAME"='benoit')

Go to previous page 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