Aveact1.sql

-- (c) Kyle Hailey 2007

column f_secs new_value v_secs
select 900 f_secs from dual;
column aveact format 999.99

-- need a lag on sample_id to catch big jumps
select
      to_char(min(sample_time),'MON DD HH24:MI')         start_time ,
      to_char(max(sample_time),'MON DD HH24:MI')         end_time ,
      --round(to_char(sample_time,'DDDSSSSS')/&v_secs) id,
      max(sample_id) - min(sample_id)               runs,
      sum(decode(session_state,'ON CPU' ,1,0))    cpu,
      sum(decode(session_state,'WAITING',1,0))    waits,
      count(*) total,
      round(count(*)/decode(max(sample_id)-min(sample_id),0,-1,max(sample_id)-min(sample_id)),2.2) aveact
from
  sash
here sample_time > sysdate - 1
group by round(to_char(sample_time,'DDDSSSSS')/&v_secs)
order by round(to_char(sample_time,'DDDSSSSS')/&v_secs)