Oh, but you *will* get CBO if you use hints. I mention this because it was referred to earlier in this thread. Oracle uses default values when statistics are lacking. This is 9.2.0.4 on RH 7.1 Jared 16:32:44 SQL> 16:32:44 SQL>alter session set optimizer_mode = rule; Session altered. 16:32:44 SQL> 16:32:44 SQL>drop table cbo; Table dropped. 16:32:44 SQL> 16:32:44 SQL>create table cbo 16:32:44 2 as 16:32:44 3 select owner, object_name, object_type 16:32:44 4 from dba_objects 16:32:44 5 / Table created. 16:32:45 SQL> 16:32:45 SQL> 16:32:45 SQL>create index cboidx on cbo(owner, object_name) 16:32:45 2 / Index created. 16:32:46 SQL> 16:32:46 SQL> 16:32:46 SQL>delete from plan_table; 6 rows deleted. 16:32:46 SQL> 16:32:46 SQL>commit; Commit complete. 16:32:46 SQL> 16:32:46 SQL>explain plan 16:32:46 2 set statement_id 'Q1' 16:32:46 3 for 16:32:46 4 select count(*) 16:32:46 5 from cbo 16:32:46 6 / Explained. 16:32:46 SQL> 16:32:46 SQL> 16:32:46 SQL>explain plan 16:32:46 2 set statement_id 'Q2' 16:32:46 3 for 16:32:46 4 select /*+ index(cbo cboidx) */ count(*) 16:32:46 5 from cbo 16:32:46 6 / Explained. 16:32:46 SQL> 16:32:46 SQL> 16:32:46 SQL>@showplan9i Q1 16:32:46 SQL> 16:32:46 SQL>-- showplan9i.sql 16:32:46 SQL> 16:32:46 SQL>SET PAUSE OFF 16:32:46 SQL>SET VERIFY OFF 16:32:46 SQL>set trimspool on 16:32:46 SQL>set line 200 arraysize 1 16:32:46 SQL>clear break 16:32:46 SQL>clear compute 16:32:46 SQL> 16:32:46 SQL> 16:32:46 SQL>select * 16:32:46 2 from table(dbms_xplan.display( 'PLAN_TABLE', '&&1')) 16:32:46 3 / PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------- -------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | SORT AGGREGATE | | | | | | 2 | TABLE ACCESS FULL | CBO | | | | -------------------------------------------------------------------- Note: rule based optimization 10 rows selected. 16:32:46 SQL> 16:32:46 SQL> 16:32:46 SQL> 16:32:46 SQL>@showplan9i Q2 16:32:46 SQL> 16:32:46 SQL>-- showplan9i.sql 16:32:46 SQL> 16:32:46 SQL>SET PAUSE OFF 16:32:46 SQL>SET VERIFY OFF 16:32:46 SQL>set trimspool on 16:32:46 SQL>set line 200 arraysize 1 16:32:46 SQL>clear break 16:32:46 SQL>clear compute 16:32:46 SQL> 16:32:46 SQL> 16:32:46 SQL>select * 16:32:46 2 from table(dbms_xplan.display( 'PLAN_TABLE', '&&1')) 16:32:46 3 / PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------- -------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 19 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL | CBO | 2000 | | 19 | -------------------------------------------------------------------- Note: cpu costing is off 10 rows selected. 16:32:46 SQL> 16:32:46 SQL> DENNIS WILLIAMS <DWILLIAMS@xxxxxxxxxxxxx> Sent by: oracle-l-bounce@xxxxxxxxxxxxx 03/18/2004 02:10 PM Please respond to oracle-l To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx> cc: Subject: RE: Stored Outlines and Optimizer Mode Paula How are you ensuring RULE is used today? Not analyzing tables? That is an issue because CBO needs you to analyze, which will cause everything to be CBO. If the SQL has a hint, then analyze won't be a problem, but you won't get CBO anyway. Dennis Williams DBA Lifetouch, Inc. dwilliams@xxxxxxxxxxxxx -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Paula Winkler Sent: Thursday, March 18, 2004 3:35 PM To: oracle-l@xxxxxxxxxxxxx Subject: RE: Stored Outlines and Optimizer Mode Thank you Niall and Lex for your responses. We have a 3rd-party Oracle8i system running under the RULE-based optimizer. We have identified a handful of poor performing SQLs. We can't change the generated SQLs therefore we are looking into using stored outlines to store the access plans for those exceptional SQLs. Our thought is CBO would kick in for the SQLs with the stored outlines and RBO would kick in for the other acceptable SQLs. Does this sound like it will work? - Paula W. Do you Yahoo!? <http://us.rd.yahoo.com/mailtag_us/*http://mail.yahoo.com> Yahoo! Mail - More reliable, more storage, less spam ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------