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.
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
