Re: PCTFREE Vs PCTUSED

I think you need to consider these parameters in conjunction with the table's FREE LIST. When a table is created, all the blocks in all the extents are linked into the free list.

When you insert rows, Oracle uses the free list to determine which block to use.

When an insert pushes the block under the PCTFREE, the block is removed from the FREE LIST. No inserts can now touch this block, only updates and deletes

When a delete (or row-shrinking update) causes the block utilization to fall below the PCTUSED, Oracle places the block back on the FREE LIST. It is now available for inserts until it again falls under PCTFREE.

And on and on and on ....

Zhenming Wang wrote:

> I have a confusion about the two PCTFREE and PCTUSED parameters. For
> example, when the PCTFREE is set 20, and the PCTUSED is set 50, according to
> their definitions, I can insert rows unitil the block is 80% full because
> the remaining 20% is kept for updating for the rows in the block. Following
> I did some upgrading, which consumed some storage, let's say now the block
> is 90% full. I know I can't insert any rows at this level, so I deleated
> some rows, now the rows in the block take 30% of the block's total capacity.
> So according to the PCTUSED definition(below PCTUSED 50), I can insert new
> rows into the block. My question is: After I inserted some rows and did
> some upgrading, the block is now 70%. At this level, Can I continue to
> insert new rows into the block until it reaches 80%? It seems I can, because
> the block has not reached the 80%. But According to the PCDUSED, you cannot.
> You have to wait until you do some deleating rows until the block is below
> the PCTUSED 50. Because if I can insert new rows at a level higher than
> PCTUSED but below the 80% full, why I need the PCTUSED value? At this point,
> the two parameters seem contradict, as one say you can insert your rows
> until you reach 80%, another say you can't, because you are not below 50%
> you set for the PCTUSED.
> Zhenming
>
> zmwang_at_thenew.net
Received on Tue Aug 11 1998 - 10:28:29 CDT