buffer busy waits

[Knarayan]

I use spotlight to monitor the database, I see buffer busy waits a lot of times and I want to find out which table it is occurring in. But I am not able to see any buffer busy waits in v$session_wait (to get the hot block). It appears in v$system_event and v$session_event but never in v$session_wait. How do I find out which is the block for which there are large number of waits.

Thanx

[mattias]

Spotlight is not able to give you this information in a simple way, you need StorageXpert or a similar tool for this that will go in deep (very deep) and point out hotspots. I recommend a visit to the site http://www.ixora.com.au/scripts/cache.htm where you can find scripts that might help you, I use alot of these scripts when handling buffer busy waits and such things.

[pando]

From Anjo Kolk

buffer busy waits
This event is caused by:
•multiple sessions requesting the same block (i.e., one or more sessions are waiting
for a process to read the requested block into the buffer cache)
•multiple sessions waiting for a change to complete for the same block (only one
process at a time can write to the block, so other processes have to wait for that
buffer to become available)
If buffer busy waits is high, determine which blocks are being accessed concurrently and if
the blocks are being read or changed through V$SESSION_WAIT and V$WAITSTAT.
V$SESSION_WAIT will show the file#, block# and id (where id represents the status of
the buffer busy wait event).
•file# - data file number containing the block being read
•block# - block number being waited on
•id - buffer busy wait event:
•1013/1014 - block is being read by another session
•1012/1016 - block is being modified
V$WAITSTAT will show the block classes and the number of times waited for each.
Different actions may be taken for each block class to alleviate contention. Tuning
priorities should be oriented toward the classes that contribute the highest wait time
percentage.


Identifying block waits by file
X$KCBFWAIT shows a count of buffer busy waits per file. The indx column represents the
file id number - 1. So this view can be queried to determine which file has a high number
of buffer busy waits.
select indx+1 fileno, count, time
from x$kcbfwait
where time != 0 or count > 0
order by time;
If the file with highest wait time is known, find the objects that belong to that file:
select file_id, segment_name, segment_type, freelists,
freelist_groups, pctfree, pctused
from dba_extents
where file_id = ;

[Knarayan]

When I run the query below to find out for what file/tablespace the buffer busy waits is occurring for,

select
d.tablespace_name,
sum(x.count) total_waits,
sum(x.time) time_waited
from
sys.x_$kcbfwait x,
sys.dba_data_files d
where
x.inst_id = userenv('Instance') and
x.count > 0 and
d.file_id = x.indx + 1
group by
d.tablespace_name
order by 3 desc
--
SYSTEM 52964 219146
RBS 2644 1346
DATA 14 18
INDEXES 1 0


I see that the system tablespace has the highest waits, what does this mean, I thought buffer busy waits occur on waits for blocks/freelists for inserts??

Thanx in advance.