Re: Buffer busy wait clarification

Thanks DR.

below is from Oracle 9.2 performance tuning and guide reference

buffer busy waits
This wait indicates that there are some buffers in the buffer cache that multiple
processes are attempting to access concurrently. Query V$WAITSTAT for the wait
statistics for each class of buffer. Common buffer classes that have buffer busy waits
include data block, segment header, undo header, and undo block. [...]
segment header
If the contention is on the segment header, then this is most likely freelist contention.

This is what I am not able to understand. How can segment header (freelist contention) be a reason for buffer busy waits ???

Thanks

Chintu

DR wrote:
> "Chintu" <agarwalp_at_eeism.com> ha scritto nel messaggio
> news:1143027158.440998.261810_at_v46g2000cwv.googlegroups.com...
> > If Buffer busy wait indicates that "multiple process are trying to
> > access a buffered block simultaneously". How can segment header
> > (freelist contention) can be a reason for this. ? Segment Header is
> > there in the physical files. How can this cause BUFFER busy wait.
>
> From Oracle Wait Interface: A Practical Guide to Performance Diagnostics &
> Tuning by Richmond Shee, Kirtikumar Deshpande and K Gopalakrishnan
> (www.amazon.com/gp/product/007222729X/103-6495770-1997454?v=glance&n=283155)
>
> <quote>
> [...]
> A session that reads or modifies a buffer in the SGA must first acquire the
> cache buffers chains latch and traverse the buffer chain until it finds the
> necessary buffer header. Then it must acquire a buffer lock or a pin on the
> buffer header in shared or exclusive mode, depending on the operation it
> intends to perform. Once the buffer header is pinned, the session releases
> the cache buffers chains latch and performs the intended operation on the
> buffer itself. If a pin cannot be obtained, the session waits on the buffer
> busy waits wait event.
> [...]
> If the majority of the buffer busy waits wait events are centered on data
> segment headers (that is, the table or index segment header and not the undo
> segment header), this usually means some tables or indexes in the database
> have high segment header activities. Processes visit segment headers for two
> main reasons-to get or modify the process FREELISTS information and to
> extend the high watermark (HWM).
> [...]
> </quote>
>
>
> Regards,
> Dimitre
Received on Wed Mar 22 2006 - 22:27:14 CST