Actallsum.sql

-- (c) Kyle Hailey 2007

column f_bars new_value v_bars
select 5 f_bars from dual;
column f_graph new_value v_graph
select 30 f_graph from dual;
column graph format a30
column host format a15
select
      ' # of cpus ' host,
      null asl,
      substr(lpad('1', &v_bars,'-') ||
      lpad('2', &v_bars,'-') ||
      lpad('3', &v_bars,'-') ||
      lpad('4', &v_bars,'-') ||
      lpad('5', &v_bars,'-') ||
      lpad('6', &v_bars,'-') ||
      lpad('7', &v_bars,'-') ||
      lpad('8', &v_bars,'-') , 0,  &v_graph)
      graph
from dual
union all
select
      db.host host,
      nvl(stats.avg_act_sess,-1) asl,
      stats.graph
from
 (
    select
         to_number(cpus) cpus,
         dbid,
         pts,
         round((wait/(wait+cpu)),2) pct_in_wait,
         round(((wait+cpu)/pts),2) avg_act_sess,
            substr(
              substr(
                  rpad('+',round((cpu*&v_bars)/pts),'+')  || /* cpu +  */
                  rpad('-',round((wait*&v_bars)/pts),'-') || /* wait - */
                  rpad(' ',cpus * &v_bars,' '),               /* blanks */
               0,((cpus * &v_bars - 1))) ||            /* cut at cpu line */
              cpus  ||                                    /* cpu line */
               substr(
                   rpad('+',round((cpu*&v_bars)/pts),'+') ||   /* above max cpu */
                   rpad('-',round((wait*&v_bars)/pts),'-')   ||
                   rpad(' ',cpus * &v_bars,' ')
                ,((cpus * &v_bars)-1))
            ,0,&v_graph) graph
    from (
         select
             ash.dbid dbid,
             nvl(substr(max(param.value),0,2),0)  cpus,
             nullif(max(sample_id)-min(sample_id),0) pts,
             sum(decode(ash.session_state,'ON CPU',1,0)) cpu,
             sum(decode(ash.session_state,'WAITING',1,0)) wait,
             count(*) act
         from sash_all ash,
              sash_params_all param
         where
           --   session_type!=81 and
                ash.sample_time >= sysdate - 60/(24*60)
            and ash.sample_time <= sysdate
            and param.dbid (+) = ash.dbid
            and param.name (+) = 'cpu_count'
         group by ash.dbid
        )
      order by cpus
   ) stats,
     dbids db
where
    stats.dbid (+) = db.dbid
order by asl desc

/