Re: Free space not re-used

Hi Doug,

If you have a PCTFREE and PCTUSED too close together, most inserts and most deletes will require the target block to be either taken off or added to a freelist. That involves a change to the segment header or freelist group block that stores the freelist headers. If there is high concurrency of inserts and deletes on the table, they will be single-threaded on the buffer lock for the buffer containing the current mode copy of that freelist header block. This will be shown as 'buffer busy waits' in V$SYSTEM_EVENT. V$WAITSTAT will classify these waits as against 'segment header' class blocks, or 'free list' class blocks, depending on whether the table has multiple freelist groups.

Regards,
Steve Adams

http://www.ixora.com.au/

http://www.oreilly.com/catalog/orinternals/

http://www.christianity.net.au/

On Fri, 18 Feb 2000 23:20:24 GMT, dcowles_at_i84.net (Doug Cowles) wrote:

>You wouldn't care to elaborate on the different problem would you?
>The buffer busy waits?
>
>On Fri, 18 Feb 2000 00:17:43 GMT, steve.adams_at_ixora.com.au (Steve
>Adams) wrote:
>
>>Hi Oliver,
>>
>>Having PCTFREE + PCTUSED = 100 does not cause free space to go unused,
>>as in your case, but it does increase the risk of 'buffer busy waits'
>>against the segment header block if the table is modified by multiple
>>concurrent sessions. However, that is a different problem.
>>
>>[snip]
Received on Tue Feb 22 2000 - 22:01:02 CST