Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 29305 invoked from network); 10 May 2006 00:15:34 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 10 May 2006 00:15:31 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 063F131B0DA; Tue, 9 May 2006 23:54:11 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 17317-04; Tue, 9 May 2006 23:54:10 -0400 (EDT) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 532EE31A62F; Tue, 9 May 2006 23:54:10 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 09 May 2006 23:53:30 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5287231B07F for ; Tue, 9 May 2006 23:53:30 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 16922-09 for ; Tue, 9 May 2006 23:53:30 -0400 (EDT) Received: from nz-out-0102.google.com (nz-out-0102.google.com [64.233.162.195]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C6ABF31A627 for ; Tue, 9 May 2006 23:53:27 -0400 (EDT) Received: by nz-out-0102.google.com with SMTP id n29so1501851nzf for ; Tue, 09 May 2006 20:53:20 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=ku5e1jjYdhypapa/MySJVV7MKwHFAloqVlJh6pzyfOndEQPRIiBfhMLnybUkicEQJ1HOo719JX9Xn8SbXxa687uN8I6x4zC3bqtO8EVH6Z4MVe2n17PECFRcnhNzoSaeBCG2PokvHQG2tqH25PBCuc6KFXxWirgDoj8rVp3AqIM= Received: by 10.37.21.24 with SMTP id y24mr26460nzi; Tue, 09 May 2006 20:53:20 -0700 (PDT) Received: by 10.36.119.20 with HTTP; Tue, 9 May 2006 20:53:20 -0700 (PDT) Message-ID: <4415a5ed0605092053l20b59fb6k187e55f858110045@mail.gmail.com> Date: Wed, 10 May 2006 11:53:20 +0800 From: "eagle fan" To: steve.adams@ixora.com.au Subject: Re: best pctfree for read only indexes is 3? Cc: oracle-l@freelists.org In-Reply-To: <44615648.7060900@ixora.com.au> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_6462_10864381.1147233200356" References: <44615648.7060900@ixora.com.au> X-archive-position: 34445 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: eagle.f@gmail.com Precedence: normal Reply-to: eagle.f@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at avenirtech.net ------=_Part_6462_10864381.1147233200356 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline hi Steve: Very good explanation. I have another question here. If we set the pctfree=3D0 when building the index and inittrans=3D2. 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 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 ------=_Part_6462_10864381.1147233200356 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline hi Steve:

Very good explanation.

I have another question here= .

If we set the pctfree=3D0 when building the index and inittrans=3D= 2. What happends when more than two sessions modify the same leaf block sim= ultaneously?

Shoud the third session waitting or something else happen?

T= hanks

On 5/10/06, Steve Adams < steve.adams@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 PCT= FREE setting protects you from the risk of
ITL waits and row migration. = However, those problems do not affect
indexes, so no block space needs t= o 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 REBUI= LD. A
zero setting maximizes data density, and thus range scan performan= ce,
but will cause a short-term spike of block splits if the index is
su= bject to updates, or inserts that are not monotonically increasing
from = the maximum key value. Block splits increase redo generation and
can res= trict concurrency. They also degrade data density, of course.

In general, your objective is to optimize data density in the mediu= m
term, without a noticeable short term performance impact due to block<= br>splits. 10 is probably a better default than 3, but your value of 3
would often be appropriate.

@   Regards,
@   = Steve Adams
@   http://ww= w.ixora.com.au/         - For D= BAs
@   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 pct= free
> But for read-only indexes pctfree 3 is the best size?

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





--
Eagle Fan

Oracle DBA ------=_Part_6462_10864381.1147233200356-- -- http://www.freelists.org/webpage/oracle-l