Category Archives: Oracle Active Session History (ASH)

This category regroups all scripts based on the v$active_session_history view.
These scripts will help you to diagnose performance problems and easily identify bottlenecks.
This view helps you calculate the Average Active Session metric which is a precise way to compare workload during diffrent periods.

Top 5 wait events from v$active_session_history

This query returns the top 5 wait events for the last hour from the v$active_session_history view.

Be careful, this view is part of the diagnostic pack, you should not query this view if you not licensed for it.

Top 5 wait events from v$active_session_history

This is obviously an approximation, because v$active_session_history contains only 1 second samples, and who knows what happens during each second sample. If you compare the time given by this query with information from v$system_event it will not exactly match but you should be close if you choose a sufficiently long period.

SQL Activity for the last hour

This script can be used to show the top 10 SQL activity for the last hour.
It uses the v$active_session_history view to search top SQL by resource consumption.

Top 10 SQL Activity

Here is the result you can obtain:

SQL activity

and the active sessions history graph for the same period:

instance activity last hour

Average Active Sessions (AAS)

The Average Active Sessions (AAS) metric is a very good indicator of the database activity.
This metric represents the number of sessions, either working or waiting for a resource at a specific point in time.
Idle sessions are not included in the calculation of this metric.
To calculate AAS, we need another metric called “DB Time” which represents the total time spent in the database by sessions, either working or waiting (The real work done in the database).
This DB time is divided by the clock elapsed time to obtain the Average Active Sessions.
The following script will calculate the Average Active Sessions from v$active_session_history view.

Be careful, this view is part of the diagnostic pack, you should not query this view if you don’t have license for it.

Average Active Sessions from v$active_session_history

You can restrict period of time analyzed by filtering with the sample_time column. Here is AAS from the last hour :

This metric can be correlated with the Graph from v$active_session_history