RE: Index rebuilding
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 12 Nov 2004 09:27:35 -0500
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Mark W. Farnham
Sent: Friday, November 12, 2004 9:10 AM
To: breitliw@xxxxxxxxxxxxx; steve@xxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Wolfgang Breitling
Sent: Friday, November 12, 2004 7:10 AM
To: steve@xxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
