JL Computer Consultancy

File statistics

Prior to Aug 1999

A simple script to dump the current values of the v$filestat internal monitoring view. There is a sample of the output at the end of the page. If you want to use a (Unix) shell script to find the changes between any two snapshots from this script go to the page on awk, cut, and paste.

Note: In version 8 of Oracle, there are extra columns on the v$filestat (and underlying x$kcfio) object. The most useful of these is likely to be the 'max I/O time' column which, as its name suggests, records the single largest time recorded for an I/O. I have visited a site where the occasional I/O reported a time of 7,000,000 seconds (81 days !) - this can have a surprisingly realistic and unpleasant effect when you have recorded several million read requests, and don't have the max IO time on your report to highlight the anomly.

You need timed_statistics=true in your init.ora file to get the most benefit from this script; even so there are all sorts of things that can make the timing results useless, among them - a bug in the write-time code, using async I/O, and using multiple db_writers.

There is still some argument about whether timed_statistics is an expensive overhead or not. In the worst case I ever saw it added 10% to the total performance cost of the applicaton; however the application was such a disaster anyway that an extra 10% was pretty irrelevant. Typically I have noticed a possible, and not absolutely provable, 1% overhead - and if that is your worst worry then you've done a pretty amazing job with your system and don't need to read my web pages.

Back to Main Index of Topics

rem     Script:        filestat.sql
rem     Author:        J.P.Lewis
rem     Dated:         Lost in the mists of time
rem     Purpose:       Report v$filestat
rem     Notes:
rem     m_timestamp has been defined before this report is called
rem     usually through a loop which calculates the date and time.
rem     Spot the little fix for avoiding the divide by zero error.
rem     The headings have no spaces to cater for awk further down.
rem     The code has to be run by a user who can see v$filestat
set trimspool on
set pagesize 1023
set verify off
set feedback off
ttitle off
btitle off
clear columns
clear breaks
column  file#          format  9999   heading "File"
column  phyrds         format  9999999 heading "Reads"
column  phyblkrd       format  9999999 heading "Blks_Rd"
column  readtim        format  99.999  heading "Avg_Time"
column  phywrts        format  9999999 heading "Writes"
column  phyblkwrt      format  9999999 heading "Blks_wrt"
column  writetim       format  99.999  heading "Avg_Time"
spool filestat.&m_timestamp
        round(readtim/decode(phyrds,0,1,phyrds),3)    readtim,
        round(writetim/decode(phywrts,0,1,phywrts),3) writetim
from v$filestat
order by file#
spool off

Back to Main Index of Topics

 File    Reads  Blks_Rd Avg_Time   Writes Blks_wrt Avg_Time
----- -------- -------- -------- -------- -------- --------
    1  1774679  1774679     .759    22935    22935    4.411
    2     8654    25678    1.073     6498    25764    2.546
    3    15206    41355    1.135    12364    43546    2.451
    4     3181     3181    1.027     6703     6703    5.543
    5     2380     2380     .932     1530     1530    5.497
    6     9454    23854    1.470     9063    30820    2.599
    7      236      236    2.051        0        0     .000
    8        0        0     .000        0        0     .000
   11    32797    91000    1.187    25954    94469    2.539
   12    15256    43716    1.537    14374    46862    2.503
   13    18742    18742    1.305        0        0     .000

Back to Main Index of Topics