 |
|
STATSPACK Reports
for Oracle Datafiles
Oracle Tips by Burleson |
To perform I/O load balancing, we need to get
information about the amount of I/O for an Oracle datafile, relative
to the total I/O from the database. Remember, a hot file is not
necessarily causing a disk bottleneck. The goal of the following
STATSPACK technique is to alert the Oracle DBA to those datafiles that
are taking a disproportionate amount of I/O relative to other files in
the database.
The script we use for this purpose is called
rpt_hot_files.sql, and this script is also incorporated into our
generalized DBA alert script, statspack_alert.sql.
The
rpt_hot_files.sql script is listed next. Let's take a look at how
this script works. The idea is to compare the overall I/O between
snapshots (hourly in this case) to the total I/O for the database, as
shown in Figure 8-12.
Figure 8-50: Elapsed time I/O comparison
To get the data we need, we rely on two
STATSPACK tables:
·
stats$sysstat The
stats$sysstat table contains two important metrics. These are used to
compute the total read I/O and write I/O for the entire database:
·
Total physical reads (statistic#=40)
·
Total physical writes (statistic#=44)
·
stats$filestatxs The
stats$filestatxs table contains detailed read I/O and write I/O,
totaled by datafile name.
We then compare the system-wide total for read
and write I/O with the individual I/O for each Oracle datafile. This
allows us to quickly generate an alert report to tell us which files
are having the most I/O activity. If we were judicious in placing
important tables and indexes into separate tablespaces and datafiles,
this report will tell us exactly which database objects are the most
active.
Note that you can adjust the thresholds for the
rpt_hot_files.sql script. You can set the threshold to 25
percent, 50 percent, or 75 percent, reporting on any files that exceed
this threshold percentage of total read and write I/O.
This is a very important script and appears in
the generic statspack_alert.sql script. It is critical that the
DBA become aware whenever an Oracle datafile is consuming a
disproportionate amount of disk I/O. The following script is somewhat
complex, but it is worth your time to carefully examine it to
understand the query. Let's examine the main steps of this SQL
statement:
1.
We select the individual I/O from stats$filestatxs and compare
the value
for each file to the total I/O as reported in stats$systat.
2.
The WHERE clause determines when a file will be reported. You
have the option of adjusting the reporting threshold by commenting out
one of the three choices—25 percent, 50 percent, or 75 percent—of the
total I/O.
rpt_hot_files.sql
set pages
9999;
set feedback off;
set verify off;
column mydate heading 'Yr. Mo Dy Hr.' format a16
column file_name format a35
column reads format 99,999,999
column pct_of_tot format 999
--prompt
--prompt
--prompt ***********************************************************
--prompt This will identify any single file with a read I/O
--prompt more than 25% of the total read I/O of the database.
--prompt
--prompt The "hot" file should be examined, and the hot table/index
--prompt should be identified using STATSPACK.
--prompt
--prompt - The busy file should be placed on a disk device with
--prompt "less busy" files to minimize read delay and channel
--prompt contention.
--prompt
--prompt - If small file has a hot small table, place the table
--prompt in the KEEP pool
--prompt
--prompt - If the file has a large-table full-table scan, place
--prompt the table in the RECYCLE pool and turn on parallel query
--prompt for the table.
--prompt ***********************************************************
--prompt
--prompt
select
to_char(snap_time,'yyyy-mm-dd HH24') mydate,
new.filename file_name,
new.phyrds-old.phyrds reads,
((new.phyrds-old.phyrds)/
(
select
(newreads.value-oldreads.value) reads
from
perfstat.stats$sysstat oldreads,
perfstat.stats$sysstat newreads,
perfstat.stats$snapshot sn1
where
sn.snap_id = sn1.snap_id
and
newreads.snap_id = sn.snap_id
and
oldreads.snap_id = sn.snap_id-1
and
oldreads.statistic# = 40
and
newreads.statistic# = 40
and
(newreads.value-oldreads.value) > 0
))*100 pct_of_tot
from
perfstat.stats$filestatxs old,
perfstat.stats$filestatxs new,
perfstat.stats$snapshot sn
where
snap_time > sysdate-&1
and
new.snap_id = sn.snap_id
and
old.snap_id = sn.snap_id-1
and
new.filename = old.filename
and
-- **********************************************************
-- Low I/O values are misleading, so we filter for high I/O
-- **********************************************************
new.phyrds-old.phyrds > 100
and
-- **********************************************************
-- The following will allow you to choose a threshold
-- **********************************************************
(new.phyrds-old.phyrds)*4> -- This is 25% of total
-- (new.phyrds-old.phyrds)*2> -- This is 50% of total
-- (new.phyrds-old.phyrds)*1.25> -- This is 75% of total
-- **********************************************************
-- This subquery computes the sum of all I/O during the snapshot
period
-- **********************************************************
(
select
(newreads.value-oldreads.value) reads
from
perfstat.stats$sysstat oldreads,
perfstat.stats$sysstat newreads,
perfstat.stats$snapshot sn1
where
sn.snap_id = sn1.snap_id
and
newreads.snap_id = sn.snap_id
and
oldreads.snap_id = sn.snap_id-1
and
oldreads.statistic# = 40
and
newreads.statistic# = 40
and
(newreads.value-oldreads.value) > 0
)
;
--prompt
--prompt
--prompt ***********************************************************
--prompt This will identify any single file who's write I/O
--prompt is more than 25% of the total write I/O of the database.
--prompt
--prompt The "hot" file should be examined, and the hot table/index
--prompt should be identified using STATSPACK.
--prompt
--prompt - The busy file should be placed on a disk device with
--prompt "less busy" files to minimize write delay and channel
--prompt contention.
--prompt
--prompt - If small file has a hot small table, place the table
--prompt in the KEEP pool
--prompt
--prompt ***********************************************************
--prompt
select
to_char(snap_time,'yyyy-mm-dd HH24') mydate,
new.filename file_name,
new.phywrts-old.phywrts writes,
((new.phywrts-old.phywrts)/
(
select
(newwrites.value-oldwrites.value) writes
from
perfstat.stats$sysstat oldwrites,
perfstat.stats$sysstat newwrites,
perfstat.stats$snapshot sn1
where
sn.snap_id = sn1.snap_id
and
newwrites.snap_id = sn.snap_id
and
oldwrites.snap_id = sn.snap_id-1
and
oldwrites.statistic# = 44
and
newwrites.statistic# = 44
and
(newwrites.value-oldwrites.value) > 0
))*100 pct_of_tot
from
perfstat.stats$filestatxs old,
perfstat.stats$filestatxs new,
perfstat.stats$snapshot sn
where
snap_time > sysdate-&1
and
new.snap_id = sn.snap_id
and
old.snap_id = sn.snap_id-1
and
new.filename = old.filename
and
-- **********************************************************
-- Low I/O values are misleading, so we only take high values
-- **********************************************************
new.phywrts-old.phywrts > 100
and
-- **********************************************************
-- Here you can choose a threshold value
-- **********************************************************
(new.phyrds-old.phywrts)*4> -- This is 25% of total
-- (new.phyrds-old.phywrts)*2> -- This is 50% of total
-- (new.phyrds-old.phywrts)*1.25> -- This is 75% of total
-- **********************************************************
-- This subquery computes the sum of all I/O during the snapshot
period
-- **********************************************************
(
select
(newwrites.value-oldwrites.value) writes
from
perfstat.stats$sysstat oldwrites,
perfstat.stats$sysstat newwrites,
perfstat.stats$snapshot sn1
where
sn.snap_id = sn1.snap_id
and
newwrites.snap_id = sn.snap_id
and
oldwrites.snap_id = sn.snap_id-1
and
oldwrites.statistic# = 44
and
newwrites.statistic# = 44
and
(newwrites.value-oldwrites.value) > 0
)
;
Please note in the above code listing that
there ate three threshold choices for the level of I/O. Two of the
lines are commented-out (using the -- notation), and the active
threshold does not have a comment.
It is highly recommended that the DBA run this
STATSPACK report daily so the DBA can constantly monitor for hot
datafiles. Here is a sample of the output from this script. Note how
it identifies hot files on an hourly basis.
***********************************************************
This will identify any single file with a read I/O
more than 50% of the total read I/O of the database.
***********************************************************
Yr. Mo Dy Hr. FILE_NAME READS
PCT_OF_TOT
---------------- ----------------------------------- -----------
----------
2001-12-14 14 /u02/oradata/prodb1/bookd01.dbf
354 62
2001-12-14 15 /u02/oradata/prodb1/bookd01.dbf
123 63
2001-12-14 16 /u02/oradata/prodb1/bookd01.dbf
132 66
2001-12-14 20 /u02/oradata/prodb1/bookd01.dbf
124 65
2001-12-15 15 /u02/oradata/prodb1/bookd01.dbf
126 72
2001-01-05 09 /u02/oradata/prodb1/system01.dbf
180 63
2001-01-06 14 /u03/oradata/prodb1/perfstat.dbf
752 100
2001-01-06 15 /u02/oradata/prodb1/bookd01.dbf
968 69
***********************************************************
This will identify any single file with a write I/O
more than 50% of the total write I/O of the database.
***********************************************************
Yr. Mo Dy Hr. FILE_NAME WRITES
PCT_OF_TOT
---------------- ----------------------------------- ----------
----------
2001-12-18 21 /u02/oradata/prodb1/bookd01.dbf
2654 58
2001-12-29 15 /u02/oradata/prodb1/bookd01.dbf
1095 49
Now that we have examined how to identify hot files,
let's take a look at other useful STATSPACK reports that can tell us
about disk activity.
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. |
|
