Active Session History (ASH) is a performance monitoring feature initially designed for Oracle databases to provide a detailed view of session activity over time, helping database administrators and developers troubleshoot performance issues. It was first introduced in Oracle's Enterprise Manager and is also used in various other tools, such as Amazon RDS Performance Insights, and third-party or open-source tools like pgsentinel and ASHViewer.
Here’s an overview of how ASH works and its role in performance monitoring:
What is ASH?
Active Session History captures and records the state of active database sessions every second (or at a configurable interval). These "snapshots" are stored in a rolling buffer within memory, typically for a fixed period (depending on the system's configuration).
Each snapshot contains data on:
The session's current activity (like SQL execution, waiting for I/O, locking, etc.)
Resource consumption (CPU, memory, I/O)
Session metadata (e.g., user, program, execution plan)
This approach allows database administrators to analyze active workload over time, identifying bottlenecks, SQL queries that are consuming resources, and other performance-related patterns.
How ASH Is Displayed:
Oracle Enterprise Manager (OEM) – Top Activity Screen
Top Activity: In OEM, the "Top Activity" screen uses ASH data to display real-time and historical session activity. It offers a graphical representation of the system’s workload, often categorized by wait events, SQL queries, or users.
ASH Report: Administrators can also generate ASH reports to drill down into specific periods of high activity and investigate the root cause of issues, such as slow-running queries or system hangs.
Amazon RDS Performance Insights
Performance Insights: Based on ASH concepts, Amazon RDS also provides a visual breakdown of active sessions over time. The interface shows top waits and SQL statements, allowing administrators to identify performance issues quickly, much like Oracle’s Top Activity feature.
Benefits of ASH:
Historical Performance Monitoring: Unlike real-time monitoring, which only captures what’s happening at a specific moment, ASH gives a time-based perspective, allowing DBAs to understand how performance issues develop over time.
Granular Insights: It enables deep visibility into specific SQL statements, wait events, or sessions causing bottlenecks.
Root Cause Analysis: ASH helps administrators not only detect where the problem is but also pinpoint why it’s happening, providing the necessary context (SQL, session info) to resolve issues efficiently.
Low Overhead: It samples session data at intervals, minimizing the performance overhead while still providing detailed information.
History and Development:
Kyle Hailey and John Beresniewicz designed the performance monitoring pages in Oracle Enterprise Manager that leveaged the ASH data collected by the database kernel in the early 2000s at Oracle to provide a more effective way to understand and visualize real-time performance data with lower overhead than full SQL tracing or auditing.
DB Optimizer: After working at Oracle, Kyle Hailey expanded on ASH concepts while at Embarcadero, developing the tool DB Optimizer, which brought ASH-like functionality to other database environments.
Amazon RDS: Hailey later continued this work at Amazon, helping to develop Performance Insights, which incorporated ASH-like features for cloud-based databases, giving RDS users a similarly powerful performance monitoring tool.
ASH in Open-Source Tools:
pgsentinel: This tool brings ASH-like functionality to PostgreSQL, capturing similar session-level data to monitor database activity in real-time.
ASHViewer: A third-party tool used to view ASH data, allowing DBAs to explore session history, analyze performance, and troubleshoot issues.
Conclusion:
ASH is a powerful and widely adopted performance monitoring technique for databases, providing insight into how sessions consume resources over time. Whether it’s through Oracle’s Enterprise Manager, Amazon RDS Performance Insights, or third-party tools, ASH data is invaluable for troubleshooting and optimizing database performance. Its continued adoption in various database environments speaks to its effectiveness in delivering high-value performance insights.
Comentários