Aveactf.sql
Oracle: aveactf.sql
/*
| TM | NPTS | AAS | GRAPH | CPU | WAITS | |
| 23-JAN 15:28:00 | 8 | .53 | +-- | 4 | 9 | 23 |
| 23-JAN 15:30:00 | 31 | .00 | 4 | 0 | 0 | |
| 23-JAN 15:31:00 | 1 | .00 | 4 | 0 | 0 | |
| 23-JAN 15:32:00 | 1 | .00 | 4 | 0 | 0 | |
| 23-JAN 15:33:00 | 1 | .00 | 4 | 0 | 0 |
*/
Def v_bars= 5 -- width of one AAS
Def v_graph= 30 -- total width of graph in characters
Def v_secs= 60 -- size of bucket
Def v_minutes= 60 -- total time
col waits for 9999
col cpu for 9999
col graph format a&v_graph
col total format 99999
col npts format 99999
col aas format 999.99
/*
- dba_hist_active_sess_history
*/ select
- to_char(to_date(tday||' '||tmod*&v_secs,'YYMMDD SSSSS'),'DD-MON HH24:MI:SS') tm,
- samples npts,
- total/&v_secs aas,
- substr(
- 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)
- ,0,&v_graph)
- graph,
- -- total,
- cpu,
- waits
from (
- select
- to_char(sample_time,'YYMMDD') tday
- , trunc(to_char(sample_time,'SSSSS')/&v_secs) tmod
- , sum(decode(session_state,'ON CPU',1,decode(session_type,'BACKGROUND',0,1))) total
- , (max(sample_id) - min(sample_id) + 1 ) samples
- , sum(decode(session_state,'ON CPU' ,1,0)) cpu
- , sum(decode(session_state,'WAITING',1,0)) -
- sum(decode(session_type,'BACKGROUND',decode(session_state,'WAITING',1,0))) waits
- /* for waits I want to subtract out the BACKGROUND
- but for CPU I want to count everyon */
- from
- v$active_session_history
- where sample_time > sysdate - &v_minutes/(60*24)
- group by trunc(to_char(sample_time,'SSSSS')/&v_secs),
- to_char(sample_time,'YYMMDD')
- select
union all
- select
- to_char(sample_time,'YYMMDD') tday
- , trunc(to_char(sample_time,'SSSSS')/&v_secs) tmod
- , sum(decode(session_state,'ON CPU',1,decode(session_type,'BACKGROUND',0,1))) total
- , (max(sample_id) - min(sample_id) + 1 ) samples
- , sum(decode(session_state,'ON CPU' ,10,0)) cpu
- , sum(decode(session_state,'WAITING',10,0)) -
- sum(decode(session_type,'BACKGROUND',decode(session_state,'WAITING',10,0))) waits
- /* for waits I want to subtract out the BACKGROUND
- but for CPU I want to count everyon */
- from
- dba_hist_active_sess_history
- where sample_time > sysdate - &v_minutes/(60*24)
- 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')
- select
) ash,
- v$parameter p
where p.name='cpu_count' order by to_date(tday||' '||tmod*&v_secs,'YYMMDD SSSSS') /