Extracting Average Active Sessions from ASH

Average Active Sessions, or AAS is one of the most important metrics to look at from a database performance perspective.  There are many good resources that talk about AAS metrics, including:
You can obtain AAS data from the main Enterprise Manager performance page, as well as by running an ASH report.  But you can also get this data from AWR or ASH data and analyze the data yourself.
In the post, I’ll show you a query for measuring AAS using V$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY on an 11gR2 database.
The Basics about AAS
In general, the following is true:
– If AAS < 1, your database is not “blocked”
– If AAS =~0, your database is relatively idle
– If AAS < # CPUs, you’ve got CPUs available and the database is *probably* not blocked
– If AAS > #CPUs, you could have a performance problem
– If AAS >> #CPUs, you’ve got a bottlneck
Measuing AAS from V$ACTIVE_SESSION_HISTORY
To gather AAS from V$ACTIVE_SESSION_HISTORY:
– Decide what your windowing interval needs to be (i.e., decide what time ranges you’d like to examine
– Sum the number of samples from V$ACTIVE_SESSION_HISTORY per time internal
– Divide the number of samples the sampling interval
Below is a script that you can use to accomplish this:
col samplestart format a30 head ‘Begin Time’
col sampleend format a30 head ‘End Time’
col aas format 999.99 head ‘AAS’
col aas_cpu format 999.99 head ‘AAS per |CPU’
COLUMN bt NEW_VALUE _bt NOPRINT
COLUMN et NEW_VALUE _et NOPRINT
select min(cast(sample_time as date)) bt,
sysdate et
from v$active_session_history;
COLUMN ncpus NEW_VALUE _ncpus NOPRINT
select value ncpus from v$parameter where name=’cpu_count’;
with xtimes (xdate) as
(select to_date(‘&_bt’) xdate
from dual
union all
select xdate+(&&interval_mins/1440)
from xtimes
where xdate+(&&interval_mins/1440) < sysdate)
select to_char(s1.xdate,’DD-MON-RR HH24:MI:SS’) samplestart,
to_char(s1.xdate+(&&interval_mins/1440),’DD-MON-RR HH24:MI:SS’) sampleend,
count(s2.sample_id)/(60*&&interval_mins) aas,
count(s2.sample_id)/(60*&&interval_mins)/&_ncpus aas_cpu
from xtimes s1,
     v$active_session_history s2
where s2.sample_time between s1.xdate and s1.xdate+(&&interval_mins/1440)
group by s1.xdate
order by s1.xdate
/
undefine interval_mins
You can modify this script and enter input criteria for start and end times, much like an ASH report, if you’d like.
Measuring AAS from DBA_HIST_ACTIVE_SESS_HISTORY
To gather AAS from DBA_HIST_ACTIVE_SESS_HISTORY
– Decide what your windowing interval needs to be (i.e., decide what time ranges you’d like to examine, such as “every 1 minute”, “every 10 minutes”, etc.)
– Sum the number of samples from DBA_HIST_ACTIVE_SESS_HISTORY per time interval and multiply by 10
– Divide the number of samples the sampling interval
Below is a script that you can use to accomplish this.  Be mindful that this does a lot of sorting, so your TEMP tablespaces need to be sized accordingly:
col samplestart format a30 head ‘Begin Time’
col sampleend format a30 head ‘End Time’
col aas format 999.99 head ‘AAS’
col aas_cpu format 999.99 head ‘AAS per |CPU’
COLUMN bt NEW_VALUE _bt NOPRINT
COLUMN et NEW_VALUE _et NOPRINT
select min(cast(hist.sample_time as date)) bt,
sysdate et
from dba_hist_active_sess_history hist,
v$database d,
v$instance i
where hist.dbid=d.dbid
and i.instance_number=hist.instance_number;
COLUMN ncpus NEW_VALUE _ncpus NOPRINT
select value ncpus from v$parameter where name=’cpu_count’;
with xtimes (xdate) as
(select to_date(‘&_bt’) xdate
from dual
union all
select xdate+(&&interval_mins/1440)
from xtimes
where xdate+(&&interval_mins/1440) < sysdate)
select to_char(s1.xdate,’DD-MON-RR HH24:MI:SS’) samplestart,
to_char(s1.xdate+(&&interval_mins/1440),’DD-MON-RR HH24:MI:SS’) sampleend,
10*(count(s2.sample_id)/(60*&&interval_mins)) aas,
10*(count(s2.sample_id)/(60*&&interval_mins)/&_ncpus) aas_cpu
from xtimes s1,
     dba_hist_active_sess_history s2
where s2.sample_time between s1.xdate and s1.xdate+(&&interval_mins/1440)
group by s1.xdate
order by s1.xdate
/
undefine interval_mins
You can modify this script and enter input criteria for start and end times, much like an ASH report, if you’d like.
Next Steps
Extract the data, plot in Excel, and compare against ASH reports and/or Enterprise Manager to validate results.