Apparently it is still out there, the RBO in 10g and even Oracle 10.2.0.2.0, as demonstrated by Eric, and i thought that it was already had gone. Especially in Oracle 10gR2. Maybe not internally, but untouchable by us mere mortals.
I saw the following post of Eric Jenkinson in an OTN forum( http://forums.oracle.com/forums/message.jspa?messageID=1499581)
The rule based optimizer is most definitely present in 10gR2. It might
not be in the documentation, but it is still there.
C:\sql>sqlplus test/test
SQL*Plus: Release 10.2.0.2.0 - Production on Tue Oct 10 2006
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
With the Partitioning, OLAP and Data Mining options
test@SVTEST> set autotrace traceonly
test@SVTEST> alter session set optimizer_mode=rule;
Session altered.
Elapsed: 00:00:00.01
test@SVTEST> select * from dual;
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 272002086
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| DUAL |
----------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
2 physical reads
0 redo size
407 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
The other remark he makes is also, I think, a valid one (and one that could be expected):
Also, if perform 10046 trace on SQL statements that generate recursive
SQL you will see Oracle still uses the RULE hint on some of the
queries.
It is not supported, but it is still there.
Thank you Eric.
Hi,
Could you please suppress the JS popups that complaints that I have a browser
By the way, for those who don’t know, “Dizwells” (aka Howard J Rogers) can be found here: http://www.dizwell.com
Thanks Howard for clarifying this, I wasn’t aware of it.
Only excuse I have is that when I’m sometimes allowed to go to a course (and/or have time), I choose for seminars like those from Tom Kyte, Steve Adam or Chris Date or attend an Oracle Open World (but not Oracle courses). I try to keep up, if I have the time, is to selectively read items in the manuals (like the Concepts Guide) or read up on blog items like those on yours.
😉
To be honest, I would have preferred that they disabled it old together and only for (practical) emergencies had implemented a hidden parameter, in case it is needed by not core Oracle processes. The optimizer is smart enough nowadays to handle almost all situations.
Marco
But this is well-documented (in Oracle University’s own 10g New Features training course material, at least!): the Rule-based Optimiser exists and can be used but, as they put it, ‘has undergone almost no regression testing’… which, being translated into English, means that they don’t know if it works properly with all the new 10g code or not. (My best guess is “not”!)
The idea that the RBO ‘disappeared’ or was ‘removed’ was an old myth floating around during the 10g beta days. It wasn’t true then, and it’s not true now. The code is still there, and it’s pretty much exactly the same code as was in 9i… but we simply don’t know if it works properly any more given everything else that went on in 10g.