AWR query to find load spikes
select
to_char(round(sub1.sample_time, ‘HH24′), ‘YYYY-MM-DD HH24:MI’) as sample_hour,
round(avg(sub1.on_cpu),1) as cpu_avg,
round(avg(sub1.waiting),1) as wait_avg,
round(avg(sub1.active_sessions),1) as act_avg,
round( (variance(sub1.active_sessions)/avg(sub1.active_sessions)),1) as act_var_mean
from
( — sub1: one row per ASH/AWR sample observation
select
sample_id,
sample_time,
sum(decode(session_state, ‘ON CPU’, 1, 0)) as on_cpu,
sum(decode(session_state, ‘WAITING’, 1, 0)) as waiting,
count(*) as active_sessions
from
dba_hist_active_sess_history
where
sample_time > sysdate – (&hours/24)
group by
sample_id,
sample_time
) sub1
group by
round(sub1.sample_time, ‘HH24′)
order by
round(sub1.sample_time, ‘HH24′)
;
SAMPLE_HOUR CPU_AVG WAIT_AVG ACT_AVG ACT_VAR_MEAN
—————– ———- ———- ———- ————
2008-07-26 04:00 1.5 0 1.5 .4
2008-07-26 05:00 1.5 .1 1.5 .5
2008-07-26 06:00 1.5 .1 1.6 .5
2008-07-26 07:00 1.6 0 1.6 .6
2008-07-26 08:00 5.5 .1 5.6 14.2
2008-07-26 09:00 1.5 .1 1.6 .6
2008-07-26 10:00 2 .3 2.3 1
2008-07-26 11:00 1.8 .2 1.9 .6
2008-07-26 12:00 1.9 .3 2.1 .6
2008-07-26 13:00 1.5 .1 1.6 .5
2008-07-26 14:00 1.5 .1 1.6 .4
2008-07-26 15:00 1.5 .2 1.7 .5
2008-07-26 16:00 1.5 .1 1.7 .5
2008-07-26 17:00 2.1 .1 2.3 1.2
2008-07-26 18:00 16.4 .4 16.8 52.2
2008-07-26 19:00 1.7 .1 1.8 .6
2008-07-26 20:00 3.5 .1 3.6 4.1
2008-07-26 21:00 2.3 .1 2.4 1
2008-07-26 22:00 2.7 .4 3.1 1.3
2008-07-26 23:00 2 .4 2.4 .6
2008-07-27 00:00 2.1 .2 2.4 .7
2008-07-27 01:00 3 .1 3.1 1.9
2008-07-27 02:00 2 .1 2.1 .9
2008-07-27 03:00 1.5 0 1.6 .5
2008-07-27 04:00 1.6 .1 1.6 .5
2008-07-27 05:00 1.3 .1 1.4 .4
2008-07-27 06:00 2 0 2.1 1.3
2008-07-27 07:00 1.6 0 1.7 .7
Here we found two spikes ..
- No! short spikes:
- might not generate user complaints,
- might be ignored by monitors,
- but are in fact problems
- No! Very high AAS values always a problem
- Use of variance to find skew minimizes limits of aggregated statistics
- Enough detail in AWR/ASH to find bottlenecks
- Perhaps you should go fishing sometimes!
- Month’s worth of AWR makes this easy
Aggregate statistics
selectsub.sql_id,
sub.seconds_since_date,
sub.execs_since_date,
sub.gets_since_date
from
( — sub to sort before rownum
select
sql_id,
round(sum(elapsed_time_delta)/1000000) as seconds_since_date,
sum(executions_delta) as execs_since_date,
sum(buffer_gets_delta) as gets_since_date
from
dba_hist_snapshot natural join dba_hist_sqlstat
where
begin_interval_time > to_date(‘&start_YYYYMMDD’,'YYYY-MM-DD’)
group by
sql_id
order by
2 desc
) sub
where
rownum < 30
;
Enter value for start_yyyymmdd: 2010-03-01
old 16: begin_interval_time > to_date(‘&&start_YYYYMMDD’,'YYYY-MM-DD’)
new 16: begin_interval_time > to_date(’2010-03-01′,’YYYY-MM-DD’)
SQL_ID SECONDS_SINCE_DATE EXECS_SINCE_DATE GETS_SINCE_DATE
————- —————— —————- —————
1wc4bx0qap8ph 30617 21563 284059357
6hudrj03d3w5g 23598 20551110 472673974
6tccf6u9tf891 18731 33666 457970700
2u874gr7qz2sk 15175 29014 370715705
fpth08dw8pyr6 14553 2565 36018228
1jt5kjbg7fs5p 11812 12451 2004271887
2f75gyksy99zn 10805 21529 567776447
ccp5w0adc6xx9 5222 6167 222949142
gn26ddjqk93wc 3568 114084711 248687700
b6usrg82hwsa3 2888 2 165621244
ctaajfgak033z 2391 4 66644336
7zwhhjv42qmfq 2197 592377 31495833
96v8tzx8zb99s 2152 6167 117875813
cxjsw79bprkm4 1526 396277 137413869
f2awk3951dcxv 1500 3462 35853709
fzmzt8mf2sw16 1421 311 44067742
01bqmm3gcy9yj 1329 299778 23504806
Non-uniform statistics
selectsub1.sql_id,
round(avg(sub1.seconds_per_hour)) as avg_seconds_per_hour,
round(variance(sub1.seconds_per_hour)/avg(sub1.seconds_per_hour)) as var_over_mean,
count(*) as ct
from
( — sub1
select
snap_id,
sql_id,
elapsed_time_delta/1000000 as seconds_per_hour
from
dba_hist_snapshot natural join dba_hist_sqlstat
where
– look at recent history only
begin_interval_time > sysdate – &&days_back
and
executions_delta > 0
) sub1
group by
sub1.sql_id
having
– only queries that consume 10 seconds per hour on the average
avg(sub1.seconds_per_hour) > 10
and
– only queries that run 50% of the time, assumes hourly snapshots too
count(*) > ( &&days_back * 24) * 0.50
order by
3;
Example (sqlstat, high variance): obvious outlier with high variance, but not the most elapsed time
SQL_ID AVG_SECONDS_PER_HOUR VAR_OVER_MEAN CT
————- ——————– ————- ———-
72wuyy9sxdmpx 41 7 167
bgpag6tkxt34h 29 12 167
crxfkabz8atgn 14 14 167
66uc7dydx131a 16 16 167
334d2t692js2z 36 19 167
6y7mxycfs7afs 23 20 167
36vs0kyfmr0qa 17 21 129
fp10bju9zh6qn 45 22 167
fas56fsc7j9u5 10 22 167
61dyrn8rjqva2 17 22 129
4f8wgv0d5hgua 31 23 167
7wvy5xpy0c6k5 15 23 151
8v59g9tn46y3p 17 24 132
9pw7ucw4n113r 59 27 167
41n1dhb0r3dhv 32 32 120
8mqxjr571bath 35 38 117
8jp67hs2296v3 46 154 128
afdjq1cf8dpwx 34 184 150
6n3h2sgxpr78g 454 198 145
g3176qdxahvv9 42 383 92
b72dmps6rp8z8 209 1116 167
6qv7az2048hk4 3409 50219 167
Behavior of a specific SQL over time
selectsnap_id,
to_char(begin_interval_time,’YYYY-MM-DD HH24:MI’) as begin_hour,
executions_delta as execs_per_hour,
buffer_gets_delta as gets_per_hour,
round(buffer_gets_delta/executions_delta) as gets_per_exec,
round(elapsed_time_delta/1000000) as seconds_per_hour
from
dba_hist_snapshot natural join dba_hist_sqlstat
where
begin_interval_time between to_date(‘&start_hour’, ‘YYYY-MM-DD HH24:MI’)
and to_date(‘&end_hour’, ‘YYYY-MM-DD HH24:MI’)
and
sql_id = ‘&sql_id’
and
executions_delta > 0
order by
snap_id
;
Example (sqlstat, one sql_id): sustained high execution rates, occasional wait pile-ups
SNAP_ID BEGIN_HOUR EXECS_PER_HOUR GETS_PER_HOUR GETS_PER_EXEC SECONDS_PER_HOUR
———- —————- ————– ————- ————- —————-
1978 2008-04-07 20:00 140449 540639 4 11
1979 2008-04-07 21:00 124142 477807 4 17
1980 2008-04-07 22:00 90568 347286 4 20
1981 2008-04-07 23:00 83287 323100 4 30
1982 2008-04-08 00:00 57094 221166 4 49
1983 2008-04-08 01:00 43925 170594 4 7
1984 2008-04-08 02:00 38596 150277 4 4
1985 2008-04-08 03:00 35710 139576 4 4
1986 2008-04-08 04:00 29700 115429 4 4
1987 2008-04-08 05:00 43666 170520 4 5
1988 2008-04-08 06:00 50755 197116 4 6
1989 2008-04-08 07:00 80371 310652 4 9
1990 2008-04-08 08:00 111924 431470 4 11
1991 2008-04-08 09:00 127154 489649 4 27
1992 2008-04-08 10:00 139270 536962 4 25
1993 2008-04-08 11:00 128697 496013 4 18
1994 2008-04-08 12:00 158739 613554 4 45287
1995 2008-04-08 13:00 152515 587605 4 40
1996 2008-04-08 14:00 144389 555770 4 37589
1997 2008-04-08 15:00 149278 575827 4 26
1998 2008-04-08 16:00 140632 542580 4 12
1999 2008-04-08 17:00 120113 462665 4 11
2000 2008-04-08 18:00 121394 468684 4 12
2001 2008-04-08 19:00 127948 493084 4 13
System statistic history
selectstat_start.snap_id,
to_char(snap.begin_interval_time,’YYYY-MM-DD HH24:MI’) as begin_hour,
stat_end.value – stat_start.value as delta_value
from
dba_hist_sysstat stat_start,
dba_hist_sysstat stat_end,
dba_hist_snapshot snap
where
– assumes the snap_id at the end of the interval
– is one greater than the snap_id at the start ofthe interval
stat_end.snap_id = stat_start.snap_id + 1
and
– otherwise, we join stat_end and stat_start on exact matches of the remaining PK columns
( stat_end.dbid = stat_start.dbid
and stat_end.instance_number = stat_start.instance_number
and stat_end.stat_name = stat_start.stat_name
)
and
– filter for the statistic we are interested in (might want to add date range filter too)
stat_end.stat_name = ‘&stat_name’
and
– join stat_start to snap on FK
( stat_start.snap_id = snap.snap_id
and stat_start.dbid = snap.dbid
and stat_start.instance_number = snap.instance_number
)
order by
stat_start.snap_id
;
SQL> @sys-stat-hist.sql
Enter value for stat_name: DB time
old 27: stat_end.stat_name = ‘&stat_name’
new 27: stat_end.stat_name = ‘DB time’
SNAP_ID BEGIN_HOUR DELTA_VALUE
———- —————- ———–
4159 2010-07-07 17:00 2089225
4160 2010-07-07 18:00 1505607
4161 2010-07-07 19:00 31188489
4162 2010-07-07 20:00 24930866
4163 2010-07-07 21:00 1828924
4164 2010-07-07 22:00 1258286
4165 2010-07-07 23:00 396076
4166 2010-07-08 00:00 688963
4167 2010-07-08 01:00 354481
4168 2010-07-08 02:00 411555
4169 2010-07-08 03:00 325875
4170 2010-07-08 04:00 328739
4171 2010-07-08 05:00 447432
4172 2010-07-08 06:00 838585
4173 2010-07-08 07:00 1138196
4174 2010-07-08 08:00 1852437
4175 2010-07-08 09:00 68736587
4176 2010-07-08 10:00 739175
4177 2010-07-08 11:00 647451
4178 2010-07-08 12:00 702787
4179 2010-07-08 13:00 3722000
4180 2010-07-08 14:00 712481
4181 2010-07-08 15:00 475688
4182 2010-07-08 16:00 416013
0 comments:
Post a Comment