OraFAQ Forum: Performance Tuning » RBO to CBO Conversion

Home » RDBMS Server » Performance Tuning » RBO to CBO Conversion

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

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

Report message to a moderator

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.

Report message to a moderator

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

Report message to a moderator

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.

Report message to a moderator

Re: RBO to CBO Conversion [message #184117 is a reply to message #184067]

Tue, 25 July 2006 07:58

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

Report message to a moderator

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.

Report message to a moderator