| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Proving or Debunking the need for rebuilding
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.
> >
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 :
(I am overstating for clarity. Please add the customary caution :-)
Point (2) would be the main focus point, and deserves further investigations to understand what's going on.
Point (3) should be handled afterwards, and requires a more controlled test, for example flushing systematically the cache before execution of the test case.
Going through your questions :
> This is all rather vague and ambiguous isn't ? I have no idea what
> "occasionally important" is suppose to mean for example.
Certainly, it is vague... It happens after the system has run for a while... Recall that I am on purpose simulating full activity.
> I refer back to my earlier post where I suggest it's rather difficult
> to attribute these figures (whatever they actually mean) back to
> rebuilding an index.
Certainly again... I wouldnt dare to attribute or disattribute in this specific case. The point of a global test is to pinpoint with few efforts the troublesome areas from the user point of view .
I find interesting that this exercise provided all three possible answers : yes, no, perhaps :-)
>
Yes.
> and again it suggests that the execution plan
> could differ between good and bad execution times.
We are using rule.
> Some information on the SQL, the execution plan (both good and bad if
> different), some table/index stats and some summary execution statistics
> (both good and bad) would be useful in determining why you're getting this
> "occasional" 30% improvement.
I know, I know... But the first step would be to make "occasional"
more
reproducible... Unfortunatly, I lack time right now, like everybody
I would guess :-)
Have a nice day, Richard
 |
 |