Average Active Sessions (AAS)

Cyrille Modiano
Latest posts by Cyrille Modiano (see all)

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

3 thoughts on “Average Active Sessions (AAS)

  1. Hi Cyrille, you blog is very informative and useful.
    I have a clarification on AAS.

    In a exsisting 11g two node RAC I could see the session is evenly distributes but the AAS is very different. I do not know is the RAC load balancing is dependent to AAS or session counts.

    Session count in instance 1 & 2
    SQL> select inst_id,count(*) from gv$session where username is not null group by inst_id;

    INST_ID COUNT(*)
    ———- ———-
    1 2463
    2 2474

    AAS Instance 1
    ——————-
    674.97

    AAS Intance 2
    ——————
    148.99

    1. Hi Arjun,

      Thanks you for reading my blog and for your kind comment.
      Is it always like that?
      did you take multiple samples at different time intervals?

      AAS is not involved in the load balancing process, I recommend you to read the following post about load balancing in RAC:
      http://oracleinaction.com/rac-load-balancing/

      Can you check the the result of the following query:

      set pages 100
      select INSTANCE_NUMBER,
      SUM(value),
      to_char(trunc(end_time,’HH’),’dd-mm-yyyy hh24:mi’) as sample_time
      from dba_hist_sysmetric_history
      where metric_name=’Average Active Sessions’
      and end_time between sysdate -1 and sysdate
      group by trunc(end_time,’HH’), INSTANCE_NUMBER
      order by 3,1;

      Cyrille

  2. Hi, how to execute this query in 9i? the view v$system_event don’t have the same columns.
    tanks

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.