Re: Free space not re-used

You wouldn't care to elaborate on the different problem would you? The buffer busy waits?

• Dc.

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.
>
>Assuming default INITRANS you have 1958 bytes available in the data area
>per block. Each row takes an extra 2 bytes in the row directory, so 3
>average rows would require 1956 bytes. It is easy to imagine that the
>some rows being deleted are smaller than average. So let us say that 1%
>of the rows being returned to the freelist only have 600 bytes free.
>When searching for a block from the freelist Oracle will look at no more
>than the first 5 blocks (by default). Any block that cannot fit the new
>row, but is still below PCTUSED, is skipped over stays on the head of
>the freelist. In this case, the delete operation will return all the
>blocks from which a row has been deleted to the head of the freelist,
>and the inserts will begin to used those blocks. However, you will soon
>have 5 of the blocks with only 600 bytes of free space on the head of
>the freelist. This means that most inserts will scan these 5 blocks,
>skip them, and get a fresh block.
>
>This is part of why it is recommended that there be greater than 1
>average row between PCTUSED and PCTFREE. In your case, I would recommend
>firstly that you drop PCTUSED to 50%. That will enable the unusable
>blocks on the head of the freelist to be unlinked, and you will soon be
>using all the presently unusable free space. Long term, this will mean
>that two rows need to be deleted from any block before it goes on the
>freelist, but overall this will be a more space efficient solution.
>Then, as soon as you have opportunity, this database should be rebuilt
>with an 8K or 16K block size.
>
>Hope this helps,
>Regards,
>Steve Adams
>http://www.ixora.com.au/
>http://www.oreilly.com/catalog/orinternals/
>http://www.christianity.net.au/
>
>
>On Thu, 17 Feb 2000 22:24:54 +0100, Olivier <oberco_at_club-internet.fr>
>wrote:
>
>>Hi everybody,
>>
>>we have a strange problem on Oracle 7.3.4 (on open vms) :
>>for a table we do each day only insert and delete (of 20% of total rows), no
>>update.
>>We have noticed that the free space due to delete is not re-used by insert so
>>the table acquire new extent each day.
>>This table contain 1 long row but there is no chained rows
>>(an analyze of the table show : chained_rows = 0 and avg_row_len=650)
>>The db_block_size=2K (a little small, I know!)
>>And last point : pctfree=10 and pctused=90 so pctfree+pctused=100 (and perhaps
>>it's the problem)
>>So has anaybody an idea about our problem ? and what is the consequence to have
>>pctfree+pctused=100 ?
>>Thanks in advance
>>
>>Olivier
>>
>>
>
Received on Fri Feb 18 2000 - 17:20:24 CST