| |
 |
|
Viewing
I/O Signatures with STATSPACK
Oracle Tips by Burleson |
You will find that your database will develop
distinctive I/O signatures. The I/O signature for an OLTP database
will be very different than that of a data warehouse, and you can use
these I/O signatures to determine regular times when the disk I/O
subsystem is overloaded. When we aggregate disk information by day of
the week and hour of the day, we can see some very interesting
patterns.
NOTE: When developing I/O signatures for your
database, it is very important to begin at the global level and drill
down for successive detail. For example, after running the global
reports, you will find spikes in your database I/O during specific
times. Your next step should be to isolate these I/O spikes to
specific Oracle database files by closer inspection of the STATSPACK
data, and running the rpt_io_pct.sql script to report on
specific datafiles.
Let's begin by taking a look at a STATSPACK
script to average disk read and write activity by the day of the week.
rpt_avg_io_dy.sql
L 8-23
set pages
9999;
column reads format 999,999,999
column writes format 999,999,999
select
to_char(snap_time,'day'),
avg(newreads.value-oldreads.value) reads,
avg(newwrites.value-oldwrites.value) writes
from
perfstat.stats$sysstat oldreads,
perfstat.stats$sysstat newreads,
perfstat.stats$sysstat oldwrites,
perfstat.stats$sysstat newwrites,
perfstat.stats$snapshot sn
where
newreads.snap_id = sn.snap_id
and
newwrites.snap_id = sn.snap_id
and
oldreads.snap_id = sn.snap_id-1
and
oldwrites.snap_id = sn.snap_id-1
and
oldreads.statistic# = 40
and
newreads.statistic# = 40
and
oldwrites.statistic# = 41
and
newwrites.statistic# = 41
having
avg(newreads.value-oldreads.value) > 0
and
avg(newwrites.value-oldwrites.value) > 0
group by
to_char(snap_time,'day')
;
The output from the script will take a running
average by the day of the week and display the output as follows:
L 8-24
TO_CHAR(S
READS WRITES
--------- ------------ ------------
friday 72 2,093
monday 221 8,896
saturday 211 5,869
sunday 160 5,056
thursday 338 7,232
tuesday 603 11,765
wednesday 316 7,781
This output can
then be pasted into an Excel spreadsheet, resequenced, and displayed
using the Excel Chart Wizard. In Figure 8-14, we see the I/O signature
for an Oracle database. Note that this signature clearly shows peak
write activity on Mondays, Tuesdays, and Wednesdays. From this
signature, the Oracle DBA knows that this database is loaded during
the first part of each week.
Figure 8-52: Average I/O signature by day of the
week
Now, let's see how easy it is to change this
report to aggregate the data by the hour of the day. The script here
is identical to the aggregate averages by day of the week except that
the date format string has been changed from ‘day' to ‘HH24'.
rpt_avg_io_hr.sql
L 8-25
set pages
9999;
column reads format 999,999,999
column writes format 999,999,999
select
to_char(snap_time,'HH24'),
avg(newreads.value-oldreads.value) reads,
avg(newwrites.value-oldwrites.value) writes
from
perfstat.stats$sysstat oldreads,
perfstat.stats$sysstat newreads,
perfstat.stats$sysstat oldwrites,
perfstat.stats$sysstat newwrites,
perfstat.stats$snapshot sn
where
newreads.snap_id = sn.snap_id
and
newwrites.snap_id = sn.snap_id
and
oldreads.snap_id = sn.snap_id-1
and
oldwrites.snap_id = sn.snap_id-1
and
oldreads.statistic# = 40
and
newreads.statistic# = 40
and
oldwrites.statistic# = 41
and
newwrites.statistic# = 41
having
avg(newreads.value-oldreads.value) > 0
and
avg(newwrites.value-oldwrites.value) > 0
group by
to_char(snap_time,'HH24')
;
Now, when we execute this script we see the
read and write averages displayed by the hour of the day. Again, we
can paste this output into a spreadsheet and create a graphical
representation, thereby getting a visual picture of the I/O signature.
L 8-26
TO
READS WRITES
-- ------------ ------------
00 250 6,103
02 180 4,701
03 174 4,580
04 195 5,832
05 191 5,109
06 171 4,669
07 221 4,727
08 354 5,353
09 264 9,531
10 258 7,994
11 249 7,397
12 364 8,499
13 341 7,902
14 326 8,288
15 305 10,891
16 279 9,019
17 692 17,291
18 592 10,444
19 448 9,911
20 385 8,247
21 395 11,405
22 366 9,182
23 271 7,308
The graph in
Figure 8-15 (on the facing page) is a graphical representation of the
I/O signature of physical reads by hour of the day. Here we see a
clear daily trend where the read activity increases throughout the
afternoon and a high peak of read activity every day at 6:00
p.m. This information
can be extremely valuable to the Oracle DBA. In this example, the DBA
could encourage the end-user community to direct their processing to
periods before 5:00 p.m.
Figure 8-53: Oracle physical read activity
averages by hour of the day
We can also
plot the physical write activity in a graph, as shown in Figure 8-16.
In this case, we see a gradual pattern of increasing writes to the
database, peaking in the late afternoon. This pattern would be
confirmed by an increase in the number of archived redo logs generated
later in the day.
Figure 8-54: Oracle physical write activity
averages by hour of the day
This is an excerpt from "Oracle
High Performance tuning with STATSPACK" by Oracle Press.
 |
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts. |
|
