← Back to blog

What is Oracle AWR: Complete Guide to the Automatic Workload Repository

DBA Copilot Team · · 11 min read
oracle awr performance tuning monitoring

What is Oracle AWR: Complete Guide to the Automatic Workload Repository

If you work with Oracle databases, you have probably heard the term AWR dozens of times. It shows up in performance conversations, incident reports, and tuning recommendations. But what exactly is it, how does it work, and when should you use it?

This guide covers everything you need to know about Oracle AWR — from the basics to advanced usage.

What is AWR?

AWR stands for Automatic Workload Repository. It is Oracle's built-in performance data warehouse — a collection of tables and views stored in the SYSAUX tablespace that automatically captures and retains historical performance statistics about the database.

AWR was introduced in Oracle 10g (2003) and has been a cornerstone of Oracle performance management ever since. Every Oracle Enterprise Edition database has AWR running silently in the background, collecting data around the clock.

The key word is automatic. Unlike older tools that required manual configuration, AWR runs without any intervention. The database takes a snapshot — a point-in-time capture of hundreds of statistics — at regular intervals (every 60 minutes by default) and stores it for 8 days by default.

What Data Does AWR Collect?

Each AWR snapshot captures a comprehensive picture of what the database was doing. The data is organized into several categories:

Wait Events and Time Model - Time spent in each wait event (I/O, locking, network, etc.) - DB time breakdown: CPU vs. wait time - Time model statistics (parse time, PL/SQL execution, Java execution)

SQL Statistics - Top SQL statements by elapsed time, CPU, I/O, memory - Execution counts, rows processed, buffer gets, disk reads - Execution plan history (with dba_hist_sql_plan)

System Statistics - Logical and physical I/O rates - Parse counts (hard vs. soft) - Redo log activity - Buffer cache and shared pool utilization

OS and Instance Statistics - CPU utilization - Memory usage (SGA, PGA) - Processes and sessions

Segment Statistics - Which tables and indexes generated the most I/O - Row lock waits by segment - Buffer busy waits by segment

RAC-Specific Statistics (if applicable) - Global cache activity - Inter-instance messaging - Service-level statistics

All of this data is stored in dba_hist_* tables, most of which map directly to their v$* counterparts in memory.

How AWR Works: The Snapshot Mechanism

The AWR snapshot mechanism is elegantly simple:

Every N minutes (default: 60)
    │
    ▼
MMON background process wakes up
    │
    ▼
Reads cumulative statistics from v$* views (memory)
    │
    ▼
Saves a "snapshot" to dba_hist_* tables (SYSAUX tablespace)
    │
    ▼
Old snapshots beyond the retention window are purged

The MMON (Manageability Monitor) background process is responsible for taking snapshots. It runs automatically as part of the Oracle instance and requires no configuration.

Because the statistics in v$* views are cumulative (they count up from instance startup), an AWR report works by computing the difference between two snapshots. This delta tells you what happened during that specific time window.

For example, if v$sysstat shows 1,000,000 physical reads at snapshot 100 and 1,050,000 at snapshot 101, the AWR report shows 50,000 physical reads for that 60-minute window.

AWR vs. Other Oracle Performance Tools

AWR is one of several Oracle performance tools. Understanding when to use each is important:

Tool Granularity Best For License Required
AWR Snapshot interval (60 min default) Trend analysis, periodic performance review, post-incident analysis Diagnostics Pack
ASH 1-second samples Short incidents (< 30 min), identifying specific sessions/SQL Diagnostics Pack
Statspack Snapshot interval Same as AWR, no license needed None (free)
v$ views Real-time Live monitoring, current session activity None
SQL Trace Statement level Deep dive into a specific session or query None
EM Performance Hub Real-time + historical Visual performance analysis Diagnostics Pack

Key distinction: AWR and ASH work together. AWR captures aggregated statistics over an interval; ASH captures individual session samples every second. For a problem that lasted 2 hours, start with AWR. For a spike that lasted 5 minutes, ASH gives you much better precision.

AWR Licensing

This is a critical point that many DBAs overlook. AWR requires the Oracle Diagnostics Pack, which is a paid add-on to Oracle Enterprise Edition.

If your database is not licensed for the Diagnostics Pack: - You cannot legally run AWR reports - You cannot query dba_hist_* tables (the data is there, but querying it violates the license) - You cannot use ASH (v$active_session_history, dba_hist_active_sess_history) - You cannot use Automatic Database Diagnostics Monitor (ADDM)

What you can use instead: Statspack

Statspack is the free predecessor to AWR. It provides similar functionality — snapshot-based performance data, Top SQL, wait events, load profile — but requires manual installation and produces text-only reports. See our guide on How to Generate an Oracle AWR Report for the Statspack setup instructions.

Oracle Standard Edition and Standard Edition 2 do not include the Diagnostics Pack at any price. Only Enterprise Edition customers can purchase it.

Key AWR Configuration Settings

You can check and modify the AWR configuration with dba_hist_wr_control and dbms_workload_repository:

-- Check current AWR configuration
SELECT snap_interval,
       retention,
       most_recent_snap_id,
       most_recent_snap_time
FROM   dba_hist_wr_control;

Sample output: 

SNAP_INTERVAL       RETENTION           MOST_RECENT_SNAP_ID
+00000 01:00:00.0   +00008 00:00:00.0   4820

This shows snapshots every 60 minutes, retained for 8 days.

Changing the snapshot interval: 

-- Change to every 30 minutes, keep for 14 days
EXECUTE DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
  interval  => 30,
  retention => 14 * 24 * 60  -- minutes
);

Taking a manual snapshot: 

-- Useful before and after a planned change or load test
EXECUTE DBMS_WORKLOAD_REPOSITORY.create_snapshot();

-- Get the snap_id of the snapshot just created
SELECT MAX(snap_id) FROM dba_hist_snapshot;

Deleting a range of snapshots: 

-- Free up SYSAUX space by removing old snapshots
EXECUTE DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range(
  low_snap_id  => 4800,
  high_snap_id => 4810
);

How Much Space Does AWR Use?

AWR stores its data in the SYSAUX tablespace. The space consumed depends on: - Snapshot frequency (more frequent = more data) - Retention period - Number of SQL statements in the workload (high-parse environments generate more data) - Number of segments, objects, and users

Check current AWR space usage:

SELECT occupant_name,
       space_usage_kbytes / 1024 AS space_mb,
       schema_name
FROM   v$sysaux_occupants
WHERE  occupant_name LIKE 'SM/%'
ORDER  BY space_usage_kbytes DESC;

On a typical OLTP database with default settings (60-minute snapshots, 8-day retention): - Small database (< 50 SQL statements in workload): ~200-500 MB - Medium database: ~500 MB - 2 GB - Large, high-parse database: 2-10 GB

If SYSAUX is running low on space, reduce retention or increase the tablespace:

-- Reduce retention to 3 days to free space
EXECUTE DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(retention => 3 * 24 * 60);

Reading an AWR Report: The Key Sections

An AWR report has 50+ sections. Most of the time, you only need to read a handful. Here is the order of priority:

1. Report Summary Header

The first thing to read. Shows the time window, database name, instance, and — critically — the DB time for the period. This tells you the scale of the problem.

DB Time:         245.23 (mins)
DB CPU:           18.44 (mins)
Elapsed:          60.05 (mins)

In this example, 245 minutes of DB time were consumed in 60 real minutes — an average of ~4 concurrent active sessions. If this is a single-user test machine, 4 sessions is high. If it's a 200-user OLTP system, 4 is extremely low.

2. Top 10 Foreground Events by Total Wait Time

The most important section. This tells you where the time went. The events with the highest % DB time are your tuning targets.

3. Load Profile

Shows key rates per second and per transaction: logical reads, physical reads, executes, parses, commits. Compare against your baseline to spot anomalies.

4. SQL Ordered by Elapsed Time

The specific SQL statements consuming the most total time. Start here for query tuning.

5. SQL Ordered by Gets / Reads / CPU

Alternative views of the Top SQL. "Gets" (logical I/O) often identifies different culprits than elapsed time.

6. Instance Activity Statistics

Raw statistics counts for the period. Useful for checking parse ratios, redo activity, and other instance-level metrics.

7. Segments by Physical Reads / Buffer Busy Waits

Identifies the specific tables and indexes causing I/O or contention.

For a detailed guide to reading wait events in AWR, see Oracle Wait Events Explained.

AWR Baselines

AWR baselines let you capture a "known good" snapshot range and compare it against future reports. This is invaluable for detecting gradual degradation.

-- Create a baseline from a good performance window
EXECUTE DBMS_WORKLOAD_REPOSITORY.create_baseline(
  start_snap_id => 4800,
  end_snap_id   => 4810,
  baseline_name => 'Normal Monday Morning'
);

-- List existing baselines
SELECT baseline_id, baseline_name, start_snap_id, end_snap_id
FROM   dba_hist_baseline;

Once a baseline is defined, the AWR Compare Periods report (awrddrpt.sql) can generate a diff between the baseline and any other time window, showing exactly what changed.

AWR in Oracle RAC

In a RAC environment, each node (instance) generates its own set of AWR snapshots with the same snap_id range. You have several reporting options:

Single-instance report (awrrpti.sql): Report for one specific instance — useful when a problem is confined to one node.

Global RAC report (awrgrpt.sql): Aggregated across all instances — useful for cluster-wide analysis.

RAC diff report (awrgdrpt.sql): Compares two time periods across the cluster.

To check which instances have snapshots: 

SELECT DISTINCT instance_number, instance_name,
       MIN(begin_interval_time) AS oldest_snap,
       MAX(end_interval_time)   AS newest_snap
FROM   dba_hist_snapshot
GROUP  BY instance_number, instance_name
ORDER  BY instance_number;

AWR Export and Import

You can export AWR data from a production database and import it into a development system for analysis — without granting anyone access to production.

-- Export AWR data to a dump file
EXECUTE DBMS_SWRF_HELPER.awr_extract(
  dmpfile  => 'awr_export.dmp',
  dmpdir   => 'DATA_PUMP_DIR',
  bid      => 4800,
  eid      => 4820
);

-- Import on another database
EXECUTE DBMS_SWRF_HELPER.awr_load(
  schname => 'AWR_STAGING',
  dmpfile => 'awr_export.dmp',
  dmpdir  => 'DATA_PUMP_DIR'
);

This is particularly useful for: - Analysing production incidents in a safe environment - Sharing AWR data with Oracle Support - Long-term archiving beyond the default 8-day retention

Limitations of AWR

AWR is powerful but has limitations to be aware of:

1. It's a sampling tool, not a trace AWR captures statistics at snapshot boundaries. Events shorter than the snapshot interval (a 5-minute spike in a 60-minute window) will be diluted. Use ASH for short incidents.

2. It does not capture every SQL statement The dba_hist_sqlstat table only retains the top SQL statements that met a threshold for resource consumption. Low-impact but frequently executed SQL may not appear.

3. SYSAUX dependency If the SYSAUX tablespace is full or has I/O issues, AWR snapshot jobs will fail silently. Monitor SYSAUX space regularly.

4. It requires a license As discussed above, AWR is not free. Many organizations run Enterprise Edition databases without the Diagnostics Pack, making AWR unavailable.

5. Baseline comparison requires planning AWR baselines are only useful if you create them during known-good periods. You cannot retroactively create a useful baseline.

Frequently Asked Questions

Is AWR enabled by default? Yes. On any Oracle Enterprise Edition database with the Diagnostics Pack license, AWR is enabled automatically. There is no configuration needed to start collecting data. Snapshots begin as soon as the database is created.

Can I disable AWR? Yes, but it is not recommended. You can set the snapshot interval to 0 to stop automatic snapshots: DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(interval => 0). However, this also disables ADDM and other features that depend on AWR.

What is ADDM? The Automatic Database Diagnostic Monitor is an Oracle feature that automatically analyses AWR data and generates recommendations. After each AWR snapshot, ADDM compares it to the previous one and identifies the top performance bottlenecks. ADDM findings appear in the AWR report and in Enterprise Manager. It also requires the Diagnostics Pack license.

How is AWR different from v$ views? v$ views show current (real-time) data from the database's memory structures. AWR (dba_hist_*) stores historical snapshots of that data. You use v$ for live monitoring ("what is happening right now?") and AWR for historical analysis ("what was happening at 2am last Tuesday?").

Can AWR data be used in court or for compliance purposes? AWR is a performance tool, not an audit tool. For compliance and auditing requirements, Oracle Audit Vault or Unified Auditing are the appropriate tools. AWR data can be purged and modified by users with DBA privileges.

What happens to AWR data when the database is upgraded? AWR data is migrated during the upgrade process. Historical snapshots are retained after upgrading from one Oracle version to another (e.g. 12c to 19c), though some statistics may not be directly comparable across versions.

Analyse Your AWR Data Automatically

Understanding AWR takes experience — knowing which sections matter, what numbers indicate a problem, and how to correlate wait events with specific SQL statements.

DBA Copilot does this analysis automatically. Upload your AWR HTML report and the AI extracts the key sections, identifies the dominant wait events and top SQL, and generates a plain-language diagnosis with prioritised recommendations — in seconds.

No direct database connection required. No need to share credentials. Just upload the report file.

Try DBA Copilot free — no credit card required

Ready to generate your first AWR report? See: How to Generate an Oracle AWR Report
Understanding what you see in the report? See: Oracle Wait Events Explained

Want to analyze your database performance automatically?

Try DBA Copilot free — upload your AWR/ASH report and get an AI-powered diagnosis in seconds.

Start for free