Search Results compare_instances

Overview

DBMS_ADDM is a SYS-owned PL/SQL package that exposes the Automatic Database Diagnostic Monitor (ADDM) framework to database sessions. In Oracle E-Business Suite 12.1.1 and 12.2.2, it serves as the programmatic interface through which database administrators and diagnostic infrastructure components perform performance analysis of the EBS database instance without relying solely on Enterprise Manager. The package enables targeted analysis of the whole database, a single instance, or a bounded time range, and it allows findings to be retrieved, compared, and refined through directives. Its role is diagnostic rather than transactional: it does not participate in EBS application business logic, but it is essential for investigating performance degradation in the highly concurrent, shared-schema environment typical of an EBS environment. The documented status is VALID, and the object resides in the SYS schema while remaining accessible to PUBLIC through a synonym.

Key Procedures and Functions

The package exposes twenty documented procedures and functions. The core analysis entry points are ANALYZE_DB, which runs ADDM across the entire database; ANALYZE_INST, which restricts the analysis to a specific instance; and ANALYZE_PARTIAL, which limits the analysis to a defined subset or time window. Report retrieval is handled by GET_REPORT and GET_ASH_QUERY, the latter returning the underlying Active Session History query associated with the analysis. REAL_TIME_ADDM_REPORT supports near-real-time diagnostic output.

Directive management is divided into insertion and deletion pairs that let an administrator suppress or emphasize specific findings. INSERT_FINDING_DIRECTIVE and DELETE_FINDING_DIRECTIVE operate on finding identifiers; INSERT_SQL_DIRECTIVE and DELETE_SQL_DIRECTIVE target specific SQL statements; INSERT_SEGMENT_DIRECTIVE and DELETE_SEGMENT_DIRECTIVE target segments; and INSERT_PARAMETER_DIRECTIVE and DELETE_PARAMETER_DIRECTIVE act on system parameters. A generic DELETE procedure removes stored task results.

Comparative analysis is provided by COMPARE_DATABASES, COMPARE_INSTANCES, COMPARE_CAPTURE_REPLAY_REPORT, and COMPARE_REPLAY_REPLAY_REPORT, which contrast periods, instances, or workload capture and replay reports. These comparators are valuable in EBS for validating the impact of a patch, parameter change, or configuration shift.

Tables Accessed

The ETRM metadata records no application tables accessed through APPS synonyms, which is consistent with DBMS_ADDM being a database-layer component rather than an EBS application object. Its internal dependencies reference SYS and STANDARD, and the package is referenced by PUBLIC, DBMS_WORKLOAD_REPLAY, and PRVT_HDM. The underlying ADDM and ASH data it reads is held in the automatic workload repository and Active Session History structures owned by SYS, while directives and task results are persisted in the ADDM task tables within the SYSAUX tablespace. Administrators should therefore expect ADDM operations to consume space in SYSAUX rather than in EBS product schemas.

Usage Notes

DBMS_ADDM is not invoked by EBS forms or standard concurrent programs. It is typically called from SQL*Plus or another privileged session by a DBA holding the ADVISOR or equivalent privileges, often as part of performance triage documented in Oracle support processes. Because it is referenced by DBMS_WORKLOAD_REPLAY, it also participates indirectly in workload replay diagnostics. When running ADDM against an EBS database, DBAs commonly scope the analysis with ANALYZE_PARTIAL around a known window of slowdown to avoid long runtimes, and they use the directive procedures to filter findings unrelated to the issue under investigation. Custom EBS diagnostic scripts should treat this package as read-only with respect to application data and should not be embedded in online transaction paths.