In the realm of database performance tuning, understanding what’s happening inside your system in real time is crucial. One of the most powerful tools for achieving this is Active Session History (ASH). This Oracle Database feature provides deep insights into session activity, enabling administrators to identify performance bottlenecks, diagnose problems, and optimize workloads with precision.
In this article, we explore everything you need to know about Active Session History, its architecture, benefits, implementation, and practical techniques to make the most out of this vital Oracle diagnostic tool.
What Is Active Session History (ASH)?
Active Session History (ASH) is an integral part of Oracle’s Automatic Workload Repository (AWR) architecture. It captures detailed information about active database sessions every second, storing them in memory for later analysis.
An active session is defined as any session that is currently using CPU or is waiting for a resource, such as I/O, locks, or latches. By continuously sampling this data, ASH provides a rolling snapshot of what’s happening in the system, allowing administrators to pinpoint performance issues without extensive overhead or tracing.
The Purpose and Importance of Active Session History
ASH plays a crucial role in Oracle performance diagnostics by providing real-time visibility into database workloads. Traditional monitoring tools often show aggregated data, but ASH delivers fine-grained, session-level metrics that help identify the root cause of performance issues quickly.
Some key benefits of ASH include:
-
Proactive Performance Analysis – Detect bottlenecks before they impact end users.
-
Detailed Wait Event Tracking – Identify what sessions are waiting for and why.
-
Comprehensive SQL Monitoring – Correlate SQL statements with session performance.
-
Historical Trend Analysis – Compare performance across time periods.
-
Lightweight Data Collection – Minimal performance impact on the database.
How ASH Works in Oracle Database
When enabled, Oracle’s MMON (Manageability Monitor Process) continuously samples session activity every second. Each sample records a variety of details about sessions currently active in the database.
This data is stored in memory within the V$ACTIVE_SESSION_HISTORY view and periodically flushed into DBA_HIST_ACTIVE_SESS_HISTORY, a persistent table in the AWR repository.
Key data captured by ASH includes:
-
Session ID (SID) and Serial#
-
User and Schema Information
-
SQL ID and Execution Plan Hash Value
-
Wait Events and Wait Class
-
Blocking Session Information
-
Module and Action Names
-
Object IDs for Active Objects
-
Sample Time and CPU Usage Metrics
This continuous sampling provides a time-based performance view, making it easier to understand what’s consuming system resources at any given moment.
Architecture of Active Session History
ASH is built upon Oracle’s Automatic Database Diagnostic Monitor (ADDM) and AWR framework.
The flow of ASH data is as follows:
-
Sampling Process – Oracle collects session data every second.
-
Memory Storage – Data is temporarily stored in the V$ACTIVE_SESSION_HISTORY view (SGA memory).
-
Persistence – A portion of the ASH samples is transferred to DBA_HIST_ACTIVE_SESS_HISTORY during AWR snapshots.
-
Analysis Tools – Administrators can query ASH data using SQL views or GUI tools like Oracle Enterprise Manager (OEM) and Automatic Database Diagnostic Monitor (ADDM).
This architecture ensures efficient and continuous tracking of performance activity with minimal system impact.
Key Views and Tables in Active Session History
To analyze ASH data, Oracle provides two primary sources:
1. V$ACTIVE_SESSION_HISTORY
This dynamic performance view contains data currently in memory. It provides real-time insights into session activity within the last few minutes (depending on system load and memory size).
Example query:
2. DBA_HIST_ACTIVE_SESS_HISTORY
This persistent AWR view stores historical ASH samples that are written to disk during AWR snapshots. It enables long-term performance analysis.
Example query:
Analyzing Performance with ASH
Using ASH effectively involves querying and correlating data to uncover performance bottlenecks. Below are some common use cases:
1. Identifying Top Wait Events
Determine which events consume the most time:
2. Finding Problematic SQL Queries
Locate SQL statements causing heavy system load:
3. Pinpointing Resource Contention
Discover sessions blocked by others:
4. Identifying High-Load Programs or Modules
Find which applications or users contribute most to system activity:
Integration of ASH with Oracle Diagnostic Tools
ASH works hand-in-hand with other Oracle performance utilities, providing a foundation for advanced diagnostics:
-
ADDM (Automatic Database Diagnostic Monitor) uses ASH data to produce actionable tuning recommendations.
-
AWR (Automatic Workload Repository) relies on ASH for capturing workload statistics.
-
OEM (Oracle Enterprise Manager) visualizes ASH data through interactive charts and reports, simplifying performance analysis.
These integrations make ASH one of the most indispensable features in the Oracle performance tuning ecosystem.
Performance Impact and Overhead
One of the major advantages of ASH is its low performance overhead. Because it samples rather than traces all sessions, it introduces minimal additional CPU or memory consumption.
However, administrators should be mindful of memory allocation in the Shared Global Area (SGA). In high-load systems, retaining too many ASH samples may require adjustments to ensure stability and performance balance.
Best Practices for Using Active Session History
To maximize the effectiveness of ASH, follow these best practices:
-
Regularly Review ASH Data – Schedule periodic reviews to detect long-term performance trends.
-
Correlate with AWR Reports – Combine ASH insights with AWR data for a complete performance picture.
-
Focus on Wait Events – Identify and resolve the most frequent or time-consuming waits.
-
Leverage ADDM Recommendations – Use ADDM’s automated analysis to prioritize optimization tasks.
-
Tune SQL Statements – Use SQL IDs from ASH data to analyze and optimize inefficient queries.
Advantages of Active Session History
-
Real-Time Insight: Immediate visibility into session performance.
-
Comprehensive Diagnostics: Captures both CPU usage and wait times.
-
Minimal Overhead: Efficient sampling reduces system impact.
-
Powerful Correlation: Links SQL, sessions, and wait events seamlessly.
-
Historical Analysis: Combines with AWR for trend evaluation.
These advantages make ASH an essential tool for database administrators seeking accurate, efficient, and actionable performance data.
Conclusion: Why Active Session History Matters
Active Session History is more than just a diagnostic feature it’s a window into the heartbeat of your Oracle Database. By continuously capturing session-level data, ASH enables administrators to identify performance bottlenecks, analyze workload patterns, and enhance system stability with precision.
In today’s data-driven environments, where efficiency and uptime are paramount, mastering ASH can mean the difference between reactive troubleshooting and proactive performance management.
For organizations running mission-critical databases, leveraging Active Session History is not optional it’s essential.
