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.
rem
rem Script: filestat.sql
rem Author: J.P.Lewis
rem Dated: Lost in the mists of time
rem Purpose: Report v$filestat
rem
rem Notes:rem m_timestamp has been defined before this report is calledrem usually through a loop which calculates the date and time.rem
rem Spot the little fix for avoiding the divide by zero error.rem
rem The headings have no spaces to cater for awk further down.rem
rem The code has to be run by a user who can see v$filestatrem
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
select
file#, phyrds, phyblkrd,round(readtim/decode(phyrds,0,1,phyrds),3) readtim,
phywrts, phyblkwrt,round(writetim/decode(phywrts,0,1,phywrts),3) writetim
from v$filestat
order by file#
;
spool off
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