Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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 <steve.adams_at_ixora.com.au>
Date: Wed, 10 May 2006 14:35:45 +1000
Message-ID: <44616DA1.8070909@ixora.com.au>


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
@   http://www.ixora.com.au/         - For DBAs
@   http://www.christianity.net.au/  - 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 <steve.adams_at_ixora.com.au> 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?
--
http://www.freelists.org/webpage/oracle-l
Received on Tue May 09 2006 - 23:35:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US