Re: best pctfree for read only indexes is 3?

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