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
• Summary of DBMS_XPLAN Subprograms
• Usage Notes

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