Home » RDBMS Server » Performance Tuning » CBO vs RBO
CBO vs RBO [message #262966] Tue, 28 August 2007 11:36 Go to next message
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.
Re: CBO vs RBO [message #262968 is a reply to message #262966] Tue, 28 August 2007 11:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
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

Re: CBO vs RBO [message #262983 is a reply to message #262968] Tue, 28 August 2007 12:55 Go to previous messageGo to next message
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
Re: CBO vs RBO [message #262997 is a reply to message #262983] Tue, 28 August 2007 13:52 Go to previous messageGo to next message
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?
Re: CBO vs RBO [message #263003 is a reply to message #262997] Tue, 28 August 2007 14:04 Go to previous message
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:
  


Current Time: Wed Dec 07 04:58:30 CST 2016

Total time taken to generate the page: 0.10449 seconds