From oracle-l-bounce@freelists.org Fri Apr 22 12:27:10 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j3MHRAKa008692 for ; Fri, 22 Apr 2005 12:27:10 -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 j3MHR44Z008686 for ; Fri, 22 Apr 2005 12:27:04 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8B61A184B91; Fri, 22 Apr 2005 11:24:34 -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 28397-03; Fri, 22 Apr 2005 11:24:34 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 02952185A32; Fri, 22 Apr 2005 11:24:33 -0500 (EST) From: "Pete Sharman" To: "Post, Ethan" , "Christian Antognini" Cc: "oracle-l@freelists.org" , "Peter Ross Sharman" Subject: RE: rm RULE based optimizer != GOOD IDEA Date: Sat, 23 Apr 2005 02:16:50 +1000 In-Reply-To: Message-ID: <20050423021650282.00000002960@psharman-au> MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-Brightmail-Tracker: AAAAAQAAAAI= X-Whitelist: TRUE X-archive-position: 18832 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: peter.sharman@oracle.com Precedence: normal Reply-To: peter.sharman@oracle.com 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.1 required=5.0 tests=AWL autolearn=ham version=2.63 Well, I guess (and guess is about all anyone except the decision makers can= do here) that the QA etc. has now finished for the RBO so my argument is r= etrospective. However, having just the RULE hint would mean that you need = virtually the entire RBO behind it anyway, wouldn't it? I can't see how it= would work otherwise. 10g does have something towards what you're talking about BTW. You can tel= l the CBO to work in normal mode, or you can tell it "I really want you to = do a lot more work and validate what the best path is." It's something cal= led the Automatic Tuning Optimizer (see http://www.oracle.com/technology/pr= oducts/manageability/database/pdf/twp03/TWP_manage_automatic_SQL_tuning.pdf= ) for an overview. And you're right. I'll call you lazy. :) = Pete = "Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook = "Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA -----Original Message----- From: Post, Ethan [mailto:Ethan.Post@ps.net] = Sent: Saturday, 23 April 2005 1:48 AM To: Pete Sharman; Christian Antognini Cc: oracle-l@freelists.org Subject: RE: rm RULE based optimizer !=3D GOOD IDEA Pete, If there really is significant QA/Testing/Development that has to be done to maintain the RBO then I understand but I would like to know the "truth" about the matter. If the truth is that maintaining the RULE hint isn't all that more complicated than maintaining the USE_HASH hint then I would like to see it stay. Recall, my argument is that Oracle only maintain the hint at the SQL statement level and that it does not need to be supported. Another option (heck this may be in 10g already, I am not that up to speed on that, although I am sure not to the extent I will describe below) is an option to tell a Oracle to go "play" with a query for a while. It would work like this...I run a query and it performs very poorly, so instead of me playing with stats, changing the SQL, adding hints etc...I tell Oracle something like this... Go try to figure out the best plan for this query by actually running it, if you take more than "N" seconds, quit and try another plan. Tomorrow morning tell me what you came up with so I can test in my production environment. What I am trying to do is get away from me having to figure things out (call me lazy). It seems to me Oracle already knows about the indexes, hash joins, nested loops and can go try all of these options for me with me having to try to figure out what is going on. Heck, I would even like Oracle to be able to add actual indexes, perhaps even compress the table. This would be so cool. Oracle would do for me what I spend all day doing on my own. Oracle could even play around with my stats. We could call this the WBO (Work Based Optimizer), meant only to be run at the statement level in a test database. Once we have the plan we would get some type of "key" that we could use to tell Oracle how to run the SQL in our production environment. - Ethan -----Original Message----- From: Pete Sharman [mailto:peter.sharman@oracle.com] = Sent: Friday, April 22, 2005 10:29 AM To: Post, Ethan; Christian Antognini Cc: oracle-l@freelists.org; Peter Ross Sharman Subject: RE: rm RULE based optimizer !=3D GOOD IDEA So let me chime in with my personal viewpoint on why this would happen after I issue the following SQL command: SQL> SELECT standard_disclaimer FROM company_requirements; You can imagine the output. -- http://www.freelists.org/webpage/oracle-l