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

 

 

   
  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$sysstatThe 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$filestatxsThe 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.

  
 

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