Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Proving or Debunking the need for rebuilding

Re: Proving or Debunking the need for rebuilding

From: <hasta_l3_at_hotmail.com>
Date: 14 Nov 2006 10:28:36 -0800
Message-ID: <1163528916.396146.307250@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
> >
> > - It took 40 minutes to rebuild the (150) hot indexes.
> > Further rebuilds took the same time +/- 15%
> >
> > - Time to perform most common querying user
> > actions was slightly improved. The measured
> > improvement was probably within the error margin.
> > An occasional 30% improvement was seen for medium
> > size selects (4000 rows)
>
> You said "The measured improvement was probably within the error
> margin.".
>
> To me at least that sounds like "no net gain"

Yes.

> but the phrasing "most common querying user actions" makes me nervous.
> Do you have specific test cases or are you just generalizing?

Hmm... I'm a french native speaker, and I used the word "common" with the meaning "most often used" - which may not be english ?

Yes, I used well-defined test cases, browsing through a pre-specified small set of very often used screens, and opening very often used lists.

I timed response time from the user point of view.

For screens, the response time is less than 3 secs, and difficult to measure accurately.

All in all, I would say rebuilding didn't change much, if anything for these screens.

> 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.

>
> >
> > - Time to perform a long night job dropped from five
> > to two hours.
>
> So what exact SQL related component of the long night job was impacted?
>
> It might be that only 1 of the indexes changed affected your "long
> night job" so perhaps your rebuilding of the 150 indexes could be
> targeted against only 1 index conceivably?

Certainly...

*That* drop was the real surprise. Now that the focus point is known, it might be interesting to investigate further...

>
> >
> > Unfortunatly, I didnt time update actions, not did I
> > check the rate of redo log switches, nor did I compare
> > the size of indexes.
>
> These are all important components. I think both Tom Kyte and Jonathan
> Lewis have noted that over time indexes want to / tend to shape
> themselves against relevant activity.

Yes. The test was an aside, unplanned effort. Only later - and with this first experience - did I realize how it could be improved.

> >
> > 9.2.0.5
> >
> > --- Raoul
>
> Thanks for being brave enough to post this!

No problem at all... Received on Tue Nov 14 2006 - 12:28:36 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US