JL Computer Consultancy

Where is V$waitstat happening.

January 1997

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.

Back to Main Index of Topicss

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

Back to Main Index of Topicss