Actall.sql

From DB Optimizer
Jump to: navigation, search

-- (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
/