From oracle-l-bounce@freelists.org Mon Apr 25 12:05:40 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j3PH5eBk014373 for ; Mon, 25 Apr 2005 12:05:40 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j3PH5e4Z014367 for ; Mon, 25 Apr 2005 12:05:40 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 39854186669; Mon, 25 Apr 2005 11:03:15 -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 13426-10; Mon, 25 Apr 2005 11:03:15 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id AD0F918661B; Mon, 25 Apr 2005 11:03:14 -0500 (EST) From: "Michael Fontana" To: Subject: RE: rm RULE based optimizer != GOOD IDEA Date: Mon, 25 Apr 2005 11:01:19 -0500 MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit Thread-Index: AcVHYLKsxhC6TEx5Sw2rLd4TmX7Q9AAARjdgAABeQfAAkv/7QA== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 In-Reply-To: Message-Id: <20050425160125.EF9E718588F@turing.freelists.org> X-archive-position: 18955 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: mfontana@verio.net Precedence: normal Reply-To: mfontana@verio.net X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=0.2 required=5.0 tests=AWL autolearn=ham version=2.63 What we have found in our shop, where we have tons of purchased products and have just implemented Siebel's first CBO release, is that you have to get down to the nuts and bolts details of your problem queries. Once you do, you will often find that your statistics are flawed, which in turn causes a few (sometimes often critically) poor performing queries. Some examples - A batch process loads a million rows to a table but doesn't regenerate stats. Solution: Stats are stale - there is now an option in the optimizer to detect this and regenerate them. Cardinality stats are not granular enough for a large, key table. Solution: Run stats for specific tables with parms to selectively drill down and return the correct information for such tables. It may be a lot of work, but we've been able to run CBO for several major apps (both in-house and third party) including Siebel, SAP, Peoplesoft and Oracle Apps, without resorting to the RBO rule "copout". And we've had numerous development consultants try to sneak them in. We simply won't allow it. Over the long haul, it will cost still more time and money to repair the downstream damage.... -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Post, Ethan Sent: Friday, April 22, 2005 12:50 PM To: Lex de Haan; thomas.mercadante@labor.state.ny.us; Pete Sharman; Christian Antognini Cc: oracle-l@freelists.org Subject: RE: rm RULE based optimizer != GOOD IDEA If you head over to http://channel9.msdn.com you might be able to find a video (a month or two ago I think) which is a tour of SQL Server's automated testing facility, it is pretty impressive. -----Original Message----- From: Lex de Haan [mailto:lex.de.haan@naturaljoin.nl]=20 Sent: Friday, April 22, 2005 12:39 PM To: thomas.mercadante@labor.state.ny.us; Post, Ethan; 'Pete Sharman'; 'Christian Antognini' Cc: oracle-l@freelists.org Subject: RE: rm RULE based optimizer !=3D GOOD IDEA you don't have the faintest idea about the size and complexity of Oracle's regression tests, and the frequency they run with ... bug-free software is an utopia. kind regards, Lex. =20 --------------------------------------------- Visit my website at http://www.naturaljoin.nl --------------------------------------------- -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l