Re: Free space not re-used

In article <38b0d39c.25364763_at_news.remarq.com>,   dcowles_at_i84.net (Doug Cowles) wrote:
> 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
> >>

Steve can correct me if I am wrong, but I believe he is saying that by having pctfree + pctused = 100 you are increasing the odds that they will not be space left in a block, to allocate transaction work areas from, if multiple sessions attempt updates against rows in the block after it is filled.

You can help prevent that problem by preallocating transaction work areas via initrans when you create the table. --
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 Sat Feb 19 2000 - 14:59:33 CST