Search Results dbms_perf




Overview
Explore in AI Chat

Overview

SYS.DBMS_PERF is an Oracle-supplied PL/SQL package that provides the performance diagnostics and reporting infrastructure used by the Automatic Workload Repository (AWR), Active Session History (ASH), and related performance monitoring components. Within Oracle E-Business Suite 12.1.1 and 12.2.2, the package serves as the underlying engine that generates performance reports from data collected in the database's performance repository. It is a SYS-owned object with VALID status, and its API classification is documented as OTHER in the ETRM repository, reflecting its role as an internal Oracle Database utility rather than an EBS application-specific API.

Although DBMS_PERF is not an EBS product schema package, it is directly relevant to EBS environments because performance troubleshooting in EBS deployments frequently relies on AWR, ASH, and SQL performance reports. The package is referenced by other SYS components including DBMS_SWRF_REPORT_INTERNAL, PRVT_AWR_VIEWER, and WRI$_REPT_PERF, all of which participate in the report generation and repository views used by the AWR reporting framework.

Key Procedures and Functions

ETRM documents seven procedures and functions within SYS.DBMS_PERF. Each is identified by name and purpose below; parameter lists are intentionally omitted because they are not part of the documented metadata.

  • REPORT_PERFHUB — Generates a consolidated performance dashboard report, assembling multiple performance perspectives into a single output suitable for the performance hub view.
  • REPORT_PERFHUB_XML — Produces the XML representation of the performance hub report, enabling rendering by external clients or report viewers.
  • REPORT_SQL — Generates a performance report scoped to one or more SQL statements, driven by data collected for the identified SQL identifiers.
  • REPORT_SQL_XML — Returns the XML form of the SQL performance report for downstream processing and display.
  • REPORT_SESSION — Generates a session-level performance report, useful for diagnosing activity attributable to a specific database session.
  • REPORT_SESSION_XML — Produces the XML variant of the session performance report.
  • REPORT_ADDM_WATCHDOG_XML — Generates an XML watchdog report associated with Automatic Database Diagnostic Monitor (ADDM) activity, highlighting findings surfaced by the ADDM analysis engine.

Tables Accessed

The provided ETRM metadata does not enumerate specific tables accessed via APPS synonyms. In practice, DBMS_PERF operates against the Oracle performance repository base tables and views, including the workload repository (WRH$ and WRI$ family) and ASH-related structures. Dependencies documented in ETRM show that the package references STANDARD and XMLTYPE, and that WRI$_REPT_PERF references the package, confirming tight coupling between DBMS_PERF and the internal repository reporting structures. Because DBMS_PERF is a SYS package, its table access occurs directly against SYS-owned dictionary objects rather than through APPS synonyms.

Usage Notes

DBMS_PERF is typically invoked indirectly rather than called directly by EBS application code. In EBS 12.1.1 and 12.2.2 environments, it is most commonly exercised through database performance tooling—such as Enterprise Manager, SQL Developer, or the AWR/ASH report interfaces—when DBAs investigate performance issues affecting the EBS database tier. The package is referenced by three other packages in the dependency chain, including DBMS_SWRF_REPORT_INTERNAL, PRVT_AWR_VIEWER, and WRI$_REPT_PERF, reflecting its role as a shared reporting primitive. Custom code should avoid direct invocation unless explicitly required, since the package is Oracle-proprietary, subject to change between database releases, and not part of the supported EBS application API surface. For EBS-specific diagnostics, DBAs should continue to use the supported AWR and ASH report generation paths that ultimately call into DBMS_PERF.