OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » CBO vs RBO

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

CBO vs RBO [message #262966]

Tue, 28 August 2007 11:36

rubhatta
Messages: 7
Registered: August 2007

Junior Member

We are upgrading our Oracle database from 8i to 10g. We have some SQL statements that use the Rule hint. Since RBO is not supported in 10g, what will be the impact of the Rule hints on the performance? How do I handle the same?

One of the SQL statements looks like this:
SELECT /*+ RULE */ d.lig_fo_id, o.order_key_id,o.source_system_id
FROM order_hdr_info o, order_dtl_info d
WHERE o.order_key_id = d.order_key_id
AND o.source_system_id = d.source_system_id
AND o.cof_list_id > 'A%'
AND (o.division is null or o.division = '')

Kindly let me know what I should do.

Thanks.

Report message to a moderator

Re: CBO vs RBO [message #262968 is a reply to message #262966]

Tue, 28 August 2007 11:56

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

RBO is still there in 10g and works as previous versions.

By the way, you can remove "o.division = ''" this is never true.

Regards
Michel

Report message to a moderator

Re: CBO vs RBO [message #262983 is a reply to message #262968]

Tue, 28 August 2007 12:55

gintsp
Messages: 118
Registered: February 2007

Senior Member

Michel Cadot wrote on Tue, 28 August 2007 19:56

RBO is still there in 10g and works as previous versions.

Of course thinking about the future you can gradually get rid of them. Just remove hint and look how the query works. Of course you need to test your queries as you always do, don't you? Wink

Gints Plivna
http://www.gplivna.eu

Report message to a moderator

Re: CBO vs RBO [message #262997 is a reply to message #262983]

Tue, 28 August 2007 13:52

rubhatta
Messages: 7
Registered: August 2007

Junior Member

Thank you for your replies.

I should probably compare the execution plans using /*+RULE*/ and
/*+CHOOSE*/ (to force the CBO). If the plan seems to work better with the RULE hint should I keep it that way or try to tune the query better for CBO?

What would you suggest?

Report message to a moderator

Re: CBO vs RBO [message #263003 is a reply to message #262997]

Tue, 28 August 2007 14:04

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

update your statistics and check both CBO & RBO.

Explain plan for 'your querry with RULE'

explain paln for 'Your query with CHOOSE'

you will get better result.

[Updated on: Tue, 28 August 2007 14:14]

Report message to a moderator

Previous Topic:	Output of Query is very slow through SQL
Next Topic:	slow in scanning documents

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 28 10:18:40 CDT 2024