AWR (Automatic Workload Repository) and ASH (Active Session History) are Oracle's most powerful built-in diagnostic tools, but most DBAs only look at the top few sections. This guide walks through every section of both reports — what each metric means, what's normal, and what's a red flag.
AWR Fundamentals
AWR snapshots ~1,200 cumulative statistics every 60 minutes (default) and stores them in SYSAUX. A report compares two snapshots to show delta activity. The key thing to understand: AWR shows averages over the interval — a 60-minute report with one 5-minute crisis looks fine. Use ASH for point-in-time diagnosis.
-- Generate AWR report manually
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
-- Check snapshot availability
SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id DESC
FETCH FIRST 10 ROWS ONLY;
-- AWR settings
SELECT snap_interval, retention
FROM dba_hist_wr_control;Default retention is 8 days, interval 60 minutes. For busy production systems, consider increasing retention to 30 days and decreasing interval to 30 minutes for better granularity.
Section 1: Report Header and DB Time
The very first section shows elapsed time, DB Time, and CPUs. DB Time is the single most important number in the entire report.
Snap Id Snap Time Sessions Curs/Sess
------- --------- -------- ---------
Begin: 100 01-Jul-26 09:00:00 245 3.2
End: 101 01-Jul-26 10:00:00 251 3.3
Elapsed: 60.05 (mins)
DB Time: 387.23 (mins)DB Time = sum of all time all sessions spent doing work (CPU + wait events). With 8 CPUs and a 60-minute interval, your theoretical maximum DB Time is 480 minutes (8 × 60). A DB Time of 387 minutes means you're using ~80% of CPU capacity — which is high but not yet critical.
If DB Time significantly exceeds CPUs × elapsed time, you have more demand than CPU can service — either add CPUs, optimize SQL, or reduce workload.
Section 2: Load Profile
Per Second Per Transaction
---------- ---------------
Logical reads: 45,823.2 1,234.5
Block changes: 1,203.4 32.4
Physical reads: 2,341.2 63.1
Physical writes: 456.7 12.3
User calls: 1,234.5 33.2
Parses: 892.3 24.0
Hard parses: 23.4 0.6
Executes: 3,456.7 93.1What to look for:
- Hard parses — should be <1% of total parses. High hard parses mean missing bind variables, cursor_sharing issues, or excessive DDL. Every hard parse locks the shared pool latch.
- Physical reads vs logical reads — the ratio tells you buffer cache hit ratio. Physical reads >5% of logical reads suggests buffer cache is undersized.
- Parses ≈ Executes — means no cursor reuse. Applications should parse once and execute many times.
Section 3: Instance Efficiency Percentages
Buffer Nowait %: 99.98 Redo NoWait %: 99.99
Buffer Hit %: 97.23 In-memory Sort %: 99.87
Library Hit %: 99.12 Soft Parse %: 97.38
Execute to Parse %: 74.23 Latch Hit %: 99.94
Parse CPU to Parse Elapsd %: 45.23
% Non-Parse CPU: 99.12Thresholds that matter:
- Buffer Hit % — should be >95%. Below 90% is critical. This is your buffer cache effectiveness.
- Library Hit % — should be >99%. Low value means shared pool pressure or cursor invalidation.
- Soft Parse % — should be >95%. Low means application isn't reusing cursors.
- Execute to Parse % — should be high (close to 100%). Low means parse-heavy workload.
- Parse CPU to Parse Elapsed % — if this is low (say <30%), time is being spent waiting for latches during parsing, not actually parsing. Shared pool latch contention.
Section 4: Top 5 Timed Events — The Most Important Section
This section shows where DB Time was actually spent. Every tuning exercise starts here.
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Avg Wait DB Time
-------------------------- -------- --------- -------- -------
db file sequential read 234,567 1,823.4 7.77 78.4%
CPU time 389.2 16.7%
log file sync 12,345 89.3 7.23 3.8%
db file scattered read 3,456 34.5 9.99 1.5%
latch: shared pool 234 8.9 38.0 0.4%How to read this:
- db file sequential read (single-block reads, typically index lookups) dominating at 78% means the database is reading lots of index blocks from disk. Either the buffer cache is too small, or there are missing indexes causing full scans that look like sequential reads at the block level.
- CPU time at 16% is healthy — you want CPU to be a significant contributor, meaning the database is actually computing, not just waiting.
- log file sync — each COMMIT waits for LGWR to flush redo to disk. High values mean too many small commits, slow storage for redo logs, or I/O subsystem issues.
- db file scattered read (multi-block reads, typically full table scans) — high values suggest missing indexes or intentional full scans on large tables.
- latch: shared pool at 38ms average wait is high — shared pool fragmentation or hard parse storm.
- User I/O — db file sequential/scattered read, direct path read. Storage performance issue.
- Commit — log file sync. Too many commits or slow redo storage.
- Concurrency — latch waits, library cache lock, row cache lock. Contention in SGA.
- Application — enq: TX, enq: TM. Row-level or table-level locking by application code.
- Configuration — log buffer space, archiver stuck. Configuration problems.
- Network — SQL*Net waits. Client/server network issues.
Section 5: SQL Statistics
SQL ordered by Elapsed Time
SQL ordered by Elapsed Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Resources Reported for PL/SQL Code Includes Code executed by Code
Elapsed CPU IO Executions Elapsed Time
Time (s) Executions Time (s) Waits (s) Per Exec (s) SQL Id
---------- ---------- -------- --------- ------------ ------------ --------
1,234.56 12 234.56 1,000.00 12 102.88 a1b2c3d4eThis is your primary SQL tuning target list. Look for:
- High elapsed time with few executions — individual slow queries. Explain plan, look for full table scans or bad cardinality estimates.
- Low elapsed time with millions of executions — high-frequency queries. Even 1ms improvement × 1M executions = 1000 seconds saved.
- High IO Waits relative to CPU time — missing indexes or full scans.
-- Pull the actual SQL text and current plan
SELECT sql_text FROM dba_hist_sqltext WHERE sql_id = 'a1b2c3d4e';
-- Historical execution stats
SELECT snap_id, executions, elapsed_time/1000000 elapsed_s,
buffer_gets, disk_reads
FROM dba_hist_sqlstat
WHERE sql_id = 'a1b2c3d4e'
ORDER BY snap_id DESC;SQL ordered by Gets (logical reads)
High logical reads mean a SQL is touching many buffer cache blocks — either due to large result sets, missing indexes, or bad plans. A single SQL with 100M buffer gets per execution is a tuning emergency regardless of its elapsed time.
SQL ordered by Parse Calls
High parse calls with low executions means bind variables are missing — the application is sending a new literal value each time (WHERE id = 1234, WHERE id = 1235, ...) instead of WHERE id = :bind_var. Each unique literal = a new cursor = a hard parse = shared pool fragmentation.
Section 6: Memory Statistics
Memory Statistics
~~~~~~~~~~~~~~~~~
Begin End
SGA use (MB): 4,096 4,097
PGA use (MB): 1,234 1,289
SGA breakdown:
Buffer Cache: 2,048 MB
Shared Pool: 1,024 MB
Large Pool: 64 MB
Java Pool: 64 MB
Streams Pool: 32 MBCross-reference with Instance Efficiency:
- Buffer Hit% <95% + Buffer Cache small relative to working set → increase DB_CACHE_SIZE
- Library Hit% <99% + Shared Pool under pressure → increase SHARED_POOL_SIZE or investigate cursor sharing
- PGA growing significantly between begin/end → sort/hash operations spilling to disk, increase PGA_AGGREGATE_TARGET
Section 7: I/O Statistics
Tablespace IO Stats
~~~~~~~~~~~~~~~~~~~
Tablespace Reads Av Reads/s Av Rd(ms) Writes Av Writes/s
------------------ ----- ---------- --------- ------ -----------
APPS_TS_TX_DATA 45,678 12.66 8.23 12,345 3.43
UNDOTBS1 2,345 0.65 3.45 456 0.13
SYSTEM 123 0.03 2.12 45 0.01Key metric: Av Rd(ms) — average read time in milliseconds.
- <5ms — excellent (SSD/NVMe)
- 5-15ms — acceptable (SSD)
- 15-30ms — marginal (SAN/HDD)
- >30ms — problem. Storage is slow or saturated.
ASH — Active Session History
While AWR averages 60 minutes, ASH samples every active session every second and keeps the last 30-60 minutes in memory (plus history in DBA_HIST_ACTIVE_SESS_HISTORY). This is your point-in-time diagnostic tool.
-- What was happening 30 minutes ago?
SELECT event, COUNT(*) sessions
FROM v$active_session_history
WHERE sample_time BETWEEN SYSDATE - 40/1440 AND SYSDATE - 30/1440
GROUP BY event
ORDER BY sessions DESC;
-- Top SQL by ASH samples (= time consuming)
SELECT sql_id, COUNT(*) samples,
ROUND(COUNT(*)/SUM(COUNT(*)) OVER () * 100, 1) pct
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24
GROUP BY sql_id
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;
-- Session timeline for a specific SQL
SELECT TO_CHAR(sample_time,'HH24:MI:SS') sample_time,
session_id, session_serial#,
event, wait_class, sql_plan_hash_value
FROM v$active_session_history
WHERE sql_id = 'a1b2c3d4e'
AND sample_time > SYSDATE - 1/24
ORDER BY sample_time;ASH Report Sections
The ASH report (@$ORACLE_HOME/rdbms/admin/ashrpt.sql) is structured differently from AWR:
Top User Events — what were sessions waiting on. Unlike AWR's Top 5 which averages the whole period, ASH shows you the distribution over time. You can see a spike at 10:15 that the AWR average buried.
Top Background Events — what LGWR, DBWR, CKPT, and other background processes were doing. High background waits often indicate I/O subsystem issues independent of user activity.
Top SQL with Top Events — combines SQL identity with wait events. This tells you not just which SQL is slow but why it's slow (I/O waits vs CPU vs locking).
Activity Over Time — the timeline chart showing session count by wait class per minute. This is where you identify the exact minute something went wrong. A spike in Application waits at 10:14 correlates to the batch job that ran at 10:13.
Real-time ASH for live incidents
-- What's happening right now — sessions by wait event
SELECT event, wait_class, COUNT(*) active_sessions,
LISTAGG(sql_id, ',') WITHIN GROUP (ORDER BY sql_id) sql_ids
FROM v$session
WHERE status = 'ACTIVE'
AND type = 'USER'
AND wait_class != 'Idle'
GROUP BY event, wait_class
ORDER BY active_sessions DESC;
-- Find the blocking session chain
SELECT level, s.sid, s.serial#, s.username,
s.event, s.wait_class,
s.blocking_session,
s.sql_id,
s.seconds_in_wait
FROM v$session s
START WITH s.blocking_session IS NULL
AND s.status = 'ACTIVE'
AND s.type = 'USER'
CONNECT BY PRIOR s.sid = s.blocking_session
ORDER SIBLINGS BY seconds_in_wait DESC;Putting It Together — A Tuning Workflow
- Start with AWR Top 5 Timed Events — identify the dominant wait class
- Check DB Time vs CPUs × elapsed time — are you CPU-bound or wait-bound?
- If I/O waits dominate: go to I/O statistics, check Av Rd(ms) per tablespace, correlate with SQL by disk reads
- If CPU dominates: go to SQL by CPU time, look for parse-heavy or full-scan SQL
- If locking/concurrency: use ASH to find the blocking chain at the time of the incident
- For specific SQL: pull from DBA_HIST_SQLSTAT, compare plans across snapshots, look for plan changes
TuneVault ADDM and Performance Advisor
TuneVault's Performance Advisor automates this entire workflow — running ADDM analysis across your AWR snapshot history and surfacing the top findings ranked by DB Time impact. Rather than reading 40-page AWR reports manually, you get the three things that matter most and the exact SQL to fix them. Connect at tunevault.app to run your first ADDM analysis.