Rule based in Oracle 10g

October 12, 2006 AMIS, Announce / Events, Database, DBA Oracle, IT, Oracle, Software Development 4 Comments

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.

Related Posts

About The Author

Marco Gralike

Marco Gralike, working for AMIS Services BV as a Principal Oracle Database Consultant in the Netherlands, has experience as a DBA since 1994 (Oracle 6). Marco is also eager and skillful in other fields, like Operating System Administration and Application Servers, mainly to find working, performing solutions. Marco has been specializing in Oracle XMLDB, since 2003, focusing on his old love, database administration and performance. He is an Oracle XMLDB enthusiast ever since. He is also a dedicated contributor of the Oracle User Group community, helping people with their steep XMLDB learning curve. To this purpose, Marco also devoted his personal blog site to XMLDB and other Oracle issues. Marco is a member of the OakTable network and an Oracle ACE Director (specialization Oracle XMLDB).

4 Comments

  1. CSS Compliant October 12, 2006

    Hi,

    Could you please suppress the JS popups that complaints that I have a browser

  2. Marco Gralike October 12, 2006

    By the way, for those who don’t know, “Dizwells” (aka Howard J Rogers) can be found here: http://www.dizwell.com

  3. Marco Gralike October 12, 2006

    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

  4. Howard J. Rogers October 12, 2006

    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.