Re: When should one rebuild an index?
From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 28 Dec 2008 13:16:08 -0800
Message-ID: <1230498966.930889@bubbleator.drizzle.com>
>
> 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.
>
>
> 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.
>
>
> Well, delegation is a valid approach - there are even people getting
> paid for doing consultancy... ;-)
>
> Cheers
>
> robert
Date: Sun, 28 Dec 2008 13:16:08 -0800
Message-ID: <1230498966.930889@bubbleator.drizzle.com>
Robert Klemme wrote:
> 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. >> >> It doesn't look like OP has reached any conclusion yet. He was merely >> asking a question. I think my response was relevant to his question. >> Whether he should rebuild indexes based on the stats and other factors >> is not my call.
>
> 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. >> >> Not different in the way BCHR is also irrelevant to performance and it >> is just a number? That's fascinating.
>
> 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
Thakn you Robert ... very well said.
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Sun Dec 28 2008 - 15:16:08 CST