Data … as usual

Active Average Session (AAS) is one of the first statistic you have to look when you analyze a performance issue.

In Oracle Standard Edition (or Enterprise Edition without Diagnostic pack), you cannot query V$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY views, nor execute ashrpt script.

If you have installed statspack and if you take snapshots, you can find Active Average Session between 2 snapshots.

AAS is calculated with the formula : DB Time / elapsed time. So, if you have recorded a db time of 300 seconds in 900 seconds (elapsed time between two snapshots), AAS is 0,33.

So it’s easy to calculate AAS between two snapshot, but the most important thing is to have AAS trending during a period.

To do that, I wrote a small PL/SQL function based on SYS_TIME_MODEL_DIF function available in STATSPACK package definition (in spcpkg.sql script).

create or replace function SYS_TIME_MODEL_DIF(i_name varchar2, bid number, eid number, db_ident number, inst_num number) RETURN number is

/* Returns the difference between statistics for the Time Model statistic
   name specified for the interval between the begin and end
   snapshot id's (bid, eid)

   In the case the data being queried is from a prior release
   which did not have the statistic requested, this function
   returns 0.
*/

beg_val_missing   boolean := false;
end_val_missing   boolean := false;
bval           number;
eval           number;

cursor SY (i_snap_id number) is
select value
  from stats$sys_time_model      stm
     , stats$time_model_statname tms
where stm.snap_id         = i_snap_id
   and stm.dbid            = db_ident
   and stm.instance_number = inst_num
   and stm.stat_id         = tms.stat_id
   and tms.stat_name       = i_name;

begin
   /* Get start value */
   open SY (bid); fetch SY into bval;
   if SY%notfound then
      beg_val_missing := true;
   end if; close SY;

   /* Get end value */
   open SY (eid); fetch SY into eval;
   if SY%notfound then
      end_val_missing := true;
   end if; close SY;

   if     beg_val_missing = true
      and end_val_missing = true      then

         /* this is likely a newer statitic which did not exist for this database version    */
        return 0;

   elsif     beg_val_missing = true
         and end_val_missing = false  then
         return -1;
   elsif     beg_val_missing = false
         and end_val_missing = true   then
         return -1;
   else

        /* Return difference */
        return eval - bval;

   end if;

end SYS_TIME_MODEL_DIF;
/

When this function has been created, you can call it to get Db time (or any value in the time model) between two snapshots (identified by their snapshot_id : bid (begin id) and eid (end id)) , for example :

SQL> select SYS_TIME_MODEL_DIF('DB time',791,792,1386309985,1) from dual;

SYS_TIME_MODEL_DIF('DBTIME',791,792,1386309985,1)
-------------------------------------------------
 10245630292

SQL> select SYS_TIME_MODEL_DIF('DB CPU',791,792,1386309985,1) from dual;

SYS_TIME_MODEL_DIF('DBCPU',791,792,1386309985,1)
------------------------------------------------
 3698356113

SQL> select SYS_TIME_MODEL_DIF('parse time elapsed',791,792,1386309985,1) from dual;

SYS_TIME_MODEL_DIF('PARSETIMEELAPSED',791,792,1386309985,1)
-----------------------------------------------------------
 211981805

Note that time is given in microseconds.

Next step, I wrote an anonymous PL/SQL script that executes this function accross all the snapshots taken between two snap_id and prints DB Time, DB CPU, and AAS in CSV format (you can adapt this script to adapt your output format):

declare
 -- Constants
 usec_to_sec number := 1000000;
 day_to_sec number := 86400;
 db_id number := &dbid;
 --
 dbtim number;
 dbcpu number;
 cursor SN_tim(sn_id number) is select snap_time from stats$snapshot where snap_id=sn_id;
 cursor snaps(sn1 number, sn2 number) is select snap_id from stats$snapshot where snap_id between sn1 and sn2 order by snap_id;
 b_time date;
 e_time date;
 bid number;
 eid number;
 cur_nf number;
begin
dbms_output.put_line('BEGIN_TIME;END_TIME;DBTIME;DBCPU;AAS');
open snaps(&start,&end);
loop
fetch snaps into bid;
exit when snaps%notfound;
 cur_nf := 0;
 fetch snaps into eid;

 open SN_tim(bid); fetch SN_tim into b_time;
 if SN_tim%notfound then
 cur_nf := 1;
 end if;
 close SN_tim;

 open SN_tim(eid); fetch SN_tim into e_time;
 if SN_tim%notfound then
 cur_nf := 1;
 end if;
 close SN_tim;

 dbtim := SYS_TIME_MODEL_DIF('DB time',bid,eid,db_id,1);
 dbcpu := SYS_TIME_MODEL_DIF('DB CPU',bid,eid,db_id,1);
 if ((dbtim>0 or dbcpu>0) and cur_nf = 0) then
 dbms_output.put_line(b_time||';'||e_time||';'||round(dbtim/usec_to_sec,2) ||';'||round(dbcpu/usec_to_sec,2) ||';'|| round((dbtim/usec_to_sec) / ((e_time-b_time)*day_to_sec),2) );
 else
 dbms_output.put_line(b_time||';'||e_time||';N/A;N/A' );
 end if;
end loop;
end;

Then you can graph it in Excel or Tableau (for example) and you will get the AAS trend: