From oracle-l-bounce@freelists.org Fri Apr 22 12:07:14 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j3MH7E3l006100 for ; Fri, 22 Apr 2005 12:07:14 -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 j3MH7D4Z006090 for ; Fri, 22 Apr 2005 12:07:13 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A1F2518659B; Fri, 22 Apr 2005 10:50:11 -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 22087-04; Fri, 22 Apr 2005 10:50:11 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 27797186230; Fri, 22 Apr 2005 10:50:11 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit Subject: RE: rm RULE based optimizer != GOOD IDEA Date: Fri, 22 Apr 2005 10:47:46 -0500 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: rm RULE based optimizer != GOOD IDEA Thread-Index: AcVHUGy7t4/KWvfQR8mwtxW/Mw1sdQAAGuZw From: "Post, Ethan" To: "Pete Sharman" , "Christian Antognini" Cc: X-OriginalArrivalTime: 22 Apr 2005 15:48:18.0090 (UTC) FILETIME=[B3E448A0:01C54752] X-archive-position: 18829 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Ethan.Post@ps.net Precedence: normal Reply-To: Ethan.Post@ps.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.0 required=5.0 tests=AWL autolearn=ham version=2.63 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]=20 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