In today’s always-on enterprise systems, database performance issues can directly impact business operations, user experience, and revenue. Oracle databases are designed with powerful built-in diagnostic tools that help DBAs quickly identify, analyze, and resolve performance bottlenecks. Two of the most important tools in Oracle’s performance diagnostics arsenal are Active Session History (ASH) and Automatic Workload Repository (AWR) . ASH and AWR work together to provide both real-time and historical insights into database activity. While ASH focuses on what is happening right now at a very granular level, AWR provides a summarized, long-term view of database workload and performance trends.

A performance bottleneck occurs when a database resource becomes overloaded and limits overall system performance. Common bottlenecks include: - CPU saturation - I/O latency (disk or ASM) - Memory pressure (SGA or PGA) - Locking and contention - Inefficient SQL execution plans Oracle continuously collects performance statistics in the background so that DBAs can analyze these issues after they occur—this is where ASH and AWR come into play.

Active Session History (ASH) is a lightweight, in-memory sampling mechanism that captures information about active database sessions every second.

- Samples active sessions only (not idle sessions) - Captured approximately once per second - Stored in memory in the SGA - Provides near real-time performance visibility ASH answers the question: > “What was my database waiting on at a specific point in time?” “What was my database waiting on at a specific point in time?”

Each ASH sample records details such as: - Session ID and Serial# - SQL ID and SQL Plan Hash Value - Wait event and wait class - Blocking session information - Object ID (table, index, etc.) - CPU or wait state This data allows DBAs to drill down to the exact sessions and SQL statements responsible for performance problems.

ASH is especially useful for diagnosing short-lived or intermittent issues that traditional reports might miss.

- Identifying top wait events in real time - Detecting blocking sessions and lock contention - Analyzing CPU spikes - Troubleshooting sudden performance slowdowns - Pinpointing problematic SQL statements

If users complain that the application was slow for 5 minutes, ASH can show: - Which sessions were active - What they were waiting on (CPU, I/O, locks) - Which SQL statements were running during that time

Automatic Workload Repository (AWR) is a built-in Oracle repository that stores historical performance data over time.

- Takes snapshots by default every 60 minutes - Retains data for 8 days (configurable) - Stores data persistently in the SYSAUX tablespace - Aggregates and summarizes performance statistics AWR answers the question: > “How has my database been performing over time?” “How has my database been performing over time?”

AWR snapshots include a wide range of performance metrics, such as: - Top SQL statements by CPU, I/O, and elapsed time - Wait events and wait classes - System resource usage (CPU, memory, I/O) - Instance efficiency percentages - Load profile and throughput - Segment-level statistics This historical data is invaluable for trend analysis and capacity planning.

The most common way to analyze AWR data is through an AWR report , which compares two snapshots and highlights performance differences.

1. Load Profile Shows per-second activity such as: - DB Time - Logical reads - Physical reads - Executes 2. Top Wait Events Identifies where the database spent most of its time: - CPU - User I/O - System I/O - Concurrency 3. SQL Statistics Lists top SQL statements by: - Elapsed time - CPU time - Buffer gets - Disk reads 4. Instance Efficiency Helps assess overall database health and tuning effectiveness.

ASH and AWR are not competing tools—they complement each other.

- ASH data is periodically flushed into AWR - AWR uses ASH samples to generate ASH reports - ASH reports provide detailed session-level insights for a specific time range

An ASH Report is generated from AWR data but focuses on session activity rather than aggregated statistics.

- When performance issues occur for a short duration - When you need session-level visibility - When identifying blocking chains or hot objects ASH reports bridge the gap between real-time ASH views and high-level AWR summaries.

It’s important to note that: - ASH and AWR are part of the Oracle Diagnostics Pack - Using them requires a valid license in production environments - Always verify compliance before enabling or using these features

- Capture AWR snapshots during known performance issues - Compare good vs bad performance periods - Focus on wait classes before tuning SQL - Validate findings with execution plans and statistics - Avoid over-tuning based on a single report