Re: Free space not re-used

Hi Olivier,

The impact of the PCTUSED setting is that when the block on the head of the freelist cannot accomodate the new row, Oracle can unlink it from the freelist if and only if it is fuller than PCTUSED.

If Oracle were to unlink blocks with less free space than PCTUSED, then that block would be most unlikely to ever end up on a freelist again, and once all the rows had been deleted the block would be "lost".

Setting PCTUSED enables blocks with relatively little free space to be unlinked from the freelist, so that blocks with more space further down the freelist can be reached. This is because Oracle will only look at the first 5 blocks by default before formatting a new block above the highwater mark. The number blocks is configurable via the _walk_insert_threshold parameter. There is also a _release_insert_threshold parameter which also defaults to 5. This limits the number blocks that will be unlinked, rather than just skipped (walked). So in the worst case, an insert may unlink 4 blocks and walk 5 or vice versa before going the highwater mark.

Regards,
Steve Adams

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

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

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

On Mon, 21 Feb 2000 00:11:00 +0100, Olivier <oberco_at_club-internet.fr> wrote:

>No Mark, I did'nt think about this, but as you said, now I will take care of it
>!
>If I have well understand explanations of Steve, the table freelist has its 5
>firsts free blocks
>not enought large to be used so as Oracle search only the 5 first blocks in the
>freelist, it doesn't
>find one so it allocate one extent. Is it right Steve ?
>But I don't understand very well the impact of the pctused in this case. What a
>smaller pctused (50%, 60%)
>would be better and resolve the problem of this 5 blocks?
>Sorry if I have some problem to understand with your clear explanations but I'm
>newbie in this part!
>However, thanks to everybody for your help.
>
>Olivier
>
Received on Tue Feb 22 2000 - 22:01:32 CST