Hunting an Oracle database bottleneck with AWR and ASH

When enterprise incident channels report that “the database has slowed down,” the worst initial reaction is blindly executing SELECT * FROM v$session and guessing what users are doing.

Diagnosing root causes without speculation and without perturbing live operations requires methodical analysis of AWR (Automatic Workload Repository) and ASH (Active Session History) metrics.

1. Deconstructing the Top Timed Events Table

Opening an AWR report begins with the Top 10 Foreground Events by Total Wait Time section. This indicates whether the engine is burning CPU cycles or bottlenecked on shared resources:

• db file sequential read: Single-block read operations typical of index lookups. If this accounts for >70% of total wait time, the system is thrashing disk I/O due to missing indexes or unselective b-trees.
• db file scattered read: Multi-block reads caused by Full Table Scans. Often indicates dropped indexes or skewed optimizer statistics.
• enq: TX - row lock contention: Application-level transaction conflicts where concurrent processes attempt to mutate the same records simultaneously.

2. Minute-by-Minute Granularity via ASH

While AWR produces 60-minute aggregated snapshots, ASH samples active sessions once per second. To identify what occurred during a sudden spike at 14:15:

SELECT sql_id, 
       event, 
       COUNT(*) as sample_count
FROM v$active_session_history
WHERE sample_time BETWEEN TO_TIMESTAMP('2025-11-12 14:15:00', 'YYYY-MM-DD HH24:MI:SS')
                      AND TO_TIMESTAMP('2025-11-12 14:25:00', 'YYYY-MM-DD HH24:MI:SS')
  AND session_state = 'WAITING'
GROUP BY sql_id, event
ORDER BY sample_count DESC;

This immediately pinpoints the exact SQL_ID generating the dominant wait states during the anomaly window.

3. Production Incident: Histogram Skew and Plan Flips

During a MetaDB integration incident on a multi-terabyte Oracle database, a routine invoice query that historically took 20ms suddenly spiked to 45 seconds per execution.

The AWR report highlighted SQL_ID 8b9f0d11: Executions: 4,000, Elapsed per Exec: 42.1s.

Inspecting historical execution plans via dbms_xplan.display_awr revealed a plan regression:

• Plan Hash Value A: Utilized an INDEX RANGE SCAN, completing in 20ms.
• Plan Hash Value B: Newly generated by CBO due to bind variable peeking on skewed histograms, resorting to TABLE ACCESS FULL across an 80GB table.

Remediation: Using DBMS_SPM (SQL Plan Management), Plan A was baselined and fixed (ACCEPTED = YES, FIXED = YES), instantly restoring the query to its 20ms baseline without application code restarts.

Engineering discipline means following catalog and performance counters rather than guessing.