Search Results dbms_workload_repository




Overview
Explore in AI Chat

Overview

SYS.DBMS_WORKLOAD_REPOSITORY is the Oracle-supplied PL/SQL package that provides the programmatic interface to the Automatic Workload Repository (AWR). Within an Oracle E-Business Suite 12.1.1 or 12.2.2 installation, the package owns and manages the persistent performance snapshots, baselines, and reports that Oracle Database uses for historical performance analysis. EBS relies on AWR indirectly through Database Control, Enterprise Manager, and the Oracle Diagnostics and Tuning Packs, all of which call this package rather than accessing the underlying WRH$ and DBA_HIST_* views directly.

The package is owned by SYS and is marked VALID in the ETRM documentation. Its API classification is OTHER, meaning it is a database-supplied utility rather than an EBS application API. Its metadata shows 43 documented procedures and functions and it is referenced by nine other packages, including DBMS_MANAGEMENT_PACKS, DBMS_SWRF_INTERNAL, DBMS_WRR_INTERNAL, and PRVT_AWR_VIEWER. The dependency listing further shows references to the AWR report collection types (AWRBL_DETAILS_TYPE_TABLE, AWRRPT_TEXT_TYPE_TABLE, AWRSQRPT_TEXT_TYPE_TABLE, and related types), confirming that report generation is an intrinsic part of the package design.

Key Procedures and Functions

The documented entry points fall into four functional groups.

Parameter lists for these procedures are defined by Oracle and should be consulted in the Database PL/SQL Packages and Types Reference for the exact release in use; they are not overridden by EBS.

Tables Accessed

The package reads and writes the AWR repository tables and their internal synonyms, exposed through the DBA_HIST_* views. The ETRM metadata explicitly names DBA_HIST_BASELINE and DBA_HIST_BASELINE_DETAILS as dependent objects, together with their internal counterparts INT$DBA_HIST_BASELINE and INT$DBA_HIST_BASELINE_DETAILS. Snapshot data is stored in the WRH$ and WRM$ tables underlying the DBA_HIST views. These tables exist only when the STATISTICS_LEVEL initialization parameter is set to TYPICAL or ALL and the AWR retention and interval settings are active; otherwise, the package cannot collect workload data.

Usage Notes

In an EBS environment, DBMS_WORKLOAD_REPOSITORY is normally invoked by the database itself through the automatic snapshot scheduler rather than by application code. DBAs call it manually to create snapshots before and after a performance test, to generate comparisons, or to pin a baseline around a known-good period. It is also invoked by the Oracle Enterprise Manager AWR pages and by the EBS-specific performance diagnostics that surface in Oracle Application Manager. Custom EBS code should not wrap this package, because it is a SYS-owned database component and EBS does not support modification of its underlying AWR tables. Consumers should instead use the DBA_HIST_* views for read-only reporting. The package's dependency on DBMS_SWRF_INTERNAL and DBMS_WRR_INTERNAL indicates that report rendering and warehouse refresh paths are internally shared, so direct manipulation of AWR data outside these packaged calls risks inconsistent repository state.