Oracle Database - DBMS_XPLAN

> Database > Oracle Database

1 - About

Dbms_xplan is a method for:

It displays:

  • the SQL execution plan (V$SQL_PLAN)
  • and SQL execution runtime statistics for cached SQL cursors (V$SQL_PLAN_STATISTICS_ALL)
  • plans from a SQL plan baseline.

It includs the supplemental information at the bottom of the report (new in Oracle9i release2)

3 - Syntax

3.1 - Display

to format and display the contents of a plan table. The query :

SELECT * FROM TABLE( dbms_xplan.display)

Will perform the same function than the @?rdms\admin\utlxpls.sql


4 - Actual Plan

Using it with the V$SQL_PLAN dynamic performance view, we can easily dump the query plans for already executed statements, directly from the database : Actual Plan with DBMS XPLAN

5 - Documentation / Reference