Re: Proving or Debunking the need for rebuilding

From: Niall Litchfield <niall.litchfield_at_dial.pipex.com>
Date: Thu, 09 Nov 2006 20:05:27 +0000
Message-ID: <cqqdnXgkrc9EFM7YnZ2dnUVZ8s2dnZ2d@pipex.net>

Ben wrote:

> hpuxrac wrote:

>> Ben wrote:
>>> 9.2.0.5 EE AIX5L
>>>
>>> Ok so I do believe everyone when they say to quit rebuilding indexes,
>>> except for certain special cases ( like large purges on tables that
>>> will never grow again unless the moon is full and the wind blows
>>> eastward). I do understand that rebuilds can be harmful, BUT how do I
>>> prove one way or the other?

I think a particularly effective test proposed I think by Connor McDonald but which I've never seen results from is.

Create a test environment
Obtain representative workloads.
Compare TOTAL elapsed time for queries plus rebuilds under load vs queries under load. i.e over a cycle of two or three rebuilds how much time is saved?

>>>
>>> I want to create matrices on the before and after of an index rebuild.
>>> How? I don't want to blindly say, "Yes this index needs to be rebuilt"
>>> I want to show the performance gain or loss from a rebuild. Surely
>>> there is a way to do this. Do you simply anayze ... validate and
>>> capture the statistics on the indexes?
>>>
>>> Thanks for any ideas and help.
>> Check out Tom Kyte's site http://asktom.oracle.com this whole area is
>> documented extensively there.
>>
>> Also Jonathan Lewis's site.

> 
> 
> I guess I must have looked over the posts, but I have been to Tom's
> site extensively and Jonathan Lewis's.
> I've search benchmarking and matrix.. any other good keywords that you
> obviously know since you're suggesting the site?
>

Received on Thu Nov 09 2006 - 14:05:27 CST