Re: Why a high INSERT activity requires to set a low PCT_USED ?

Actually, it's not whether PCTUSED (there's no underscore) is high or not that causes freelist contention issues.

It's whether it's set too close to PCTFREE (ditto).

The default settings are 10 for PCTFREE and 40 for PCTUSED.

10% free = 90% used, and 40% used = 60% free, so comparing like-with-like, 90-40=50%, or 60-10=50%: either way you work it, there's a 50% gap between the two parameters by default. In other words, Oracle wants there to be half a block's amount of space between the two.

When you degrade that gap, that's when freelist management issues can arise. Set PCTUSED to 85, for example, and PCTFREE to 5, and you now have a mere 10% gap, and that's likely to mean that every delete puts the block back on the freelist, and every insert takes it back off. There'll be a fist-fight for the head of the freelist as a result. The same thing will happen if you set PCTFREE to 70 and PCTUSED to 20.

Strictly speaking, in other words, the absolute values for PCTUSED and PCTFREE are less of significance (for freelist management issues) than the implied gap between them. Of course, the absolute values *are* important in their own right (too small a PCTFREE and updates cause row migration; too small a PCTUSED and you're threatened with a mostly empty table, and lengthy full scans as a result).

~QM

"Chuck" <chuckh_at_softhome.net> wrote in message news:Xns93AC7DFD668B9chuckhsofthomenet_at_130.133.1.4...
> PCT_USED determines when a block goes *ON* the freelist. Not when it
> comes off. If the actual % used in the block falls below the PCT_USED
> threshold, it gets added to the freelist. On a table with a lot of
> deletes and inserts into the same block(s), setting a high pct_used can
> cause freelist managment overhead. That said, I have not personally seen
> freelist management cause significant delays. That's doesn't mean it
> doesn't happen though.
> --
> Chuck Hamilton
>
>
> Brian Peasland <oracle_dba_at_remove_spam.peasland.com> wrote in
> news:3F02EDEF.43C2ADB_at_remove_spam.peasland.com:
>
> > Where did you read that?
> >
> > PCT_USED determines when a block leaves the freelist, or list of blocks
> > that can accept a new row of data. If PCT_USED is low, then this block
> > leaves the freelist earlier than a table with a higher PCT_USED. If you
> > have a lot of inserts, then one would probably want to reduce the
> > overhead of moving lots of blocks off the freelist. So a high PCT_USED
> > should be warranted. But if there are lots of concurrent transactions
> > performing the inserts, then it is possible that multiple transactions
> > inserting into the same block can cause more overhead in dealing with
> > all of the read consistency that may accompany the operations. So IMO,
> > YMMV. I can see both sides of the coin.
> >
> > HTH,
> > Brian
> >
> > Spendius wrote:
> >>
> >> (that's what I read somewhere)
> >> Why should a high PCT_USED have a negative impact on
> >> performances in a table that undergoes lots of INSERTs ??
> >>
> >> Thanks.
> >> Spendius
> >
Received on Wed Jul 02 2003 - 14:32:59 CDT