Re: best pctfree for read only indexes is 3?

hi Steve:

Very good explanation.

I have another question here.

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?

Thanks

On 5/10/06, Steve Adams <steve.adams_at_ixora.com.au> wrote:
>
> Hi Juan,
>
> 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.
>
> @ Regards,
> @ Steve Adams
> @ http://www.ixora.com.au/ - For DBAs
> @ http://www.christianity.net.au/ - For all
>
>
>
> 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
>
>
>

--
Eagle Fan

Oracle DBA

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