Re: How many people here rebuid index regularly?

Commentary embedded.
On Oct 5, 11:26 am, Hasta <hasta..._at_hotmail.com> wrote:
> In article <1191581490.312563.10..._at_o80g2000hse.googlegroups.com>,
> hjr.pyth..._at_gmail.com says...
>
> > > We do rebuild indexes, because it was measured to improve
> > > performance on our system (9i).
>
> > > See the thread "Proving or Debunking the need for rebuilding"
> > > in this group for a report...
>
> > >http://groups.google.be/group/comp.databases.oracle.server/browse_thr...
> > > hl=fr&lnk=gst&q=+Proving+or+Debunking+the+need+for+rebuilding&rnum=1#
> > > 9e32aa3cf9282f81
>
> > > --- Raoul
>
> > Well, from what I read of that thread, you didn't measure very much
> > before, you weren't entirely certain on what to measure afterwards,
> > and it was all a bit vague and airy!
>
> Well, I cannot really agree, Howard
>
> As I recall, that was a very long, very controlled measurement effort,
> whose goal was to check whether our system did benefit from index
> rebuilds.
>

And that may be, however your posts relating your efforts appear disjointed and vague and throw that same light on your testing and findings..

> I really expected to see that index rebuilds were not needed.
>
> To my surprise, the test showed that the rebuilds were beneficial.
> Trouble is that while the tests show with certainty a very
> measurable change, I dont have data to know with certainty why.

I realise this would take time, but have you considered a treedump of the indexes before and after the rebuild:

alter session set evnts = 'immediate trace name treedump level <index object id>';

You should see considerable differences in the dump files for a given index if the rebuild of it improves performance. And those file pairs with differences would indicate the indexes upon which you should concentrate your rebuild efforts.

>
> The best hypothesis right now is :
> * Many of our queries are optimized with index range scans.
> * Some important indexes do index monotonically increasing fields -
> mainly sequence and date primary keys.
> * The delete pattern is such that 9 out of ten rows with consecutive
> dates or sequences are deleted, leaving the corresponding indexes
> with non empty but sparsely populated leaf blocks.
> - Therefore range scans take longer
>
> As I recall, and this is only an hypothesis... All the
> information I have is in the referenced thread - albeit in
> bits and pieces, sorry.
>
> About repeatability... For various reasons it has happened
> that some of our customers have been running without rebuilds
> for a few weeks. And complained to our technical support that
> the system was becoming slow. The effect I have measured is
> therefore very real, experienced by actual users in the field.
>
>
>
>
>
>
>
> > But that's standard fare in the 'I know my index rebuilds are
> > beneficial' industry: no-one actually every properly measures a darn'd
> > thing!
>
> > I don;t mean that in a critical way, by the way. More a frustration
> > thing: everyone has anecdotes, few have facts and figures.
>
> > Anyway, even if you happen to be of that rare breed, someone for whom
> > an index rebuild genuinely produces tangible, repeatable and
> > measureable benefits, fine: I have no problem with that. So long as
> > you, some third party with a bouffant hair-do or some poor innocent
> > newbie passing over this thread months or years from now understands:
> > that is not usual and it doesn't provide the basis for a simplistic
> > rule of thumb of the sort that says, "If Height>= 4 then rebuild;"
>
> Sure. I am not claiming anything more than what I have observed
> *on our system* - and not suggesting anything more than "measure your
> system"- Hide quoted text -
>
> - Show quoted text -

David Fitzjarrell Received on Fri Oct 05 2007 - 14:35:16 CDT