-- (c) Kyle Hailey 2007
--
-- activity on all targets for last 15 minutes
--

break on host
select  db.host, nvl(n.name,'CPU'), act.aas
from  (
   SELECT
        distinct ash.dbid, event#,
        round(
          count(event#)
           OVER (partition by dbid,event# ORDER BY event# ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) /
          nullif(
            LAST_VALUE(sample_id)
             OVER (partition by dbid ORDER BY sample_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
               -
            FIRST_VALUE(sample_id)
             OVER (partition by dbid ORDER BY sample_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
          ,0)
         , 2.2) aas
    from
        ( select dbid, sample_id, sample_time, decode(session_state,'ON CPU',-1,event#) event# from sash_all
           where  session_type!=81)   ash
    where
        sample_time >= sysdate - 15/(24*60)
    and sample_time <= sysdate
   )  act,
   SASH_EVENT_NAMES_ALL n,
   dbids db
where
    act.event# = n.event#  (+)
and act.dbid    = n.dbid (+)
and db.dbid = act.dbid
order by db.dbid, act.aas
/