-
Notifications
You must be signed in to change notification settings - Fork 5
/
aveactn300.sql
97 lines (90 loc) · 3.22 KB
/
aveactn300.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
-- (c) Kyle Hailey 2007
spool ash_workload.txt append
set lines 500
column f_days new_value v_days
select 1 f_days from dual;
column f_secs new_value v_secs
select 5 f_secs from dual;
--select &seconds f_secs from dual;
column f_bars new_value v_bars
select 5 f_bars from dual;
column aveact format 999.99
column graph format a50
column fpct format 99.99
column spct format 99.99
column tpct format 99.99
column AAS1 format 999.99
column AAS2 format 999.99
column EVENT1 format a23
column EVENT2 format a23
select to_char(start_time,'DD HH:MI:SS'),
samples,
--total,
--waits,
--cpu,
round(fpct * (total/samples),2) AAS1,
decode(fpct,null,null,first) EVENT1,
round(spct * (total/samples),2) AAS2,
decode(spct,null,null,second) EVENT2,
substr(substr(rpad('+',round((cpu*&v_bars)/samples),'+') ||
rpad('-',round((waits*&v_bars)/samples),'-') ||
rpad(' ',p.value * &v_bars,' '),0,(p.value * &v_bars)) ||
p.value ||
substr(rpad('+',round((cpu*&v_bars)/samples),'+') ||
rpad('-',round((waits*&v_bars)/samples),'-') ||
rpad(' ',p.value * &v_bars,' '),(p.value * &v_bars),10) ,0,50)
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,25) 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,25) 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,25) 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)
order by
to_char(sample_time,'YYMMDD'),
trunc(to_char(sample_time,'SSSSS')/&v_secs)
) 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
/
spool off