Re: Buffer busy wait clarification

"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 - 06:20:55 CST