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 |