 |
|
Detailed Disk and File I/O with STATSPACK
Oracle Tips by Burleson |
Statistics that are captured in the
stats$filestatxs table will show details of read and write activity at
the file level. However, STATSPACK does not show I/O at the mount
point or disk level, and it is up to the Oracle administrator to know
the mapping of files to mount points and mount points to disks. On the
other hand, statistics that are captured at the UNIX level will show
read and write I/O only at the physical disk level. Again, it is up to
the Oracle administrator to know all of the mount points and datafiles
that reside on each physical disk. If we segregate tables and indexes
into separate tablespaces, we know the objects that reside in each
file, and we can tell which tables and indexes are experiencing the
high I/O.
NOTE: For users of disk array products such as EMC
and Net App, you may need third-party products to view I/O statistics.
These products include Precise*SQL, or DBView from EMC.
Rather than running the off-the-shelf utilities
that generate a printed report for a single time period, you can
modify the utilities to collect the I/O data over 5-minute intervals
and store the results in Oracle database tables for easy access and
report generation:
-
File statistics The stats$filestatxs
table contains the I/O data collected by STATSPACK. The I/O data
includes the actual number of physical reads and writes, the number
of block reads and writes, and the time required to perform each
operation.
-
Disk statistics The next section will
explore extending STATSPACK to capture external disk I/O with the
UNIX iostat utility and place the data in a STATSPACK extension
table called stats$iostat. The stats$iostat table also includes read
and write times corresponding to specific dates, but at the disk
level. It collects information from the iostat utility, using the
script get_iostat.ksh.
To provide a cross-reference between the
filestat and iostat tables, we added the vol_grp (volume/group) table,
which links mount points to physical disks. You need to populate this
table manually, based on how your disks are partitioned into mount
points. The design of the vol_grp, filestat, and iostat tables lets
you aggregate, or average, I/O data over time and summarize it by
disk, mount point, tablespace, or datafile.
A STATSPACK Report on Specific
I/O Activity
If the DBA is prudent in segregating Oracle
objects into distinct tablespaces and datafiles, STATSPACK can be used
to create extremely useful reports that show individual I/O or
selected datafiles or groups of related datafiles.
The following script accepts a filename “mask”
that can be used to report on selected groups of related datafiles.
For example, if we have named our customer-related datafiles
customer.dbf, custhistory.dbf, and custorders.dbf,
the following script can be run to report on all datafile names that
contain the string “cust”. In the example here, we execute the script
with the filename mask to see the I/O history for these datafiles.
rpt_file_io.sql
L 8-5
set pages
9999;
column snapdate format a16
column filename format a40
column mydate heading 'Yr. Mo Dy Hr.' format a16
select
to_char(snap_time,'yyyy-mm-dd') mydate,
-- old.filename,
sum(new.phyrds-old.phyrds) phy_rds,
sum(new.phywrts-old.phywrts) phy_wrts
from
perfstat.stats$filestatxs old,
perfstat.stats$filestatxs new,
perfstat.stats$snapshot sn
where
new.snap_id = sn.snap_id
and
old.filename = new.filename
and
old.snap_id = sn.snap_id-1
and
(new.phyrds-old.phyrds) > 0
and
old.filename like '%&1%'
group by
to_char(snap_time,'yyyy-mm-dd'),
old.filename
;
Here is the output from this script, showing total read
and write I/O per day for our cust datafiles:
L 8-6
2001-12-12 833 2770
2001-12-13 6 9
2001-12-14 2 80
2001-12-15 2 26
2001-12-16 2 4
2001-12-17 2 3
2001-12-18 7 226
2001-12-19 87 556
2001-12-20 141 640
2001-12-21 26 452
2001-12-22 45 368
2001-12-23 10 115
2001-12-24 3 14
2001-12-25 5 54
2001-12-26 169 509
2001-12-27 14 101
2001-12-28 25 316
2001-12-29 13 132
2001-12-30 7 158
2001-12-31 2 129
2001-01-01 4 264
2001-01-02 57 756
2001-01-03 56 317
2001-01-04 1110 123
2001-01-05 1075 386
2001-01-06 20 293
2001-01-07 1 6
2001-01-08 955 1774
2001-01-09 247 1145
2001-01-10 538 1724
2001-01-11 387 1169
2001-01-12 1017 1964
2001-01-13 115 397
2001-01-14 89 443
2001-01-15 22 125
2001-01-16 1267 1667
2001-01-17 646 2082
2001-01-18 588 2359
2001-01-19 46 296
Once gathered, this data
can be graphed (see Figure 8-13) to see the detailed activity of the
tables and indexes within these datafiles.
Figure 8-51: File I/O for a selected subset of
Oracle datafiles
This ability of graphing STATSPACK output will
be discussed in detail in Chapter 15. Often, the graphical
representation of the data is more useful, because the unique I/O
signature of the data becomes obvious.
Next, let's examine some STATSPACK tools that
can be used to identify potential disk bottlenecks.
A STATSPACK Script to Identify
Hot Datafiles
The first step in balancing the load on disks
is to find out where they're out of balance by identifying possible
bottlenecks. Start by identifying hot disks—those with a
disproportionate amount of activity. For example, if one disk in a
ten-disk system were experiencing 50 percent of the I/O, measured as
the number of reads, writes, or both, you would consider the disk to
be hot.
Detecting I/O-Related Slowdowns in AIX
If you are using the IBM AIX operating system,
it is easy to detect when a database server may be experiencing I/O
slowdowns. An I/O bound database server is usually evidenced by a high
value in the wa (wait) column of the UNIX vmstat utility. For example,
in the output here we see that 45 percent of the CPU time is being
used waiting for database I/O:
L 8-7
Prompt>
vmstat 5 1
kthr memory page faults cpu
----- ----------- ------------------------ ------------ ----------
r b avm fre re pi po fr sr cy in sy cs us sy id
wa
0 0 217485 386 0 0 0 4 14 0 202 300 210 14 19 22
45
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. |
|
