Your having a pctused of 90 and pctfree of 10 is probably making this worse.
After one delete a block will most likely be placed on the free list , if
this was a small record you will have just a little over 10% free for a new
record. If the next insert is for a record that is larger than this, the
events described below happen.
You prob put the pctused high to make it use as much of a block as possible
but this is most likely having the opposite effect.
I would suggest a pctfree of 10% and pctused of 70 or 80. ( if you have a
lot of row chaining consider 20/80 )
This will ensure that there is room for a larger record to be added before
the block is placed on the freelist.
Jonathan Lewis wrote in message
<949529819.25269.0.nnrp-10.9e984b29_at_news.demon.co.uk>...
>
>Tables with LONGs in them tend to be major pains.
>Even though your average row length is only 895
>bytes (I assume you have 16K blocks by the
>way), is it may possible that a significant number of rows
>are much longer - say one in 12 is up to 4K ?
>
> ** Just seen your follow up post - my guess
> ** about the block size was wrong, but the
> ** general theory still applies.
>
>I haven't checked the following notes recently, but
>it applied to earlier versions of Oracle and may
>still be true.
>
>When you try insert a row into a 'pre-used' block on
>the free list, if it is too long then Oracle removes that
>block from the free list and tries the next block - for a
>total of 5; after 5 discards Oracle bumps the high water
>mark to get some clean blocks, and does the insert
>there.
>
>Therefore - if you have the odd row of 4K, and the free
>space in your free block is less than 5.5K (the 4K
>of the row plus the 10% of a 16K block) the block
>is bumped off the free list. It wouldn't take many
>every long rows to cause lots of blocks to have
>a 25% wastage in them.
>
>
Received on Thu Feb 03 2000 - 14:17:10 CST
