What is AWR report?
If a database is like a patient, an AWR (Automatic Workload Repository) Report is the full cardiovascular stress test, blood panel, and MRI combined.
It is the primary tool used by Oracle DBAs to diagnose the health of a database over a specific window of time. If someone says, "The database was slow yesterday at 2:00 PM," the AWR report is where you go to find out why.
Oracle snapshots its own internal performance counters (Wait Events, System Statistics, SQL execution data) every hour (by default) and stores them in the SYSAUX tablespace.
An AWR Report compares two of these snapshots (a "Start" and an "End") and shows you the delta—the difference between them. This allows you to see exactly what happened during that interval.
When you open an AWR report (usually an HTML file), it can be overwhelming. To avoid getting lost, focus on these three sections first:
This section tells you how "busy" the database was.
Key Metric: DB Time. If "DB Time" is much higher than the "Elapsed Time," your database was working hard (or waiting hard).
Look For: High "Hard Parses" (suggests missing bind variables) or high "Physical Reads" (suggests poor indexing).
This is the "Smoking Gun" section. It tells you what the database was doing when it wasn't processing data.
db file sequential read: Usually means single-block reads (Index lookups).
db file scattered read: Usually means multi-block reads (Full Table Scans).
log file sync: Suggests the disk where your redo logs live is slow, or you are committing too frequently.
enq: TX - row lock contention: Someone is locking rows and making others wait.
AWR identifies the "Heavy Lifters." It lists the specific SQL statements that consumed the most resources during the snapshot period. Even if the database is "fine," you can often find 2 or 3 queries here that are responsible for 80% of the total load.
If you have the Diagnostic Pack license, you can generate the report via SQL*Plus by running:
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
You will be prompted to choose:
Format: (HTML is best for reading; Text is best for sharing).
Number of days: How far back to look.
Snapshots: The "Start" ID and "End" ID that cover your "slow" period.
AWR: Best for Trends. It gives a high-level view of an hour or a day. It tells you "The database was slow."
ASH (Active Session History): Best for Right Now. It samples data every second. It tells you "This specific user is stuck on this specific line of code right now."
| Section | What to check | Potential Issue |
| Top Events | Is CPU time the top event? | Good! The DB is working. |
| Top Events | Is User I/O the top event? | Bad. Check for missing indexes or slow disks. |
| SQL Statistics | High Buffer Gets per execution? | The query is reading too much data from memory. |
| Host CPU | Is %User plus %Sys near 100%? | The server itself is overloaded. |
AWR is a feature of the Oracle Enterprise Edition Diagnostic Pack. Even though the scripts are sitting on your server, you technically need a license to run them and query the underlying WRH$ tables. Always check your licensing agreement before making AWR a standard part of your daily routine!
If the database is slow today but was fast yesterday, don't just look at today's AWR. Run an AWR Diff Report (awrrptdiff.sql). It compares two different time periods side-by-side and highlights exactly what changed—whether it's a specific SQL plan or a system-wide wait event.