Active Sessions History (ASH) Basics

The active session history (ASH) sampler performs the sampling. ASH samples the current state of all active sessions. The database collects this data into memory, where you can access it with a V$ view.

Using ASH enables you to examine and perform detailed analysis on both current data in the V$ACTIVE_SESSION_HISTORY view and historical data in the DBA_HIST_ACTIVE_SESS_HISTORY view, often avoiding the need to replay the workload to gather additional performance tracing information.

ASH also contains execution plan information for each captured SQL statement. You can use this information to identify which part of SQL execution contributed most to the SQL elapsed time. The data present in ASH can be rolled up on various dimensions that it captures, including the following:

■ SQL identifier of SQL statement

■ SQL plan identifier and hash value of the SQL plan used to execute the SQL statement

■ SQL execution plan information

■ Object number, file number, and block number

■ Wait event identifier and parameters

■ Session identifier and session serial number

■ Module and action name

■ Client identifier of the session

■ Service hash identifier

■ Consumer group identifier

You can gather ASH information over a specified duration into a report.

Generating an ASH Report

OEM is one great tool to generate the ASH report without any extra hassles. Below we have documented the manual way of generating ASH. The ashrpt.sql SQL script generates an HTML or text report that displays ASH information for a specified duration.

When it prompts you for begin time you can give an actual date, and time to the minute and second, or if you wanted to start from a begin time 20 minutes ago, just type -20 (yes, negative 20). And for duration give the number of minutes you want the report to cover, you can pick a couple of minutes before an incident and couple of minutes after the incident. Usually not try to go beyond 10-15 mins duration.

@$ORACLE_HOME/rdbms/admin/ashrpt.sql
Enter value for report_type: text
Specify the begin time in minutes before the system date:
Enter value for begin_time: -10
Enter the duration in minutes that the report for which you want to capture ASH information from the begin time.
Enter value for duration:
Enter a report name, or accept the default report name:
Enter value for report_name:
Using the report name ashrpt_1_0310_0131.txt

Generating an ASH Report on a Specific Database Instance

The ashrpti.sql SQL script generates an HTML or text report that displays ASH information for a specified duration for a specified database and instance.

Generating an Oracle RAC ASH Report

The ashrpti.sql SQL script generates an HTML or text report that displays ASH information for a specified duration for specified databases and instances in an Oracle RAC environment.

Once the report is generated, look for the below:

1. Average Active Sessions: the more this statistics the more your DB was busy. So usually if you see this in the several tens or hundreds then you have a problem!

2. Top User Events: this gives you an idea as to what the most waited on event was within the time span you ran the report for. The % Event gives you an idea as to how much a particular wait event contributed to the overall wait.

3. Top SQL Command Types: this will give you an idea of what operation was being run the most, usually UPDATE/DELETES etc may show you locks etc. So pay attention.

4. Top SQL with TOP Events: this is the main section that shows you the most expensive SQL for the time period in question along with their SQL ID and the database wait event that contributed to their latency.

There are other details, but these 4 above will give you a very quick idea on what slowed the database and these ASH reports are much less verbose than the AWR reports and more importantly, ASH is much more granular.

Once you have identified the SQL ID, you can delve deeper to look at the plan and maybe craft a solution to the problematic SQL.