Aveact2.sql

/*

  PROBLEMS: if the cpu_count doesn't exist for the host this
            script will have no output
            running ashpack.sh on the target should take care of this

*/

column f_days new_value v_days
select &days f_days from dual;
column f_secs new_value v_secs
select 900 f_secs from dual;
--select &seconds f_secs from dual;
column f_bars new_value v_bars
select 5 f_bars from dual;
column aveact format 999.99
column graph format a30

select
       to_char(to_date(tday||' '||tmod*&v_secs,'YYMMDD SSSSS'),'DD HH24:MI:SS') tm,
       samples,
       -- total,
       total/samples aveact,
       --cpu,
       --waits,
       substr(substr(rpad('+',round((cpu*&v_bars)/samples),'+') ||
       rpad('-',round((waits*&v_bars)/samples),'-')  ||
       rpad(' ',p.value * &v_bars,' '),0,(p.value * &v_bars)) ||
       p.value  ||
       substr(rpad('+',round((cpu*&v_bars)/samples),'+') ||
       rpad('-',round((waits*&v_bars)/samples),'-')  ||
       rpad(' ',p.value * &v_bars,' '),(p.value * &v_bars),10) ,0,30)
       graph
from (
  select
      to_char(sample_time,'YYMMDD')                   tday
    , trunc(to_char(sample_time,'SSSSS')/&v_secs) tmod
    , sum(decode(session_state,'ON CPU',1,decode(session_type,81,0,1)))  total
    , (max(sample_id) - min(sample_id) + 1 )      samples
    , sum(decode(session_state,'ON CPU' ,1,0))    cpu
    , sum(decode(session_state,'WAITING',1,0))-sum(decode(session_type,81,1,0))    waits
  from
     sash
  where sample_time > sysdate - &v_days
  group by  trunc(to_char(sample_time,'SSSSS')/&v_secs),
            to_char(sample_time,'YYMMDD')
   order by
            to_char(sample_time,'YYMMDD'),
            trunc(to_char(sample_time,'SSSSS')/&v_secs)
) ash,
 v$ash_params p
where p.name='cpu_count'
order by to_date(tday||' '||tmod*&v_secs,'YYMMDD SSSSS')

/