From oracle-l-bounce@freelists.org Fri Nov 12 08:06:48 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id iACE6lw25075 for ; Fri, 12 Nov 2004 08:06:47 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id iACE6ka25068 for ; Fri, 12 Nov 2004 08:06:47 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C3E5072CA13; Fri, 12 Nov 2004 09:13:02 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 15156-52; Fri, 12 Nov 2004 09:13:02 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A70C272CB57; Fri, 12 Nov 2004 09:13:00 -0500 (EST) From: "Mark W. Farnham" To: , Cc: Subject: RE: Index rebuilding Date: Fri, 12 Nov 2004 09:09:44 -0500 Message-ID: MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 In-Reply-To: <6.1.0.6.2.20041112050457.03244650@pop.centrexcc.com> X-archive-position: 12178 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: mwf@rsiz.com Precedence: normal Reply-To: mwf@rsiz.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Wolfgang Breitling Sent: Friday, November 12, 2004 7:10 AM To: steve@trolltec.co.uk Cc: oracle-l@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 -- http://www.freelists.org/webpage/oracle-l