- 17 Dec, 2025
- 0 Comments
- 5 Mins Read
ASH & AWR: How Oracle Diagnoses Performance Bottlenecks
Introduction
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.
Understanding Performance Bottlenecks in Oracle
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.
What is Active Session History (ASH)?
Active Session History (ASH) is a lightweight, in-memory sampling mechanism that captures information about active database sessions every second.
Key Characteristics of ASH
- 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 Information Does ASH Capture?
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.
How ASH Helps Diagnose Performance Issues
ASH is especially useful for diagnosing short-lived or intermittent issues that traditional reports might miss.
Common Use Cases for ASH
- Identifying top wait events in real time
- Detecting blocking sessions and lock contention
- Analyzing CPU spikes
- Troubleshooting sudden performance slowdowns
- Pinpointing problematic SQL statements
Example Scenario
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
What is Automatic Workload Repository (AWR)?
Automatic Workload Repository (AWR) is a built-in Oracle repository that stores historical performance data over time.
Key Characteristics of AWR
- 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?”
What Data Does AWR Collect?
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.
AWR Reports: The DBA’s Go-To Tool
The most common way to analyze AWR data is through an AWR report, which compares two snapshots and highlights performance differences.
Key Sections of an AWR Report
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.
How ASH and AWR Work Together
ASH and AWR are not competing tools—they complement each other.
| Feature | ASH | AWR |
|---|---|---|
| Data granularity | Very detailed (per second) | Aggregated (per snapshot) |
| Data storage | Memory (SGA) | Disk (SYSAUX) |
| Best for | Short-term issues | Long-term trends |
| Real-time analysis | Yes | No |
Relationship Between ASH and AWR
- 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
ASH Reports
An ASH Report is generated from AWR data but focuses on session activity rather than aggregated statistics.
When to Use ASH Reports
- 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.
Licensing Considerations
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
Best Practices for Using ASH and AWR
- 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
Conclusion
ASH and AWR are two of the most powerful performance diagnostic tools available to Oracle DBAs. ASH provides deep, second-by-second visibility into active sessions, while AWR delivers a long-term, historical view of database performance.
By understanding how to interpret ASH samples, AWR snapshots, and their reports, DBAs can quickly pinpoint performance bottlenecks, identify root causes, and implement effective tuning strategies.
Mastering ASH and AWR is not just a technical skill—it’s a critical competency for any Oracle DBA responsible for maintaining high-performing, reliable databases.
If you’re preparing for Oracle DBA interviews or managing production databases, a solid understanding of ASH and AWR will set you apart as a performance troubleshooting expert. 🚀
Explore more with Learnomate Technologies!
Want to see how we teach?
Head over to our YouTube channel for insights, tutorials, and tech breakdowns:
www.youtube.com/@learnomate
To know more about our courses, offerings, and team:
Visit our official website: www.learnomate.org
Interested in mastering Oracle Database Administration?
Check out our comprehensive Oracle DBA Training program here: https://learnomate.org/oracle-dba-training/
Want to explore more tech topics?
Check out our detailed blog posts here: https://learnomate.org/blogs/
And hey, I’d love to stay connected with you personally!
Let’s connect on LinkedIn: Ankush Thavali
Happy learning!
Ankush😎
