RE: Index rebuilding

I think that before spending a great deal of time trying to figure out exactly why the CBO switches from using the index to full scanning that the manner in which the statistics are being updated should be examined.

Analyze vs. dbms_stats: table only, index only, or both? You should always double check the obvious before putting forth effort to delve into the nitty-gritty.

HTH -- Mark D Powell --

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mark W. Farnham Sent: Friday, November 12, 2004 9:10 AM
To: breitliw_at_centrexcc.com; steve_at_trolltec.co.uk Cc: oracle-l_at_freelists.org
Subject: RE: Index rebuilding

Wolfgang's approach is a clear scientific approach to determining what is going on.

One possible shortcut to carefully tracking it next time: Are you right on the bubble from one depth to another?

If so, then it is entirely possible that a rebuild makes the index "better" for a short period of time but minor insert/update/delete even at a steady state net size make the next analysis a one deeper and the cost is won by an alternate path.

If that is the case, and if things slow down a lot when the plan tips, then you need to figure out whether some cost is being mis-described to the optimizer. If things don't slow down a lot, then the new plan may in fact be the better plan now that the index is deeper. If the steady state of your index is very near a depth change, you have to figure out whether your operational schedule and machine cost to rebuild the index is worth putting the rebuild on a regularly scheduled cycle. Usually it is not, but that is something you figure out from actuals. Now I've diverged from the general case to a shot-in-the-dark possible example. Wolfgang's approach will terminate in an accurate answer. Sniffing around for hunches can go on forever, but sometimes terminates very quickly. Balancing your approach with a quiver of likely quick check hunches is not a problem as long as you remember to fall back on the scientific approach when you're stumped and you don't spend an uneconomic amount of time on hunches. As the CBO is improved, there is less and less chance that it is making insane decisions.

Regards,

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Wolfgang Breitling Sent: Friday, November 12, 2004 7:10 AM
To: steve_at_trolltec.co.uk
Cc: oracle-l_at_freelists.org
Subject: Re: Index rebuilding

I suppose you analyze religiously between the date you rebuilt the index and the date the CBO decides not to use the index anymore? Provided this assumption is correct, then a) can you post the evolution of the statistics (table, index and all relevant columns, i.e. columns appearing in predicates) b) what happens if you DON"T analyze after having rebuilt the index? Does the CBO still switch to a full scan after a few days?

At 03:45 AM 11/12/2004, Steve Jelfs wrote:
>What happens is that we build the index and queries use the
>index. Then, after a couple of days the CBO reverts to full table
>scans/. Re-analysing the table and index does not change anything but
>re-building the index does.

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l