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