Re: Proving or Debunking the need for rebuilding

From: Ben <balvey_at_comcast.net>
Date: 16 Nov 2006 05:29:59 -0800
Message-ID: <1163683799.175039.157090@f16g2000cwb.googlegroups.com>

Richard Foote wrote:
> <hasta_l3_at_hotmail.com> wrote in message
> news:1163615993.616586.162730_at_h54g2000cwb.googlegroups.com...
> > Richard Foote wrote :
> >
> >> <hasta_l3_at_hotmail.com> wrote in message
> >> news:1163528916.396146.307250_at_m7g2000cwm.googlegroups.com...
> >> > hpuxrac wrote :
> >> >
> >> >> hasta_l3_at_hotmail.com wrote:
> >> >> > >
> >> >> > > What conclusions did you reach based on your approach?
> >> >> >
> >> >> > On my system - and I stress : on my system
> >> >> >
> >> >
> >> >> What selects saw a 30% improvement? Was that in response time?
> >> >> Any OLTP application that is retrieving 4000 rows repeatedly is going
> >> >> to
> >> >> vary in response time anyway based on transient conditions, at least
> >> >> in
> >> >> my experience. Again the phrase "An occasional" makes me wonder if
> >> >> this is based on test cases or what exactly.
> >> >
> >> > Yes, the test case is well specified. It happens that we open lists,
> >> > and the underlying select goes through a huge index but answers
> >> > only 4000 rows or so...
> >> >
> >> > The time to open a list - to execute the test plan - is "occasionaly"
> >> > important, probably when the cache has to be filled. We are here
> >> > talking of times on the order of 20 secs. When the test is retried,
> >> > response time is of course dramatically better.
> >> >
> >> > Now, the 30% was measured between an "occasional" slowdown
> >> > before rebuild, and an "occasional" slowdown after the rebuild.
> >> >
> >> > I realize that the conditions varies wildly. I'm reporting the notes I
> >> >
> >> > took.
> >>
> >>
> >> However, my money is on it having nothing to do with just an index
> >> rebuild
> >> ...
> >>
> >
> > Could very well be, Richard. Perhaps you are reading too much in
> > my words ? (which were admittedly very terse in the first report :-)
> >
> > I was actually very careful not to voice any conclusions till now.
> > For the record, the conclusions are :
> >
> > 1) Did the rebuild impact the display of screens ? NO.
> > 2) Did the rebuild impact the night job ? YES, definitely yes.
> > 3) Did the rebuild impact opening lists ? Definitely a suspect area.
> >

>

> Hi Raoul

>

> Now my suspicious mind is cast towards this night job that "YES, definitely
> yes" improved after index rebuilds.

>

> You mentioned earlier that this night job dropped from 5 hours down to 2
> hours. And all from some index rebuilds. Again this is an extraordinary
> result, one that I would have thought warranted further investigation if
> only as previously mentioned by hpuxrac to determine exactly what these
> magical indexes might be.

>

> Again though I make the important point that if such a reduction in timings,
> an impressive *60%* was based purely and simply on these index rebuilds,
> then it somewhat suggests that index block reads comprise well over 60%
> (70%, 80%, 90%+ ?) of overall reads. If your use the RBO as you mentioned
> earlier, then it's unlikely that the execution plan would have changed after
> the rebuilds.

>

> This is an extremely puzzling scenario as index reads usually comprise only
> a small fraction of overall reads, even for usually inefficient indexes.
> This suggests in this case that you have a ratio of more than one index
> block for each and every index entry the SQL accesses. This in turn
> suggestions these night jobs are accessing large numbers of empty index
> blocks as part of the index scans being performed.

>

> It's really unfortunate you don't keep before and after statistics of these
> index rebuilds as the results would be most illuminating. As would traced
> execution results of the bad/good night jobs. It would also be interesting
> to know what kind of DML is performed on these indexes/tables, if there is
> significant delete activity (and I mean really really significant delete
> activity without subsequent inserts), if the indexes values are
> monotonically increasing or whether any "unusual" activity is performed on
> these indexes/tables. It would also be interesting to know what the SQL
> does, whether full index scans are performed, whether it processes
> particular "areas" of data (such as the older historical stuff) that might
> be referenced by "sick" (empty, still linked) portions of an index.

>

> It would also be interesting to know just how well tuned these night jobs
> are.

>

> It's also puzzling why with such incredible results, one lets the jobs get
> so bad as to take 2 1/2 times longer than usual before rebuilding the
> indexes again ?

>

> I'm sorry Raoul if I sound somewhat sceptical, but for index(es) to get so
> inefficient as to increase processing times by a factor of 2 1/2 times, it's
> a very very unusual scenario.

>

> Again with so little information, it's totally impossible to know either way
> but again my money is on these dramatic improvements having nothing to do
> (directly) with index rebuilds.

>

> Cheers

>
> Richard

Hi Richard,

You said above,
" It's really unfortunate you don't keep before and after statistics of these
index rebuilds as the results would be most illuminating " Could you expand on what type of statistics you're talking about?

Thanks,
Ben Received on Thu Nov 16 2006 - 07:29:59 CST