Buffer Busy Waits on ASSM

Advertisement
Oracle 11.1.0.7
Windows 2003
We have huge Buffer Busy Waits , Tablespace is in ASSM. How can we avoid this ?
Thank You
Advertisement

Replay

Buffer busy waits are reported when multiple sessions try to read the same block in the cache.
Wait events:
Buffer busy wait:
This event is commonly caused when multiple session are trying to read the same block or multiple session waiting for a change to complete in the same block. Block contention corrective actions depends on the type of block involved. Query on V$WAITSTAT and X$KCBFWAIT to detect the hottest blocks breaking down by the type of block. To reduce buffer busy waits on:
data blocks:
Reduce number of rows per block whether changing pctfree/pctused or reducing the DB_BLOCK_SIZE.
Check for 'right-hand-indexes' (indexes that get inserted into at the same point by many processes). You can use reverse key indexes to distribute the different information.
See Note:155971.1 for a detailed case-study on how to diagnose and resolve intensive random access performance problems.
segment header:
Use freelists or increase of number of freelists.
Extent size too small can cause contention on the header when the table grows regularly. Consider increasing the extent size for the table.
undo header:
Add more rollback segments to reduce the number of transaction per rollback segment.
Reduce the value of the parameter TRANSACTION_PER_ROLLBACK_SEGMENT
undo block:
Consider making rollback segments larger in exclusive mode
Refer to metalink DOC 62172.1