Home » RDBMS Server » Performance Tuning » RBO to CBO Conversion
RBO to CBO Conversion [message #183815] Mon, 24 July 2006 01:14 Go to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 #184117 is a reply to message #184067] Tue, 25 July 2006 07:58 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Not so much a correction, more trying to clear it up in my own head:

Quote:

you're using the CBO without any stats.


I could be wrong here (I very often am Smile ) 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
The DYNAMIC_SAMPLING parameter defaults now to 2 in 10g
meaning that Oracle will
Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks. (Note that the default number is 32 blocks)

My point is (I guess) That the chances are that there actually will be stats on the appropriate tables, if there isn't (i.e. DYNAMIC_SAMPLING parameter has been set to 0) then Oracle will still use the RBO.

I am no great shakes on P&T so I could hav got the completely wrong end of the stick here. Feel free to shoot me down, but please be gentle with me Smile

Jim
Re: RBO to CBO Conversion [message #184136 is a reply to message #184117] Tue, 25 July 2006 08:45 Go to previous messageGo to next message
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.
Re: RBO to CBO Conversion [message #184143 is a reply to message #184136] Tue, 25 July 2006 09:11 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
So even with dynamic_sampling set to 0, it uses cbo?
Unfortunately I don't have access to 10g to test any of this, so I'm flying in the dark Smile and pretty much guessing
Re: RBO to CBO Conversion [message #184144 is a reply to message #184143] Tue, 25 July 2006 09:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To be honest, I haven't got a clue about Dynamic Sampling and it's effects on the Optimiser.
Re: RBO to CBO Conversion [message #184145 is a reply to message #184144] Tue, 25 July 2006 09:13 Go to previous message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Cool. Let's face it, neither do I. Laughing Maybe someone else can cast some light on the subject.

Jim
Previous Topic: Data Type Mismatch
Next Topic: Deleting duplicate rows without subquery
Goto Forum:
  


Current Time: Sat Dec 03 16:09:38 CST 2016

Total time taken to generate the page: 0.08207 seconds