Automatic Workload Repository in Oracle.
The Automatic Workload Repository (AWR) is a collection of database statistics owned by the SYS user in Oracle databases, introduced with Oracle 10g and still used in 18c. It is a tool that extends the key concepts of Statspack and collects performance statistics for problem detection and self-tuning purposes. The gathered data is stored both in memory and in the database and is displayed in both reports and views.
AWR collects wait events to identify performance problems, time model statistics, Active Session History (ASH) statistics, system and session statistics, object usage statistics, and resource-intensive SQL statements. It is a source of information for other Oracle features such as the Automatic Database Diagnostic Monitor (ADDM), SQL Tuning Advisor, Undo Advisor, and Segment Advisor.
Snapshots are taken every hour by default and retained for 7 days, but the retention period and interval can be altered using the DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings procedure. Extra snapshots can be taken and existing snapshots can be removed using the DBMS_WORKLOAD_REPOSITORY.create_snapshot and DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range procedures, respectively.
A baseline is a pair of snapshots that represents a specific period of usage, and it can be used to compare current performance against similar periods in the past4. Baselines are retained until explicitly deleted, and baseline information can be queried from the DBA_HIST_BASELINE view.
Workload Repository Views are available for querying AWR data, including V$ACTIVE_SESSION_HISTORY, V$METRIC, V$METRICNAME, V$METRIC_HISTORY, V$METRICGROUP, DBA_HIST_ACTIVE_SESS_HISTORY, and others. The optimal way to analyze AWR data is by using Enginatics AWR Blitz Reports, which removes the complexity in analyzing AWR in conjunction with Oracle EBS programs.
The Blitz Report can be scheduled through the standard concurrent manager and emailed on a periodic basis, providing a reference point at all times. The Ops Insights AWR Hub lets you consolidate and store detailed performance data from the Automatic Workload Repository (AWR) of your important Oracle Databases, allowing you to view and analyze historical performance data beyond the AWR retention period of the source database. Ops Insights extracts data from one or more source database targets and transfers it into the AWR Hub Warehouse, which is maintained independent of the source databases. The AWR Hub lets you keep a long-term history of AWR data from the selected database targets, enabling long-term analysis of AWR data across databases without performance or storage impact on the source database targets.