Re: Risk of RULE mode in 10g+

From: Jeffrey Zollars <zollarja_at_gmail.com>
Date: Mon, 15 Mar 2010 14:47:51 -0700
Message-ID: <d8169fce1003151447q353a801bma676721cf8e58bae_at_mail.gmail.com>



Hey Ric, nice to hear from you.

Yes, RULE mode hints do work in 10.1+, and Oracle does use it for some SYSTEM level statements. As an example from someone above does show. I do suspect that Oracle has hard-coded the execution directly through the executable rather than calling the RBO (but, that's a guess on my part.) A quick check on the RULE hint shows ...

SQL> create table t as select * from dba_objects;

... set event 10053 (CBO) tracing on ...

SQL> select /*+ RULE */ from t where rownum < 99 ;

The 10053 trace file shows ...

Content of other_xml column


  db_version     : 10.2.0.3
  parse_schema   : SYS
  plan_hash      : 2287254946

  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      RBO_OUTLINE
      OUTLINE_LEAF(_at_"SEL$1")
      FULL(_at_"SEL$1" "T"_at_"SEL$1")

    END_OUTLINE_DATA
*/

Running the same query without the hint gives ...

  /*+
    BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(_at_"SEL$1")
      FULL(_at_"SEL$1" "T"_at_"SEL$1")

    END_OUTLINE_DATA
*/

Now the query is running in CBO, ALL_ROWS. I cannot find any documentation on the RBO_OUTLINE, but suspect that it's executing the query in RULE mode at that point.

A further note, one reason it's difficult to modify the code is that it's wrapped. I can plainly see the /*+ RULE */ hint in the wrapped SQL statement in a couple cases but cannot see many other details about it. So, it's hard to say whether other features of the query would force it into CBO mode or not.

Thanks to all for the great comments so far! Jeff

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Mar 15 2010 - 16:47:51 CDT

Original text of this message