RE: rm RULE based optimizer != 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_at_oracle.com]=20 Sent: Friday, April 22, 2005 10:29 AM
To: Post, Ethan; Christian Antognini
Cc: oracle-l_at_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