aveactn.sql

Def v_secs=3600 --  bucket size
Def v_days=1 --  total time analyze
Def v_bars=5 -- size of one AAS in characters

col aveact format 999.99
col graph format a30
col fpct format 9.99
col spct format 9.99
col tpct format 9.99
col aas1 format 9.99
col aas2 format 9.99
col first for A20
col second for A20


select to_char(start_time,'DD HH:MI:SS'),
       samples,
       --total,
       --waits,
       --cpu,
       round(fpct * (total/&v_secs),2) aas1,
       decode(fpct,null,null,first) first,
       round(spct * (total/&v_secs),2) aas2,
       decode(spct,null,null,second) second,
        substr(substr(rpad('+',round((cpu*&v_bars)/&v_secs),'+') ||
        rpad('-',round((waits*&v_bars)/&v_secs),'-')  ||
        rpad(' ',p.value * &v_bars,' '),0,(p.value * &v_bars)) ||
        p.value  ||
        substr(rpad('+',round((cpu*&v_bars)/&v_secs),'+') ||
        rpad('-',round((waits*&v_bars)/&v_secs),'-')  ||
        rpad(' ',p.value * &v_bars,' '),(p.value * &v_bars),10) ,0,30)
        graph
     --  spct,
     --  decode(spct,null,null,second) second,
     --  tpct,
     --  decode(tpct,null,null,third) third
from (
select start_time
     , max(samples) samples
     , sum(top.total) total
     , round(max(decode(top.seq,1,pct,null)),2) fpct
     , substr(max(decode(top.seq,1,decode(top.event,'ON CPU','CPU',event),null)),0,15) first
     , round(max(decode(top.seq,2,pct,null)),2) spct
     , substr(max(decode(top.seq,2,decode(top.event,'ON CPU','CPU',event),null)),0,15) second
     , round(max(decode(top.seq,3,pct,null)),2) tpct
     , substr(max(decode(top.seq,3,decode(top.event,'ON CPU','CPU',event),null)),0,10) third
     , sum(waits) waits
     , sum(cpu) cpu
from (
  select
       to_date(tday||' '||tmod*&v_secs,'YYMMDD SSSSS') start_time
     , event
     , total
     , row_number() over ( partition by id order by total desc ) seq
     , ratio_to_report( sum(total)) over ( partition by id ) pct
     , max(samples) samples
     , sum(decode(event,'ON CPU',total,0))    cpu
     , sum(decode(event,'ON CPU',0,total))    waits
  from (
    select
         to_char(sample_time,'YYMMDD')                      tday
       , trunc(to_char(sample_time,'SSSSS')/&v_secs)          tmod
       , to_char(sample_time,'YYMMDD')||trunc(to_char(sample_time,'SSSSS')/&v_secs) id
       , decode(ash.session_state,'ON CPU','ON CPU',ash.event)     event
       , sum(decode(session_state,'ON CPU',1,decode(session_type,'BACKGROUND',0,1))) total
       , (max(sample_id)-min(sample_id)+1)                    samples
     from
        v$active_session_history ash
     where
               sample_time > sysdate - &v_days
     group by  trunc(to_char(sample_time,'SSSSS')/&v_secs)
            ,  to_char(sample_time,'YYMMDD')
            ,  decode(ash.session_state,'ON CPU','ON CPU',ash.event)
union all
    select
         to_char(sample_time,'YYMMDD')                      tday
       , trunc(to_char(sample_time,'SSSSS')/&v_secs)          tmod
       , to_char(sample_time,'YYMMDD')||trunc(to_char(sample_time,'SSSSS')/&v_secs) id
       , decode(ash.session_state,'ON CPU','ON CPU',ash.event)     event
       , sum(decode(session_state,'ON CPU',10,decode(session_type,'BACKGROUND',0,10))) total
       , (max(sample_id)-min(sample_id)+1)                    samples
     from
        dba_hist_active_sess_history ash
     where
               sample_time > sysdate - &v_days
         and sample_time < ( select min(sample_time) from v$active_session_history)
     group by  trunc(to_char(sample_time,'SSSSS')/&v_secs)
            ,  to_char(sample_time,'YYMMDD')
            ,  decode(ash.session_state,'ON CPU','ON CPU',ash.event)
  )  chunks
  group by id, tday, tmod, event, total
) top
group by start_time
) aveact,
  v$parameter p
where p.name='cpu_count'
order by start_time
/



output looks like
Graph shows the load on the database measured in AAS. The + represent CPU acticity and - represent wait time
the number 4 in the graph represents 4 cores on this machine, so we can see when AAS goes above the # of cores
which is a sign of  a fair bit of contention
When there is a lot of wait, the output indicates the top two waits and what amount of AAS each represents


TO_CHAR(STA   AAS1 FIRST            AAS2 SECOND  GRAPH
-----------  ----- --------------- ----- ----------------------------------------------
10 10:00:00   2.35 direct path rea   .46 direct path wri ++----------------  4
10 11:00:00   2.23 direct path wri   .59 db file sequent +---------------    4
10 12:00:00   1.61 direct path rea  1.53 direct path wri ++------------------4
10 01:00:00    .90 db file sequent   .67 direct path rea +------------       4
10 02:00:00   1.09 direct path wri   .69 direct path rea ++------------------4-----
10 03:00:00    .60 db file paralle   .45 db file sequent ------              4
10 04:00:00    .50 db file paralle   .44 db file sequent +-------            4
10 05:00:00   2.32 db file sequent   .75 db file scatter ++------------------4----------
10 06:00:00   1.24 direct path rea   .83 db file scatter ++------------------4
10 07:00:00   3.41 direct path rea   .73 direct path wri +++-----------------4----------
10 08:00:00   3.98 direct path rea   .94 direct path wri +-------------------4---------
10 09:00:00   3.30 log buffer spac  2.15 buffer busy wai +-------------------4----------
10 10:00:00   2.02 db file scatter  1.23 direct path wri ++------------------4----------
10 11:00:00   2.15 db file scatter  1.50 CPU             +++++++-------------4----------
11 12:00:00   2.55 direct path rea  1.73 db file scatter +++-----------------4----------
11 01:00:00   1.12 direct path rea   .72 db file scatter +-------------      4
11 02:00:00    .90 direct path rea   .58 direct path wri ++-------------     4
11 03:00:00    .65 direct path rea   .45 direct path wri +-----------        4
11 04:00:00    .59 log buffer spac   .39 direct path rea --------            4
11 05:00:00    .95 log buffer spac   .76 log file sync   -----------         4
11 06:00:00    .82 log buffer spac   .68 log file sync   +------------       4
11 07:00:00   2.42 direct path rea  1.49 direct path wri +++-----------------4----------
11 08:00:00   3.35 log buffer spac  2.59 db file scatter ++------------------4----------
11 09:00:00   1.10 db file scatter   .64 log buffer spac ++----------------  4
11 10:00:00    .01 db file scatter   .01 CPU                                 4

Comments