Aveactn.sql

From DB Optimizer
Jump to: navigation, search

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)
) 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 /