| RBO to CBO Conversion [message #183815] |
Mon, 24 July 2006 01:14  |
ashuj20
Messages: 12
Registered: October 2005
Location: new delhi
|
Junior Member |
|
|
Hi,
I need some help.
My databases are running in RBO environment using oracle 8i, 9i. Now, because RBO being obsolete in oracle 10g and later releases. I want to switch to CBO.
My databases are in production. I want to look at all the scenarios for conversion to CBO (oracle 10g).
Could you please suggest, What will be the best approach for that, how should I start.
I want to know, what conversions are required for changing to CBO. How should i check the performance after conversion e.g. should i check the performance of all operations in both RBO and CBO environment or something else.
Almost all my queries are using hints, do I have to make some changes on queries also, if yes, then this might be the major task for me. Is there any other way out ?
If, any other information is required, please let me know.
Please help in determining the best strategy.
Regards
Ashish Goel
|
|
|
|
| Re: RBO to CBO Conversion [message #183856 is a reply to message #183815] |
Mon, 24 July 2006 03:08  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
If all your queries include the /*+ RULE*/ hint, then you are in big trouble because I think it still works in 10g. Perhaps there is some funky Init.ora parameter IGNORE_RULE_HINT=TRUE, but I doubt it.
- Get rid of all RULE hints and OPTIMIZER_GOAL / OPTIMIZER_MODE settings.
- Analyze all tables and indexes using DBMS_STATS. The default settings work pretty well.
- Set 10g up to gather stats automatically.
- Try it in a test database and see what happens.
Ross Leishman
|
|
|
|
| Re: RBO to CBO Conversion [message #183919 is a reply to message #183856] |
Mon, 24 July 2006 06:53 |
ashuj20
Messages: 12
Registered: October 2005
Location: new delhi
|
Junior Member |
|
|
Thanks for your reply.
No, My are not useing /*+RULE */ hints, they are using ALL_ROWS and other hints for indexes etc.
I want to know, would I have to tune all the queries again or just setting some parameters will work.
I will switch to oracle 10g, would i have to do something specific other than oracle gathering automatic stats.
Regards
Ashish Goel.
|
|
|
|
| Re: RBO to CBO Conversion [message #184055 is a reply to message #183919] |
Tue, 25 July 2006 02:39 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
OPTIMIZER_MODE (formerly OPTIMIZER_GOAL) hints such as ALL_ROWS are fairly safe for upgrades. Most tuning experts will try to steer you away from INDEX hints and the like, because they do not adapt well to changing data conditions and improvements in the optimizer. I tend to agree, but I've also been around long enough to remember when the optimizer misbehaved a lot and these hints were the only way to get the plan you wanted. I understand why older apps are littered with them.
But if you are using ALL_ROWS and INDEX hints, then you're already using CBO, not RBO. A few SQLs will mis-behave in the upgrade, but mostly all you've done by using INDEX hints is limit the possibility that the (now better-behaved) CBO can find you a more efficient plan.
Ross Leishman
|
|
|
|
| Re: RBO to CBO Conversion [message #184067 is a reply to message #184055] |
Tue, 25 July 2006 03:22 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
More importantly, you're using the CBO without any stats. Things may change quite dramatically when you analyze the tables 8->
As a rule of thumb expect about 5% of your complex queries to cause significant problems after the upgrade.
|
|
|
|
|
|
| Re: RBO to CBO Conversion [message #184136 is a reply to message #184117] |
Tue, 25 July 2006 08:45 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Oooo, now you're getting me all confused!.
When I read your last post, I thought
| Quote: |
By Jove, he's right. No Stats => RBO
|
Then I went and did some tests, and adding any valid hint to a query makes it use the CBO, and it runs with some atrocious estimated stats.
So all the queries using hints are probably going to get different plans because they'll have valid stats for the first time in history, and all the other queries will get radically different plans, as 2 full releases of optimiser query options kick in.
If everything was using the RBO, the OP would actualy be in a better position, as they could create an On-Logon trigger to switch you into CBO or RBO mode, and that way you could bring a few users over to CBO at a time.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
) But as far as I was aware, even in 10g, if you execute a query where the tables involved have (absolutely) no stats, it will still default to RULE. however 
Maybe someone else can cast some light on the subject.