Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: rm RULE based optimizer != GOOD IDEA

RE: rm RULE based optimizer != GOOD IDEA

From: Pete Sharman <>
Date: Sat, 23 Apr 2005 02:16:50 +1000
Message-ID: <20050423021650282.00000002960@psharman-au>

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
) for an overview.

And you're right. I'll call you lazy. :)



"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 [] =

Sent: Saturday, 23 April 2005 1:48 AM
To: Pete Sharman; Christian Antognini
Subject: RE: rm RULE based optimizer !=3D GOOD IDEA


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.

-----Original Message-----
From: Pete Sharman [] =

Sent: Friday, April 22, 2005 10:29 AM
To: Post, Ethan; Christian Antognini
Cc:; 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.

Received on Fri Apr 22 2005 - 12:27:10 CDT

Original text of this message