Aveactcpu.sql

-- (c) Kyle Hailey 2007

column f_secs new_value v_secs
select 900 f_secs from dual;
column f_bars new_value v_bars
select 2 f_bars from dual;
column aveact format 999.99 
column graph format a30 

-- need a lag on sample_id to catch big jumps
--  round(count(*)/decode(max(sample_id)-min(sample_id),0,-1,max(sample_id)-min(sample_id)),2.2) aveac t
-- rpad('-',trunc(round(count(*)/decode(max(sample_id)-min(sample_id),0,-1,max(sample_id)-min(sample_id)),2.2)*10),'-') gr
aph
select
        start_time,
        total/samples aveact,
        cpu,
        waits,
        substr(rpad('+',round((cpu*&v_bars)/samples),'+') ||
        rpad('-',round((waits*&v_bars)/samples),'-')  ||
        rpad(' ',p.value * &v_bars,' '),0,(p.value * &v_bars)) ||
       '|'  ||
        substr(rpad('+',round((cpu*&v_bars)/samples),'+') ||
        rpad('-',round((waits*&v_bars)/samples),'-')  ||
        rpad(' ',p.value * &v_bars,' '),(p.value * &v_bars),10)
        graph,
        runs
from (
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,
       decode(max(sample_id)-min(sample_id),0,-1,max(sample_id)-min(sample_id)) samples
from
   sash
where sample_time > sysdate - 1
group by round(to_char(sample_time,'DDDSSSSS')/&v_secs)
order by round(to_char(sample_time,'DDDSSSSS')/&v_secs)
) ash,
  sash_params p
where p.name='cpu_count'
;