Re: Free space not re-used

In article <38ac84ec.61187442_at_news.eagles.bbs.net.au>,   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
> >
> >

Two question for Oliver.

Do you have a column of type long in this table. If you do it seems to me that the manual used to say something to the effect that inserts with long columns go to unused blocks. I do not remember the details but I have seen this type of behavior when a long was involved.

My other question is when do you do the commit? If you do not do it after the delete but before the insert then this may also have an effect on the space allocation. I know it has an effect with indexes. If you delete all the rows within an index block the block goes back on the free list. If you reinsert the same rows after the delete without a commit you take an extent, but if you commit between the delete and insert no extent is taken.

I would ajust the pctfree and pctused such that the sum of the two was equal to no more than 100 - an average row length.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Feb 18 2000 - 14:19:53 CST