Aveactn.sql
Oracle: aveactn.sql /*
| TO_CHAR(STA | SAMPLES | AAS1 | FIRST | AAS2 | SECOND | GRAPH |
| 23 10:00:00 | 647 | .01 | db file sequent | .01 | db file scatter | 4 |
| 23 11:00:00 | 2773 | .02 | CPU | .01 | .buffer busy wai | 4 |
| 23 12:00:00 | 3029 | .00 | CPU | .00 | db file paralle | 4 |
| 23 01:00:00 | 2761 | .00 | SQL*Net break/r | .00 | control file se | 4 |
| 23 02:00:00 | 2258 | .00 | CPU | .00 | log file sync | 4 |
| 23 03:00:00 | 1651 | .03 | buffer busy wai | .01 | CPU |
*/
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
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)
- select
- ) 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 /