Oracle AWR and ASH — Database Performance Diagnostics

When a client says the application is slow, the first place we look is the Oracle AWR report. Automatic Workload Repository is the foundation of Oracle database performance diagnostics.

AWR — What It Is¶

Oracle automatically collects performance statistics every hour (snapshots). An AWR report compares two snapshots and shows: top SQL queries (by time, I/O, CPU), wait events (what the database is waiting on), system statistics (IOPS, throughput), and SGA/PGA utilization.

How to Read an AWR Report¶

DB Time — total time spent on processing. Top 5 Timed Events — what the database is waiting on the most. db file sequential read = random I/O (indexes). db file scattered read = full table scan. log file sync = waiting for redo log writes. If CPU dominates, the problem is in SQL. If I/O dominates, the problem is in storage or missing indexes.

ASH — Real-Time View¶

Active Session History — samples active sessions every second. V$ACTIVE_SESSION_HISTORY for the last half hour, DBA_HIST_ACTIVE_SESS_HISTORY for history. The ASH report shows what the database was doing at a specific point in time — invaluable for diagnosing sporadic issues.

Top SQL Analysis¶

The AWR top SQL section shows queries with the highest total time. Each query has a SQL ID, execution plan hash, number of executions, and average time. A change in the execution plan hash means the optimizer changed the plan — a potential problem.

Automated Reporting¶

A script generates an AWR report every day and emails it to the DBA team. Comparison with the previous day — automatic detection of regressions (top SQL change, wait event spike).

Conclusion¶

AWR and ASH are the most powerful diagnostic tools for an Oracle DBA. Learn to read them — they save hours of guesswork. Automate daily reports and monitor trends. A problem detected from AWR before the client notices is a problem nobody ever saw.