Re: best pctfree for read only indexes is 3?

hi Steve:

Thanks, that's the answer what I want:)

On 5/10/06, Steve Adams <steve.adams_at_ixora.com.au> wrote:
>
> 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?
>

--
Eagle Fan

Oracle DBA

--
http://www.freelists.org/webpage/oracle-l