Re: Risk of RULE mode in 10g+
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-lReceived on Mon Mar 15 2010 - 16:47:51 CDT