Where is V$waitstat happening.
Many of you will know of the internal monitoring view v$waitstat (based on x$kcbwait), which give an indication of the number of times an instance has had buffer busy waits on different classes of blocks since instance startup.
Although this information is generally not very significant, occasionally it can be helpful, so it is a little surprising that Oracle has never made visible the companion object X$KCBFWAIT, which duplicates the function of x$kcbwait, but summarises the waits by file id.
The following , very simple, script lists the contents of x$kcbfwait. It is left as an exercise to the reader to include file names, elimnate files where there have been no waits, and push it into a package or view so that accounts other than SYS can see the waits.
The code is good for Oracle 7 and 8.
rem Script: file_wait.sql
rem Author: J.P.Lewis
rem Dated: 7-Jan-1997
rem Purpose: List buffer busy waits by files
column file# format 999 Heading "File"
column ct format 999999 heading "Waits"
column time format 999999 heading "Time"
column avg format 999.999 heading "Avg time"
indx < (select count(*) from v$datafile)