Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: best pctfree for read only indexes is 3?

Re: best pctfree for read only indexes is 3?

From: Steve Adams <>
Date: Wed, 10 May 2006 14:35:45 +1000
Message-ID: <>

Hi Eagle,

When the ITL of an index leaf block needs to grow, and there is not enough available free space, then the block just splits - there is never any need for user transactions to wait for a free ITL slot.

@   Regards,
@   Steve Adams
@         - For DBAs
@  - For all

eagle fan @ 10/05/2006 1:53 PM:

> If we set the pctfree=0 when building the index and inittrans=2. What
> happends when more than two sessions modify the same leaf block
> simultaneously?
> Shoud the third session waitting or something else happen?
> On 5/10/06, Steve Adams <> wrote:

>> A PCTFREE of zero is fine when creating or rebuilding read-only indexes,
>> and for a lot of other indexes too.
>> In tables an appropriate PCTFREE setting protects you from the risk of
>> ITL waits and row migration. However, those problems do not affect
>> indexes, so no block space needs to be reserved for those reasons at all.
>> For indexes, PCTFREE only comes into play when a block is formatted and
>> filled in a single operation, such as for a CREATE INDEX or a REBUILD. A
>> zero setting maximizes data density, and thus range scan performance,
>> but will cause a short-term spike of block splits if the index is
>> subject to updates, or inserts that are not monotonically increasing
>> from the maximum key value. Block splits increase redo generation and
>> can restrict concurrency. They also degrade data density, of course.
>> In general, your objective is to optimize data density in the medium
>> term, without a noticeable short term performance impact due to block
>> splits. 10 is probably a better default than 3, but your value of 3
>> would often be appropriate.
>> Juan Carlos Reyes Pacheco @ 10/05/2006 7:48 AM:
>> > I remember once someone suggested me to use 3 for pctfree
>> > But for read-only indexes pctfree 3 is the best size?
Received on Tue May 09 2006 - 23:35:45 CDT

Original text of this message