Oracle Database - DBMS_XPLAN

About

Dbms_xplan is a method for:

• querying and displaying the explain plan output.
• display the plan of a statement stored:
  • in the Automatic Workload Repository (AWR)
  • or in a SQL tuning set.

It displays:

• the SQL execution plan (VSQL_PLAN)
• and SQL execution runtime statistics for cached SQL cursors (VSQL_PLAN_STATISTICS_ALL)
• plans from a SQL plan baseline.

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

Articles Related

Syntax

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

Actual Plan

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

Documentation / Reference

• DBMS_XPLAN - Oracle® Database PL/SQL Packages and Types Reference 12c Release 1 (12.1)