Re: Buffer busy wait clarification

"Chintu" <agarwalp_at_eeism.com> wrote in message news:1143088033.953449.317430_at_j33g2000cwa.googlegroups.com...
> 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

A "freelist" is a pointer in the segment header, which points to the first block of a linked list of blocks in the segment (table, index, etc.) that are considered available for inserting new data.

A process that wants to insert a row reads the pointer and locates the block identified by the pointer, and tries to insert the row in that block. If this insert were to fill the block, the process then changes the pointer (freelist entry in the segment header) to point to the next block on the linked list.

Consequently, when multiple processes are inserting data, there will be moments when one process cannot access the segment header block because another processing is changing the freelist entry.

And, as another poster pointed out, the segment header block will be in the buffer whilst this is going on.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html