forked from khailey-zz/ashmasters
-
Notifications
You must be signed in to change notification settings - Fork 0
/
ash_graph_waits_histash.sql
135 lines (124 loc) · 5.68 KB
/
ash_graph_waits_histash.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
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
set linesize 150
/*
ASH graph from v$active_session_history and dba_hist_active_sess_history
no filter no DBID
time filter by # of days, input variable &v_days
Output looks like
TO_CHAR( AAS PCT1 FIRST PCT2 SECOND GRAPH
-------- --- ---- --------------- ---- --------------- -------------------------------------------
15 15:00 7 46 db file sequent 19 CPU +++++++ooooooooooooo4ooooooooooo------
15 16:00 6 53 db file sequent 31 CPU +++++++++ooooooooooo4oooooooo---
15 17:00 7 48 db file sequent 41 CPU +++++++++++++++ooooo4oooooooooooooooo-
15 18:00 5 55 CPU 37 db file sequent ++++++++++++++oooooo4oooooo-
15 19:00 1 64 CPU 21 db file sequent ++o 4
15 20:00 1 63 CPU 19 read by other s ++++o- 4
15 21:00 2 31 db file sequent 24 CPU ++ooo---- 4
15 22:00 3 35 CPU 24 db file scatter +++++ooooooo--- 4
15 23:00 6 29 log file sync 25 db file sequent ++++ooooooooo-------4-------------
16 00:00 7 52 db file sequent 27 CPU ++++++++++oooooooooo4ooooooooooooooo--
16 01:00 4 57 CPU 36 db file sequent +++++++++++oooooooo 4
16 02:00 6 38 db file sequent 21 CPU ++++++oooooooooooo--4---------
16 03:00 3 69 db file sequent 20 CPU +++ooooooooooo 4
16 04:00 0 45 db file sequent 28 CPU o 4
16 05:00 1 58 db file sequent 24 CPU +ooo 4
16 06:00 1 41 db file sequent 39 CPU +oo 4
The "graph" on the right shows the load over time each line is an hour by default.
The "+" represent CPU, "o" represent I/O and "-" represent a wait.
The columns "FIRST" and "SECOND" represent the top two things happening on the database.
*/
Def v_secs=60 -- bucket size
Def v_days=1 -- total time analyze
Def v_bars=5 -- size of one AAS in characters
Def v_graph=80
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 99.99
col aas2 format 99.99
col first format A15
col second format A15
select to_char(start_time,'DD HH24: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
/