Home » RDBMS Server » Performance Tuning » RBO in 10g
RBO in 10g [message #232986] Tue, 24 April 2007 06:28 Go to next message
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
Member
Hi Experts,

Is it true Rule Based Optimizer is no longer supported in 10g ?

whats is the alterative for the same

Thnx
Re: RBO in 10g [message #232989 is a reply to message #232986] Tue, 24 April 2007 06:34 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
seems yes.
There is a subtle difference between obsolete and not supported (Terms used by oracle support).
Issues with RBO are not supported with 10g.
As of 10.1.0.2.0 you can still set the Optimizer_mode=rule.
Not sure whether it is actually getting used Smile
>>whats is the alterative for the same
Ofcourse CBO. After 8i, CBO is the choice.
Re: RBO in 10g [message #232991 is a reply to message #232989] Tue, 24 April 2007 06:38 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
http://technology.amis.nl/blog/?p=1354
Re: RBO in 10g [message #232998 is a reply to message #232989] Tue, 24 April 2007 06:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes it is used if you really want it:
SQL> @v

Version Oracle : 10.2.0.3.0

SQL> alter session set  Optimizer_mode=rule;

Session altered.

SQL> explain plan for select * from t;

Explained.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 1601196873

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| T    |
----------------------------------

Note
-----
   - rule based optimizer used (consider using cbo)

12 rows selected.

Notice the "rule based optimizer used" and the "consider using cbo". Wink

Regards
Michel
Re: RBO in 10g [message #233107 is a reply to message #232986] Tue, 24 April 2007 13:47 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
The alternative is CBO and particularly first_rows_n for an oltp app and all_rows for more datawarehousing app. Of course this is general suggestion and thing may differ for some cases.

Gints Plivna
http://www.gplivna.eu
Re: RBO in 10g [message #234846 is a reply to message #233107] Thu, 03 May 2007 05:36 Go to previous messageGo to next message
sriram717
Messages: 48
Registered: February 2007
Location: UNITED KINGDOM
Member
Even Oracle has not come out of RBO completely
Say for example if you do a 10046 trace while the database startsup , you could see many statements with rule hint.

say for example like

select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
END OF STMT
Re: RBO in 10g [message #234856 is a reply to message #234846] Thu, 03 May 2007 06:02 Go to previous message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Is this not what my post clearly showed without having to "hack" internal query?

Regards
Michel
Previous Topic: Any Suggestion on this STATSPACK Report
Next Topic: Problem with spreport.sql
Goto Forum:
  


Current Time: Thu Dec 05 13:22:15 CST 2024