Statspack : Average Active Sessions pour les versions antérieures à 10g

AAS = DB Time / Elapsed Time

Pour les versions antérieures à oracle 10g, le DB Time peut être calculé de la façon suivante ( Cf l'article de K. Hailey) :

En utilisant les tables Statspack stats$system_event et stats$sysstat (pour la version 9.2) :

SELECT snap_id,
  instance_number,
    to_char(start_snap_time,'DD/MM/YYYY_HH24:MI') start_snap_time,
    to_char(end_snap_time,'DD/MM/YYYY_HH24:MI') end_snap_time,
  TRUNC (dbtime / elapsed_time, 3) "AAS"
FROM
  (SELECT snap_id,
    instance_number,
    start_snap_time,
    end_snap_time,
    elapsed_time,
    SUM (VALUE) AS dbtime
  FROM
    (SELECT snap_id,
      instance_number,
    start_snap_time,
    end_snap_time,
      'Wait event' AS stat,
      TRUNC ( (end_snap_time - start_snap_time) * 24 * 60 * 60) elapsed_time,
      SUM (tw_delta) AS VALUE
    FROM
      (SELECT snap.snap_id,
        snap.instance_number,
        snap.startup_time,
        CASE
          WHEN snap.startup_time - LAG ( snap.startup_time, 1) OVER ( PARTITION BY a.dbid, snap.instance_number, snap.startup_time, a.event ORDER BY snap.snap_id) = 0
          THEN LAG ( snap.snap_time) OVER ( PARTITION BY a.dbid, snap.instance_number, snap.startup_time, a.event ORDER BY snap.snap_id)
          WHEN (snap.startup_time                                                                                                               > LAG ( snap.startup_time, 1) OVER ( PARTITION BY a.dbid, snap. instance_number, snap.startup_time, a.event ORDER BY snap.snap_id))
          OR (LAG ( snap.startup_time, 1) OVER ( PARTITION BY a.dbid, snap. instance_number, snap.startup_time, a.event ORDER BY snap.snap_id) IS NULL)
          THEN snap.startup_time
        END start_snap_time,
        snap.snap_time AS end_snap_time,
        a.event,
        a.time_waited_micro,
        CASE
          WHEN snap.startup_time      - LAG ( snap.startup_time, 1) OVER ( PARTITION BY a.dbid, snap.instance_number, snap.startup_time, a.event ORDER BY snap.snap_id) = 0
          THEN ( (a.time_waited_micro - LAG ( a.time_waited_micro, 1) OVER ( PARTITION BY a.dbid, snap. instance_number, snap.startup_time, a.event ORDER BY snap.snap_id))) / 1000000
          WHEN snap.startup_time                                                                                                               > LAG ( snap.startup_time, 1) OVER ( PARTITION BY a.dbid, snap. instance_number, snap.startup_time, a.event ORDER BY snap.snap_id)
          OR LAG ( snap.startup_time, 1) OVER ( PARTITION BY a.dbid, snap. instance_number, snap.startup_time, a.event ORDER BY snap.snap_id) IS NULL
          THEN a.time_waited_micro / 1000000
        END tw_delta
      FROM perfstat.stats$system_event a,
        perfstat.stats$snapshot snap
      WHERE a.snap_id  = snap.snap_id(+)
      AND a.event NOT IN
        (SELECT event FROM stats$idle_event
        )
      AND snap.snap_time >= SYSDATE - &NB_JOURS
      AND snap.snap_time   <= SYSDATE
      AND a.instance_number = snap.instance_number
      )
    GROUP BY snap_id,
      instance_number,
start_snap_time,
end_snap_time,
      'Wait event',
      TRUNC ( (end_snap_time - start_snap_time) * 24 * 60 * 60)
    UNION ALL
    SELECT snap_id,
      instance_number,
    start_snap_time,
    end_snap_time,
      'CPU Time' AS stat,
      TRUNC ( (end_snap_time - start_snap_time) * 24 * 60 * 60) elapsed_time,
      SUM (cpu_delta) AS VALUE
    FROM
      (SELECT snap.snap_id,
        snap.instance_number,
        snap.startup_time,
        CASE
          WHEN snap.startup_time - LAG ( snap.startup_time, 1) OVER ( PARTITION BY a.dbid, snap.instance_number, snap.startup_time ORDER BY snap.snap_id) = 0
          THEN LAG ( snap.snap_time) OVER ( PARTITION BY a.dbid, snap.instance_number, snap.startup_time ORDER BY snap.snap_id)
          WHEN (snap.startup_time                                                                                                      > LAG ( snap.startup_time, 1) OVER ( PARTITION BY a.dbid, snap. instance_number, snap.startup_time ORDER BY snap.snap_id))
          OR (LAG ( snap.startup_time, 1) OVER ( PARTITION BY a.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,
        a.name         AS stat,
        NULL           AS time_waited_micro,
        CASE
          WHEN snap.startup_time - LAG ( snap.startup_time, 1) OVER ( PARTITION BY a.dbid, snap.instance_number, snap.startup_time ORDER BY snap.snap_id) = 0
          THEN ( (a.VALUE        - LAG ( a.VALUE, 1) OVER ( PARTITION BY a.dbid, snap. instance_number, snap.startup_time ORDER BY snap.snap_id))) / 100
          WHEN snap.startup_time                                                                                                      > LAG ( snap.startup_time, 1) OVER ( PARTITION BY a.dbid, snap. instance_number, snap.startup_time ORDER BY snap.snap_id)
          OR LAG ( snap.startup_time, 1) OVER ( PARTITION BY a.dbid, snap. instance_number, snap.startup_time ORDER BY snap.snap_id) IS NULL
          THEN a.VALUE / 100
        END cpu_delta
      FROM perfstat.stats$sysstat a,
        perfstat.stats$snapshot snap
      WHERE a.snap_id = snap.snap_id
      AND a.name      = 'CPU used by this session'
      AND snap.snap_time >= SYSDATE - &NB_JOURS
      AND snap.snap_time   <= SYSDATE
      AND a.instance_number = snap.instance_number
      )
    GROUP BY snap_id,
      instance_number,
start_snap_time,
end_snap_time,
      'CPU Time',
      TRUNC ( (end_snap_time - start_snap_time) * 24 * 60 * 60)
    )
  GROUP BY snap_id,
    instance_number,
    start_snap_time,
    end_snap_time,
    elapsed_time
  ORDER BY snap_id
  )
ORDER BY snap_id,start_snap_time,end_snap_time;

Pour les versions précédentes, la colonne time_waited_micro de la table stats$system_event n'existe pas. En version 8i la colonne est time_waited. Elle est alimentée uniquement si le paramètre timed_statistics est positionné à TRUE et les valeurs sont alors exprimées en centisecondes.