Re: Proving or Debunking the need for rebuilding

<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 Received on Thu Nov 16 2006 - 04:30:44 CST