| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: migration from RBO to CBO
Big depends answer. The only way I think you can be absolutely sure that
the plan won't change with CBO is to use hints or outlines. I have noticed
that FIRST_ROWS hint behaves a lot likely RBO but I cannot say that is
always true. I defer to the CBO experts on this one like Jonathon.
Oracle OCP DBA
-----Original Message-----
Sent: Thursday, May 08, 2003 2:32 PM
To: Multiple recipients of list ORACLE-L
A few months back I inquired about the effect of RBO tuning syntax on the CBO. To sum up the response. No one reading had looked into it, and suggested I do so and report my findings. Well I just recently reached that item on my to do list, that is approaching infinity, and this is what I have learned so far.
I found that, in deed, all those tuning techniques used to force the RBO to ignore spurious indexes also prevent the CBO from considering those less than desirable indexes. This is what I had expected, but wanted to confirm it before throwing a possibly huge wrench into a production OLTP database. I created a test copy of our production database, only about 50GB, to evaluate the CBO. I grabbed about two dozen SQL statements out of the production sga, and ran them on the test instance. All but one query had identical execution plans on both systems. That one query involved a FTS on a very small table. It looks like the CBO plan is a small improvement. I supplemented this set of queries by searching our SQL code base for '+0', and tested many of the queries that had been so tuned. Those queries, as expected, also had identical plans on both systems.
This, to me, seems to be enough evidence to support generating statistics on production(OPTIMIZER_MODE=CHOOSE), and monitoring the results. I really don't want to be suprised though. I am wondering if anyone has any ideas on how to 'bulk' compare the execution plans of a large number of queries? I have compared about 30 so far, and don't necessarily want to duplicate that process for a hundred more.
That said, I have a question for those of you using the CBO. How often do the CBO execution plans need serious tweaking?
Steve McClure
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Steve McClure
INET: smcclure_at_usscript.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: Paula_Stankus_at_doh.state.fl.us
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu May 08 2003 - 14:06:31 CDT
 |
 |