Generate Active Session History ASH Report in Oracle Database

calendar Oct 3, 2025 / · 6 min read · Oracle Database Performance Tuning ASH Reports Database Monitoring SQL Scripts Diagnostics AWR Session Analysis V$ACTIVE_SESSION_HISTORY  ·
Share on: linkedin copy

Generate Active Session History ASH Report in Oracle Database

The Active Session History (ASH) report is a powerful diagnostic tool that helps database administrators analyze performance issues by capturing detailed snapshots of active database sessions every second. This report provides granular insights into what database sessions are doing at specific points in time, making it essential for troubleshooting performance problems.

Purpose

ASH reports serve as a critical performance analysis tool that captures real-time database activity to identify bottlenecks, wait events, and resource contention issues. Unlike AWR reports that provide aggregated statistics over longer periods, ASH reports offer second-by-second details of active sessions, enabling administrators to pinpoint exact moments when performance degraded. The reports are particularly valuable for diagnosing issues in sessions that have already completed, as the data remains available in the ASH buffer for analysis.

Breakdown of Code

Query: Produce an Active Session History (ASH) report

Command:

1@?/rdbms/admin/ashrpt.sql

The @? symbol represents the Oracle home directory path variable, which automatically resolves to $ORACLE_HOME. The script is located in the rdbms/admin directory within the Oracle installation. When executed, this command launches an interactive script that guides administrators through generating either HTML or text-based ASH reports.

How the Script Works

The ashrpt.sql script operates by querying the V$ACTIVE_SESSION_HISTORY view, which contains samples of active sessions captured every second. An active session is defined as one that is either consuming CPU or waiting on non-idle events at the time of sampling. The script prompts users to specify the report format (HTML or text), time range, and duration for analysis.

Script Execution Steps

When running the script, administrators must provide several parameters through interactive prompts. First, choose between HTML or text report format by entering the desired report type. Second, specify the begin time in minutes before the current system time (negative values indicate past time). Third, define the duration in minutes to capture ASH information from the begin time. Finally, enter a custom report name or accept the default naming convention that includes timestamp information.

Key Points

ASH sampling occurs every second and captures only active sessions, making it lightweight on system resources while providing detailed diagnostic information. The sampled data includes session identifiers, SQL statements being executed, wait events, time waited, and resource consumption metrics. The ASH buffer uses a circular structure in the SGA, meaning older samples are overwritten as new activity occurs, with retention depending on database activity levels.

Report Variants

Oracle provides multiple ASH report scripts for different scenarios. The ashrpt.sql script generates reports for the local database instance only. The ashrpti.sql script allows specification of database ID and instance number, enabling reports on specific instances in RAC environments. For multi-instance analysis, the same ashrpti.sql script can generate consolidated reports across Oracle Real Application Clusters.

Data Sources

ASH reports utilize two primary data sources depending on the time range requested. Recent activity within approximately the last hour comes from the in-memory V$ACTIVE_SESSION_HISTORY view. Historical data beyond memory retention is retrieved from the DBA_HIST_ACTIVE_SESS_HISTORY table, which persists ASH samples to disk every 10 seconds. This dual-source approach enables analysis of both current and historical performance issues.

Insights and Benefits

ASH reports excel at identifying short-lived performance problems that may not appear in hourly AWR snapshots. The second-by-second granularity allows administrators to correlate specific SQL statements with wait events during problem periods. This level of detail proves invaluable when users report transient slowdowns that occurred at specific times.

Diagnostic Advantages

The always-available nature of ASH data eliminates the need to reproduce problems, a significant advantage over SQL trace methods. Administrators can analyze any time range without prior planning or trace activation. The multi-dimensional nature of ASH allows filtering by SQL ID, session, module, wait class, service, and other attributes. This flexibility enables targeted analysis of specific application components or user activities.

Performance Impact

ASH sampling has minimal performance overhead because it captures only active sessions rather than all database sessions. The one-second sampling interval provides sufficient statistical accuracy, with research showing that 30 ASH entries give 95% confidence in capturing events representing 10% or more of database time. Administrators should consider time ranges and target filters when generating reports to limit data volume and reduce report generation time.

Best Practices

For optimal analysis, focus ASH reports on specific time windows when performance issues occurred rather than extended periods. Use target filters to narrow analysis to specific SQL statements, sessions, or wait classes of interest. Combine AWR reports for trend analysis with ASH reports for detailed investigation of specific incidents. In RAC environments, generate individual instance reports to identify workload imbalances across cluster nodes.

Prerequisites

Generating ASH reports requires SELECT_CATALOG_ROLE privileges or SYSDBA access to execute the diagnostic scripts. The Diagnostic Pack license is required for accessing ASH functionality in Oracle Database. Active Session History must be enabled, which is the default configuration for Oracle Database 10g and higher versions. Sufficient retention in the ASH buffer and AWR repository ensures availability of historical data for analysis.

Use Cases

ASH reports are particularly effective for investigating sudden performance degradation reported by users at specific times. They help identify blocking sessions and lock contention issues by showing which sessions are waiting for resources. The reports assist in analyzing SQL statement performance during actual execution rather than relying solely on execution plan estimates. Database administrators use ASH data to validate whether tuning changes have resolved wait event issues.

The awrrpt.sql script generates Automatic Workload Repository reports that provide aggregated performance statistics over snapshot intervals. The addmrpt.sql script runs Automatic Database Diagnostic Monitor analysis with recommendations for resolving identified performance issues. The awrsqrpt.sql script focuses on specific SQL statement performance across AWR snapshots. These scripts complement ASH reports by providing different perspectives on database performance.

References

Posts in this series