-- (c) Kyle Hailey 2007

break on sample_time skip 1
compute sum of aas on sample_time
--clear breaks
--clear compute
column event format a35
column sample_time format a15
column aas format 999.99
select  * from (
           select
                 to_char(sample_time,'DD/MM/YY HH24:MI') sample_time,
                 event,
                 round( cnt /
                 nullif( fv -
                   min(lv) OVER ( ORDER BY secs range 1 preceding )
                 ,0)
                 , 2.2) aas , cnt
          from (
                SELECT
                  distinct event , secs  ,
                  max(sample_time)
                    OVER (partition by secs ORDER BY event
                    ROWS BETWEEN UNBOUNDED PRECEDING AND
                    UNBOUNDED FOLLOWING) sample_time,
                  count(event)
                    OVER (partition by event, secs ORDER BY event
                    ROWS BETWEEN UNBOUNDED PRECEDING AND
                    UNBOUNDED FOLLOWING) cnt,
                  LAST_VALUE(sample_id)
                    OVER ( partition by secs ORDER BY sample_id
                    ROWS BETWEEN UNBOUNDED PRECEDING AND
                    UNBOUNDED FOLLOWING) fv,
                  FIRST_VALUE(sample_id)
                    OVER ( partition by secs ORDER BY sample_id
                    ROWS BETWEEN UNBOUNDED PRECEDING AND
                    UNBOUNDED FOLLOWING ) lv
               from
                  ( select
                      sample_id,
                      sample_time,
                      trunc(to_char(sample_time,'SSSSS')/60) secs,
                      decode(session_state,'ON CPU','CPU',event) event
                      from v$active_session_history
                    where
                       sample_time >= sysdate - 60/(24*60)
                    ) ash
               order by   secs
) ) where aas > 0.01
/