Re: Proving or Debunking the need for rebuilding

DA Morgan wrote:
> 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.

>
> Is it possible what the OP is seeing is a delayed block cleanout?

I think it is entirely possible. I'm wondering if simply doing a count(*) that uses the index in question, before performing each test, might shed light one way or the other. The idea is to visit all blocks and clean them.

jg

--
@home.com is bogus.  "Some of the old-timers grumbled: 'Where's the
mystique? Where is the aura of mystery around what we do?' The good
news is that we are not seeking to hire those people.  The CIA is after
the generation that has grown up on the Web. They were born with ear
buds in their ears. They are ADD and it is contagious. We need that
kind of talent here." - Tom McCluskey, CIA chief of hiring and
employee development