Re: best pctfree for read only indexes is 3?

  • From: Steve Adams <steve.adams@xxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 10 May 2006 12:56:08 +1000

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?

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


Other related posts: