Contents
Introduction
Oracle databases power some of the world’s most critical applications, yet performance issues can bring even the most robust systems to their knees. Active Session History (ASH) stands as one of Oracle’s most powerful diagnostic tools, offering database administrators and developers unprecedented visibility into database performance patterns and bottlenecks.
ASH captures detailed snapshots of active database sessions, creating a comprehensive timeline of database activity that helps identify performance problems before they impact users. This guide explores how to leverage Active Session History (ASH) effectively, from understanding its architecture to implementing practical queries that solve real-world performance challenges.
Understanding Active Session History Architecture and Components
Active Session History operates by sampling active database sessions every second, storing this information in a circular buffer within the System Global Area (SGA). This lightweight sampling approach captures critical performance data without significantly impacting database performance.
Core ASH Components
The ASH infrastructure consists of several key elements that work together to provide comprehensive session monitoring:
V$ACTIVE_SESSION_HISTORY View: This dynamic performance view provides real-time access to ASH data stored in memory. It contains detailed information about each sampled session, including SQL statements, wait events, and resource consumption.
DBA_HIST_ACTIVE_SESS_HISTORY: This view stores historical ASH data that has been flushed from memory to disk as part of the Automatic Workload Repository (AWR). This persistent storage enables long-term performance analysis and trend identification.
MMON Background Process: The Memory Monitor process manages ASH data collection and transfer. It ensures that ASH samples are consistently captured and that older data is properly archived to AWR.
Data Collection Methodology
ASH employs a sophisticated sampling strategy that balances data completeness with system overhead. The sampling occurs every second, but only captures sessions that are actively consuming CPU or waiting for resources. This approach ensures that ASH focuses on sessions that matter most for performance analysis.
Each ASH sample contains over 50 different attributes, including session identifiers, SQL execution details, wait event information, and resource consumption metrics. This rich dataset enables detailed analysis of database behavior patterns.
Benefits of Using ASH for Performance Tuning
ASH provides several distinct advantages over traditional performance monitoring approaches, making it an essential tool for database optimization efforts.
Real-Time Performance Visibility
Unlike static performance reports, ASH offers near real-time insights into database activity. This immediate visibility enables rapid identification of performance issues as they occur, rather than discovering problems after users have already been impacted.
Granular Session Analysis
ASH captures individual session details that aggregate statistics often obscure. This granular view helps identify specific problem sessions, SQL statements, or application components that contribute to performance degradation.
Historical Trend Analysis
The integration between ASH and AWR enables comprehensive historical analysis. Database administrators can identify performance trends, seasonal patterns, and long-term degradation that might not be apparent from current system metrics alone.
Wait Event Correlation
ASH excels at correlating wait events with specific SQL statements and sessions. This correlation capability helps pinpoint the root causes of performance bottlenecks rather than just identifying symptoms.
10 Useful Queries of Active Session History
These practical ASH queries address common performance tuning scenarios that database administrators encounter regularly.
1. Identify Top CPU-Consuming Sessions
SELECT session_id, session_serial#, user_id, program,
COUNT(*) AS cpu_samples
FROM v$active_session_history
WHERE sample_time >= SYSDATE - 1/24
AND session_state = 'ON CPU'
GROUP BY session_id, session_serial#, user_id, program
ORDER BY cpu_samples DESC;
This query identifies sessions consuming the most CPU resources during the past hour, helping prioritize optimization efforts.
2. Analyze Top Wait Events
SELECT event, COUNT(*) AS wait_count,
ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER(), 2) AS percentage
FROM v$active_session_history
WHERE sample_time >= SYSDATE - 1/24
AND event IS NOT NULL
GROUP BY event
ORDER BY wait_count DESC;
Understanding wait event distribution helps identify system bottlenecks and resource contention issues.
3. Find Long-Running SQL Statements
SELECT sql_id, COUNT(*) AS samples,
COUNT(DISTINCT session_id) AS sessions,
MIN(sample_time) AS first_seen,
MAX(sample_time) AS last_seen
FROM v$active_session_history
WHERE sample_time >= SYSDATE - 1/24
GROUP BY sql_id
HAVING COUNT(*) > 100
ORDER BY samples DESC;
This query identifies SQL statements that appear frequently in ASH samples, indicating potential performance problems.
4. Monitor I/O-Related Bottlenecks
SELECT event, COUNT(*) AS io_waits,
ROUND(AVG(time_waited), 2) AS avg_wait_time
FROM v$active_session_history
WHERE sample_time >= SYSDATE - 1/24
AND event LIKE '%I/O%' OR event LIKE '%read%' OR event LIKE '%write%'
GROUP BY event
ORDER BY io_waits DESC;
I/O-related waits often indicate storage performance issues or suboptimal SQL execution plans.
5. Analyze Session Activity by Hour
SELECT TO_CHAR(sample_time, 'YYYY-MM-DD HH24') AS hour,
COUNT(*) AS total_samples,
COUNT(CASE WHEN session_state = 'ON CPU' THEN 1 END) AS cpu_samples,
COUNT(CASE WHEN session_state = 'WAITING' THEN 1 END) AS wait_samples
FROM v$active_session_history
WHERE sample_time >= SYSDATE - 1
GROUP BY TO_CHAR(sample_time, 'YYYY-MM-DD HH24')
ORDER BY hour;
Hourly analysis helps identify peak usage periods and plan capacity accordingly.
6. Identify Blocking Sessions
SELECT blocking_session AS blocker,
COUNT(DISTINCT session_id) AS blocked_sessions,
COUNT(*) AS total_blocks
FROM v$active_session_history
WHERE sample_time >= SYSDATE - 1/24
AND blocking_session IS NOT NULL
GROUP BY blocking_session
ORDER BY total_blocks DESC;
Blocking analysis helps resolve lock contention issues that can severely impact application performance.
7. Monitor Temp Space Usage
SELECT session_id, session_serial#, sql_id,
MAX(temp_space_allocated) AS max_temp_mb
FROM v$active_session_history
WHERE sample_time >= SYSDATE - 1/24
AND temp_space_allocated > 0
GROUP BY session_id, session_serial#, sql_id
ORDER BY max_temp_mb DESC;
Temporary space monitoring helps identify SQL statements that consume excessive temporary storage.
8. Analyze PGA Memory Usage
SELECT sql_id, COUNT(*) AS samples,
ROUND(AVG(pga_allocated), 2) AS avg_pga_mb,
ROUND(MAX(pga_allocated), 2) AS max_pga_mb
FROM v$active_session_history
WHERE sample_time >= SYSDATE - 1/24
AND pga_allocated > 0
GROUP BY sql_id
HAVING AVG(pga_allocated) > 100
ORDER BY max_pga_mb DESC;
PGA memory analysis helps identify memory-intensive operations that might benefit from tuning.
9. Track Object-Level Activity
SELECT current_obj#, object_name, object_type,
COUNT(*) AS access_count
FROM v$active_session_history ash
JOIN dba_objects obj ON ash.current_obj# = obj.object_id
WHERE sample_time >= SYSDATE - 1/24
AND current_obj# > 0
GROUP BY current_obj#, object_name, object_type
ORDER BY access_count DESC;
Object-level analysis helps identify hot spots in database schema and optimize indexing strategies.
10. Monitor RAC Load Distribution
SELECT instance_number, COUNT(*) AS samples,
COUNT(CASE WHEN session_state = 'ON CPU' THEN 1 END) AS cpu_samples,
ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER(), 2) AS load_percentage
FROM v$active_session_history
WHERE sample_time >= SYSDATE - 1/24
GROUP BY instance_number
ORDER BY instance_number;
For RAC environments, this query helps ensure balanced load distribution across cluster nodes.
Why is Active Session History Important?
ASH addresses fundamental challenges in database performance management that traditional monitoring approaches struggle to solve effectively.
Proactive Problem Detection
ASH enables proactive identification of performance issues before they escalate into user-impacting problems. By continuously monitoring active sessions, database administrators can spot emerging bottlenecks and address them preemptively.
Root Cause Analysis
The detailed session-level data that ASH provides enables thorough root cause analysis. Rather than relying on aggregate statistics that might mask important details, ASH reveals the specific circumstances that contribute to performance problems.
Capacity Planning Support
Historical ASH data supports informed capacity planning decisions. By analyzing usage patterns and growth trends, organizations can make data-driven decisions about hardware upgrades and resource allocation.
Application Performance Optimization
ASH data helps development teams optimize application performance by identifying inefficient SQL statements, excessive resource consumption, and suboptimal database interaction patterns.
How to Interpret ASH Data Effectively
Effective ASH interpretation requires understanding both the technical details and the broader context of database operations.
Understanding Wait Events
Wait events represent the primary indicators of database bottlenecks. Each wait event type suggests different optimization approaches:
Buffer Busy Waits: Often indicate hot blocks or insufficient buffer cache sizing. Solutions might include improving SQL efficiency or increasing buffer cache size.
Log File Sync Waits: Suggest I/O bottlenecks in the redo log system. Optimization might involve faster storage or redo log configuration changes.
Enqueue Waits: Point to locking issues that might require application logic changes or different transaction patterns.
Correlating Multiple Metrics
Effective ASH analysis involves correlating multiple metrics to build a complete picture of database behavior. CPU usage should be analyzed alongside wait events, memory consumption should be considered with I/O patterns, and individual session behavior should be viewed in the context of overall system load.
Identifying Patterns and Trends
Identifying patterns and trends within ASH data requires a methodical approach. By analyzing historical data, you can pinpoint recurring issues, such as specific queries or routines that consistently cause high wait times. Trend analysis can also reveal gradual performance degradation, helping to address problems before they escalate. Visualizing the data through tools like dashboards or heatmaps can make it easier to spot anomalies and correlations, enabling more effective troubleshooting.
