Search Results dbms_xplan




Overview
Explore in AI Chat

Overview

SYS.DBMS_XPLAN is an Oracle-supplied PL/SQL package that renders and formats execution plan information for SQL statements. Although owned by SYS and shipped with the Oracle database rather than with the E-Business Suite application schema, it plays a central role in EBS 12.1.1 and 12.2.2 environments because it is the standard mechanism by which DBAs, developers, and Oracle Support obtain readable access paths for SQL executed against the EBS database. The package is marked VALID in the ETRM metadata and is classified as an "OTHER" API rather than an EBS business API.

Its business value is diagnostic rather than transactional: it exposes the optimizer's chosen plan for a cursor, a stored outline, a SQL plan baseline, a SQL profile, or a historical AWR snapshot, translating internal plan table rows into human-readable text or XML.

Key Procedures and Functions

The ETRM metadata documents 25 procedures and functions in the package. The principal public entry points include:

  • DISPLAY — Formats and returns the execution plan for the statement most recently explained into PLAN_TABLE, including predicate and cost information.
  • DISPLAY_CURSOR — Formats the execution plan of a SQL statement currently present in the shared cursor cache, filtered by SQL ID, child number, and optionally by plan statistics level.
  • DISPLAY_AWR — Retrieves and formats the execution plan captured in Automatic Workload Repository snapshots, allowing historical plan review.
  • DISPLAY_SQLSET — Formats plans stored in a SQL Tuning Set.
  • DISPLAY_SQL_PLAN_BASELINE — Formats the plans associated with a SQL plan baseline, used in plan-stability analysis.
  • DISPLAY_SQL_PROFILE_PLAN — Formats the plan generated under a SQL profile.
  • DISPLAY_PLAN — Renders an execution plan directly from plan XML supplied to the routine.

Supporting functions such as PREPARE_RECORDS, PREPARE_PLAN_XML_QUERY, VALIDATE_FORMAT, BUILD_PLAN_XML, GET_PLAN_ROWS, and GET_CURSOR_ROWS perform the internal retrieval, validation, and row-fetching work. Formatting helpers include FORMAT_SIZE, FORMAT_NUMBER, FORMAT_SIZE2, FORMAT_NUMBER2, and FORMAT_TIME_S, which render byte sizes, numeric values, and timing in a consistent textual layout. I_DISPLAY_SMB_PLAN and DIFF_PLAN_OUTLINE support plan comparison and outline-based output.

Tables Accessed

The ETRM extract lists no APPS-synonym tables for this package. It relies instead on SYS-owned infrastructure and SQL-layer views such as PLAN_TABLE, V$SQL_PLAN, V$SQL_PLAN_STATISTICS_ALL, DBA_HIST_SQL_PLAN, and plan XML types. Internally the package references DBMS_XPLAN_TYPE_TABLE and XMLTYPE, which carry the formatted plan rows and structured plan documents. The two reporting tables shown in the metadata, WRI$_REPT_PLAN_DIFF and WRI$_REPT_XPLAN, belong to the SQL Performance Analyzer repository and consume formatted plan output.

Usage Notes

Within EBS, DBMS_XPLAN is rarely called from application forms or concurrent programs; it is invoked interactively from SQL*Plus, SQL Developer, or a DBA session against the EBS schema, typically as SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(...)) after locating a slow statement via AWR or SQL Monitoring. The package is also referenced by nine dependent packages, including DBMS_SQLTUNE, DBMS_SQLTUNE_INTERNAL, DBMS_SQLPA-related internals, DBMS_COMPARISON, and DBMS_SWRF_REPORT_INTERNAL, because those tools embed formatted plans in tuning reports and workload comparisons. In 12.2.2 it is commonly used alongside the Online Patching and ADOP diagnostics, and Oracle Support frequently requests DBMS_XPLAN output when analyzing performance issues.