Statspack : Average Active Sessions

Dans la doc. oracle, l'Average Active Sessions est défini par :

AAS = DB time / elapsed time 

D'après la requête de l'article précédent définissant le DB Time nous pouvons calculer l'AAS ( pour version 10g ou supérieure) :

select
snap_id
,to_char(start_snap_time,'DD/MM/YYYY HH24:MI:SS') as start_snap_time
,to_char(end_snap_time,'DD/MM/YYYY HH24:MI:SS') as end_snap_time
,'AAS'
,trunc(value/elapsed_time,2)  as AAS
from (
select snap_id,start_snap_time,end_snap_time,trunc((end_snap_time-start_snap_time)*24*60*60) elapsed_time,stat_name,value
from (
select
     snap.snap_id
     ,snap.startup_time  as startup_time
     ,case
          when snap.startup_time - lag(snap.startup_time,1) over (partition by e.stat_id,e.dbid,snap.instance_number,snap.startup_time order by snap.snap_id ) = 0
          then lag(snap.snap_time) over (order by snap.dbid, snap.instance_number, snap.snap_id)
          when (snap.startup_time > lag(snap.startup_time,1) over (partition by e.stat_id,e.dbid,snap.instance_number,snap.startup_time order by snap.snap_id )) or
          (lag(snap.startup_time,1) over (partition by e.stat_id,e.dbid,snap.instance_number,snap.startup_time order by snap.snap_id ) is null)
          then snap.startup_time
          end start_snap_time
      , snap.snap_time AS end_snap_time
     ,sn.stat_name
     ,case
          when snap.startup_time - lag(snap.startup_time,1) over (partition by e.stat_id,e.dbid,snap.instance_number,snap.startup_time order by snap.snap_id ) = 0
           then round(((e.value - lag(e.value,1) over ( partition by e.stat_id,e.dbid,snap.instance_number,snap.startup_time order by snap.snap_id)))/1000000,1)
          when snap.startup_time > lag(snap.startup_time,1) over (partition by e.stat_id,e.dbid,snap.instance_number,snap.startup_time order by snap.snap_id )
           then round(e.value/1000000,1)
          else round(e.value/1000000,1)
     end value
from stats$sys_time_model e
     , stats$time_model_statname sn
     , stats$snapshot snap
 where
  snap.snap_id = e.snap_id
  and snap.dbid = e.dbid
  and snap.instance_number = e.instance_number
  and e.stat_id                = sn.stat_id
  -- and snap.snap_time >= TO_DATE('&start_date','DD/MM/YYYY HH24:MI')
  -- and snap.snap_time <= TO_DATE('&end_date','DD/MM/YYYY HH24:MI')
  and sn.stat_name             = 'DB time'
  -- and snap.snap_id between 81 and 82
  and snap.snap_time >= sysdate - &NB_JOURS
  and snap.snap_time <= sysdate
  order by snap.snap_id
  ) )

Pour créer un graphique sous SQL Developper ( cf. article précédent), nous devons avoir uniquement 3 colonnes.
La requête est donc modifiée de la façon suivante :

select 
to_char(end_snap_time,'DD/MM/YYYY HH24:MI:SS') as end_snap_time
,'AAS'
,trunc(value/elapsed_time,2)  as AAS
from (
...

Les propriétés de l'axe Y ont été modifiées pour notre exemple :

Nous obtenons le graphique :