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: Richard Foote <richard.foote_at_bigpond.nospam.com>
Date: Tue, 14 Nov 2006 13:41:30 GMT
Message-ID: <eIj6h.66078$rP1.32370@news-server.bigpond.net.au>


<hasta_l3_at_hotmail.com> wrote in message news:1163485482.971252.200030_at_f16g2000cwb.googlegroups.com...
> hpuxrac wrote :
>
>> hasta_l3_at_hotmail.com wrote:
>> > I checked exactly this last month. In retrospect, I would use
>> > this methodology :
>> >
>> > 1. Setup a load test environment duplicating system activity
>> > 2. Let the system run for 15 days, without index rebuild.
>> > 3. Time a small representative set of transactions, including
>> > - Most common querying user action(s)
>> > - Most common updating user action(s)
>> > - Nightly batch jobs
>> > 4. Rebuild all indexes
>> > 5. Time again
>> > 6. Run one week with nightly rebuilds
>> > 7. Time again
>> > 8. Believe your data
>>
>> 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)

Hi Raoul

A 30% improvement appears to be an extraordinary improvement for the return of 4000 rows when due to only a humble index rebuild. Unfortunately, we have no information on the complexity of the queries involved, number of tables involved and what 4000 rows actually means in terms of the work required by the query.

However it does suggest that somewhat more than the 30% (40%, 45%, 50%+ ?) of the "work" performed by Oracle is reading of index blocks for an index rebuild to improve things by 30% (however this "30%" is measured).

Eg. Assuming a terrible clustering factor and assuming only *1* index entry on average per index block, approximately 50% of the work of an index range scan would attributed to the index. Therefore, the index would need to be in this order of disarray for such an improvement to be possible by a rebuild. Note just *2* index entries per index block and a poor CP would result in the index performing just 33% of the work, in which case an index rebuild could only improve things by 33% at very best. And of course most indexes would have significantly more than 2 entries per index block ...

Therefore it suggests that the index density was truly awful before the rebuild, the execution plan has dramatically changed, the execution plan involved a index full scan as a significant part of the plan or your 30% improvement is not accurately measured (how was it measured btw ?).

Would be curious to know which of the above scenarios is the correct one ?

Cheers

Richard Received on Tue Nov 14 2006 - 07:41:30 CST

Original text of this message

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