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

select * from (
	select
		 WAIT_CLASS ,
		 EVENT,
		 count(sample_time) as EST_SECS_IN_WAIT
	from v$active_session_history
	where sample_time between sysdate - interval '1' hour and sysdate
	group by WAIT_CLASS,EVENT
	order by count(sample_time) desc
	)
where rownum <6

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

SELECT trunc(sample_time,'MI'),
       sql_id,
       count(sql_id) as TOTAL
FROM v$active_session_history
WHERE sample_time between sysdate - interval '1' hour and sysdate
AND sql_id in (select sql_id from (
 select
     SQL_ID ,
     sum(decode(session_state,'WAITING',1,1))  as TOTAL_ACTIVITY
from v$active_session_history
WHERE sample_time between sysdate - interval '1' hour and sysdate
group by sql_id
order by sum(decode(session_state,'WAITING',1,1))   desc)
where rownum < 11)
group by trunc(sample_time,'MI'),sql_id 
order by trunc(sample_time,'MI') desc

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

select round((count(ash.sample_id) / ((CAST(end_time.sample_time AS DATE) - CAST(start_time.sample_time AS DATE))*24*60*60)),2) as AAS
from
	(select min(sample_time) sample_time 
	from  v$active_session_history ash 
	) start_time,
	(select max(sample_time) sample_time
	from  v$active_session_history 
	) end_time,
	v$active_session_history ash
where ash.sample_time between start_time.sample_time and end_time.sample_time
group by end_time.sample_time,start_time.sample_time;

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

select round((count(ash.sample_id) / ((CAST(end_time.sample_time AS DATE) - CAST(start_time.sample_time AS DATE))*24*60*60)),2) as AAS
from
	(select min(sample_time) sample_time 
	from  v$active_session_history ash 
	where sample_time between sysdate-1/24 and sysdate) start_time,
	(select max(sample_time) sample_time
	from  v$active_session_history 
	where sample_time between sysdate-1/24 and sysdate) end_time,
	v$active_session_history ash
where ash.sample_time between start_time.sample_time and end_time.sample_time
group by end_time.sample_time,start_time.sample_time;

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