Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: When to rebuild the index?

Re: When to rebuild the index?

From: Chirag DBA <chiragdba_at_gmail.com>
Date: Tue, 6 Sep 2005 15:37:03 -0400
Message-ID: <1a36296005090612371dcff509@mail.gmail.com>


Agreed...!!!
 U know some staff and managers in IT will ask this question first when they feel a slight down in performance. Some will see the all kind of ratios and then suggest to create statistics and rebuild the indexes.  OEM halps them a lot to see all these things. Pictorial view, wow...  I need to make them feel that " Oh Yeah, That can work out. Let me try....".
 n believe me, with no change or 1 - 2% change they are so happy to go to bed happily.
 That definitely helps. (politically..)

 On 9/6/05, Goulet, Dick <DGoulet_at_vicr.com> wrote:
>
> Jared,
> I agree. Most indexes require little if any attention. Those that go
> through rapid growth are worth attention & 99% of the time something in the
> application is the root cause. We rebuild indexes periodically to placate
> damagement (read that as Damage Management) so that the end user feels the
> DBA staff is doing something to handle their pain. Sometimes a little
> "false" information (politically correct term for a lie) is a good thing,
> sometimes not. The only good thing I've found comes from a rebuild exercise
> is that you get back some space in your index tablespace, somewhere between
> 1 and 5 percent with a definite bias towards the former. In general it's
> normally an exercise in futility, desperation, or both.
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Jared Still
> *Sent:* Tuesday, September 06, 2005 2:46 PM
> *To:* chiragdba_at_gmail.com
> *Cc:* Allen, Brandon; Oracle-L Freelists
> *Subject:* Re: When to rebuild the index?
>
> I don't have time to read the entire article, so I skipped down to the
> summary:
>
>
> -
>
> *Academic approach* - Many Oracle experts claim that indexes rarely
> benefit from rebuilding, yet none has ever proffered empirical evidence that
> this is the case, or what logical I/O conditions arise in those "rare" cases
> where indexes benefit from rebuilding.
> -
>
> *Pragmatic approach* – Many IT managers force their Oracle DBAs to
> periodically rebuild indexes because the end-user community reports faster
> response times following the rebuild. The pragmatists are not interested in
> "proving" anything, they are just happy that the end-users are happy. Even
> if index rebuilding were to be proven as a useless activity, the Placebo
> Effect on the end-users is enough to justify the task.
>
>
> Re the Academic approach: much evidence has been supplied regarding this.
> I am not going to attempt to rehash it. Look on AskTom. Search for Richard
>
> Foote's presenation on index internals.
>
> Re the pragmatic approach: If the users just need a placebo, then lie to
> them.
> Tell them the indexes were rebuilt and they will be happy. Speed increases
> do to index rebuilding in an OLTP system are only temporary. It is
> entirely
> possible that rebuilding index will hurt performance as the index blocks
> re-split due to inserts.
>
> Oops, there I go, rehashing.
>
>
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
> 11+ years of trying to appear to know what I'm doing.
>
>
>
> On 9/6/05, Chirag DBA <chiragdba_at_gmail.com> wrote:
> >
> > Hi,
> > http://www.dba-oracle.com/art_dbazine_idx_rebuild.htm
> > Here is the very good document for indexes.
> > I just found that.
> > Regards - Chirag
> >
> > On 9/6/05, Chirag DBA <chiragdba_at_gmail.com > wrote:
> >
>

>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Sep 06 2005 - 14:40:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US