Re: When should one rebuild an index?

On 27.12.2008 23:53, Bob Jones wrote:
> "DA Morgan" <damorgan_at_psoug.org> wrote in message
> news:1230405015.761378_at_bubbleator.drizzle.com...

>> Bob Jones wrote:
>>> "DA Morgan" <damorgan_at_psoug.org> wrote in message 
>>> news:1230357209.294829_at_bubbleator.drizzle.com...
>>>> Bob Jones wrote:
>>>>> <shweta.kaparwan_at_googlemail.com> wrote in message 
>>>>> news:3bddc9d2-d04f-4525-9703-9c74ccd33d9e_at_h20g2000yqn.googlegroups.com...
>>>>>> Hi
>>>>>>
>>>>>> We can use
>>>>>> ANALYZE INDEX <index> VALIDATE STRUCTURE
>>>>>> command on the affected indexes - each invocation of this command
>>>>>> creates a single row in the INDEX_STATS view.
>>>>>> Thus determining the indexes which are good candidates for rebuilding.
>>>>>>
>>>>>> BUT my doubt  is that if,
>>>>>> we use dbms_stats to gather the statstistics for CBO using cascade
>>>>>> option. Then if
>>>>>> ANALYZE INDEX <index> VALIDATE STRUCTURE  is ran , that what will be
>>>>>> fate of the stattistics gathered using dbms_stat for index( cascade
>>>>>> option).
>>>>>>
>>>>>> Also, analyze command is deprecated now. So is there any other way to
>>>>>> find out the indexes which needs rebuilding.?
>>>>>>
>>>>> INDEX_STATS is populated by ANALYZE only. Your CBO data collected by 
>>>>> DBMS_STATS are safe.
>>>> The CBO data is safe but what you are suggesting as criteria irrelevant.
>>>>
>>> What criteria?
>> I will state it clearly.
>>
>> The data collected by ANALYZE does not lead to a conclusion as to
>> whether an index should be rebuilt. In other words ... the fact that
>> CBO data is not destroyed is irrelevant. Time is being spent, CPU and
>> I/O wasted, producing a metric that is not a relevant criteria.

IMHO your notion of relevance here is a bit too formal - while part of the OP's question was indeed whether DBMS_STATS data is safe when doing ANALYZE the more important question he asked was how to determine whether an index is eligible for rebuilding. If the answer is "not by using ANALYZE" it is completely irrelevant whether or not ANALYZE destroys DBMS_STATS data.

>> This is not different from the same mythologist promoting Buffer Cache Hit 
>> Ratios. Yes you can generate a value. A numeric value. But valid
>> conclusions can not be drawn from that numeric value. Only, perhaps,
>> about the person that suggested making a decision based upon it.

Well, I guess Daniel means that the output of ANALYZE (which is deprecated btw., this has also been mentioned already) is meaningless without further information much the same way as a high or low BCHR is meaningless without further info. A high BCHR can indicate that your application is doing too much useless work (wasting CPU cycles doing table scans in buffer cache) and a low BHCR can indicate that your app does not have proper indexes (and thusly needs to do FTS for objects larger than BC). BCHR of course is not irrelevant for performance because a change most likely correlates with changed performance - but it does not help you find out whether performance is ok or not and what's wrong in case not. So it's relevant but useless.

Whatever numbers you get from ANALYZE - they are only a snapshot of the current index structure. Without further information about the data, change nature and frequency it is pretty useless because your perfectly rebuilt index might be back to before in less time than you needed for the rebuild rendering rebuilding of this index useless to harmful.

> Perhaps, instead of looking at stats, we just need an Ace Director to
> orchestrate DBAs what to use or not to use in all situations.

Well, delegation is a valid approach - there are even people getting paid for doing consultancy... ;-)

Cheers

        robert Received on Sat Dec 27 2008 - 17:35:06 CST