Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Extend STATSPACK to Capture disk I/O

Nov 11, 2015
Oracle Tips by Burleson Consulting


For complete details and scripts for viewing disk I/O metrics in Oracle, see my book "Oracle Tuning: The Definitive Reference".

One of the current shortcomings of STATSPACK is that it cannot directly monitor disk input and output (I/O). Here's a great technique that I use for extending the capabilities of Oracle's STATSPACK performance utility to report statistics on I/O activity at the disk and file level in a UNIX environment.

Ordinarily, statistics captured by a STATSPACK snapshot are related only to the read and write activity at the Oracle data file level. These statistics are stored in the stats$filestatxs table. Normally, STATSPACK does not show I/O at the disk or mount point level, which can be valuable information in determining hyperactivity on particular files or disks.

An IBM disk I/O monitor


UNIX, on the other hand, displays read and write I/O statistics only at the physical disk level, and it's the responsibility of the Oracle administrator to know what mount points and disks are used to store the Oracle data files. If DBAs segregate tables and indexes into separate tablespaces, they will know which objects reside in each file, and they can tell which tables and indexes are experiencing high I/O rates.

Instead of using standard utilities to generate a report for a single time period, you can modify utilities to collect I/O data over consistent intervals, storing the results in Oracle tables for easy access and reporting. The following is an outline of requirements:
 

  • File-level statistics?As mentioned, the STATSPACK stats$filestatxs table contains I/O data collected by snapshots taken at consistent intervals. I/O data captured includes the actual number of physical reads, physical writes, block reads, block writes, and the time required for each operation.
     
  • Disk-level statistics?By extending STATSPACK to capture disk I/O from the UNIX iostat command and placing the data in a newly created STATSPACK extension table (stats$iostat), we create a repository for expanded data reporting. The stats$iostat table will contain overall disk-level read and write information with corresponding timestamps. Using a script, we call get_iostat.ksh, and we collect the disk-level information displayed by the iostat command at regular intervals and insert it into the stats$iostat table.


Below is the sample script that I use in Solaris. Additionally, a cross-reference table (stats$vol_grp) between the stats$filestatxs and stats$iostat tables, could be created, linking mount points to physical disks. You would need to populate this table manually, according to how disks are partitioned into mount points. The design of these tables lets you aggregate, or average, I/O data over time and create summaries by disk, mount point, tablespace, or datafile.

#!/bin/ksh
while true
do
 iostat -x 300 1|\
   sed 1,2d|\
   awk  '{ printf("%s %s %s\n", $1, $4, $5) }' |\
  while read HDISK VMSTAT_IO_R VMSTAT_IO_W
  do
    if [ $VMSTAT_IO_R -gt 0 ] and [ $VMSTAT_IO_W -gt 0 }
    then
     sqlplus -s perfstat/perfstat <<!
      insert into perfstat.stats\$iostat
      values
        (SYSDATE, 5, '$HDISK', $VMSTAT_IO_R, $VMSTAT_IO_W);
      exit
!
   fi
  done
  sleep 300
done


Specific I/O activity

When the DBA prudently segregates Oracle objects into distinct datafiles and tablespaces, STATSPACK can be used to create useful reports showing individual I/O, selected datafiles, or groups of related datafiles. For example, the script in Listing B uses a filename "mask" to report on selected groups of related datafiles.

If we have purposefully named our customer-related datafilescustomer.dbf, custhistory.dbf, and custorders.dbf, we can use this script to report the I/O history on all datafile names that contain the string "cust".

set pages 9999;
column filename format a40
column mydate heading 'Yr. Mo Dy  ' format a16
 
 
select
  to_char(snap_time,'yyyy-mm-dd') mydate,
   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;


The output from this script shows the total read and write I/O per day for our custdatafiles. In order to better visualize what the report is telling us, we can graph the statistics using Excel's chart wizard, as shown below. By viewing the output graphically, we can often recognize patterns of activity called I/O signatures, which are useful in file load balancing.
 

Yr. Mo Dy          PHY_RDS   PHY_WRTS  
---------------- ---------- ----------
2002-12-18                7        226
2002-12-19               87        556
2002-12-20              141        640
2002-12-21               26        452
2002-12-22               45        368
2002-12-23              10        115
2002-12-24                3         14
2002-12-25                5         54
2002-12-26              169        509
2002-12-27               14        101
2002-12-28               25        316
2002-12-29               13        132
2002-12-30                7        158
2002-12-31                2        129
2015-01-01                4        264
2015-01-02               57        756
2015-01-03               56        317
2015-01-04             1110        123
2015-01-05            1075        386
2015-01-06               20        293
2015-01-07                1          6
2015-01-08              955       1774
2015-01-09              247       1145
2015-01-10              538       1724
2015-01-11              387       1169
2015-01-12             1017       1964
2015-01-13              115        397
2015-01-14               89        443
2015-01-15               22        125
2015-01-16             1267       1667
2015-01-17              646       2082
2015-01-18              588       2359


The script below uses the newly captured disk-level statistics to display the sum of disk I/O by day, hour, or collection interval (five minutes in our earlier script). Notice how the script time interval can be selected via un-commenting appropriate lines of code. Here we see a partial listing of the report generated here.
 

column hdisk            format a10;
column mydate           format a15;
column sum_kb_read      format 999,999;
column sum_kb_write     format 999,999;
 
 
set pages 999;
 
 
break on hdisk skip 1;
 
 
select
  hdisk,
--  to_char(snap_time,'yyyy-mm-dd HH24:mi:ss') mydate,
  to_char(snap_time,'yyyy-mm-dd HH24') mydate,
--  to_char(snap_time,'day') mydate,
   sum(kb_read) sum_kb_read,
   sum(kb_write) sum_kb_write
from
  stats$iostat
group by
  hdisk
--   ,to_char(snap_time,'day')
--  ,to_char(snap_time,'yyyy-mm-dd HH24:mi:ss')
  ,to_char(snap_time,'yyyy-mm-dd HH24')
;
 
 
HDISK      MYDATE          SUM_KB_READ SUM_KB_WRITE
---------- --------------- ----------- ------------
atf0      2002-12-26 21           9        477
          2002-12-26 22          12        636
          2002-12-26 23         112      14636
          2002-12-27 07         382       3636
          2002-12-27 08         433        641
 
 
atf2      2002-12-26 21           0          4
 
atf4      2002-12-26 21           9         90
          2002-12-26 22          12        120
          2002-12-26 23         132       5655


Unlimited enhancements

I've provided a brief introduction to how the standard STATSPACK utility can be extended to collect and report on additional UNIX-provided information.

Enhancing the reporting capabilities of STATSPACK is bound only by the imagination of the DBA in search of automating mundane tasks while providing timely and useful information.

   
Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training
 
 

 


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational