Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
  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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter