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 rem Script: file_wait.sql rem Author: J.P.Lewis rem Dated: 7-Jan-1997 rem Purpose: List buffer busy waits by files rem
clear columns clear breaks
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"
spool file_wait
select indx+1 file#, count ct, time, time/(decode(count,0,1,count)) avg from x$kcbfwait where indx < (select count(*) from v$datafile) ;
spool off