How about no optimizer stats on the objects?

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Aug 22 06:29:42 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options


Session altered.

Elapsed: 00:00:00.08

Session altered.

Elapsed: 00:00:00.08
whse2000:prot\ciopdba> ed glogin.sql -- SHOWN AT BOTTOM

whse2000:prot\ciopdba> set autot on exp;
whse2000:prot\ciopdba> select count(*) from o_prdcr_prod_wip;

            COUNT(*)
--------------------
           9,635,613

Elapsed: 00:00:49.49

Execution Plan
----------------------------------------------------------
Plan hash value: 2446809081

-----------------------------------------------
| Id  | Operation          | Name             |
-----------------------------------------------
|   0 | SELECT STATEMENT   |                  |
|   1 |  SORT AGGREGATE    |                  |
|   2 |   TABLE ACCESS FULL| O_PRDCR_PROD_WIP |
-----------------------------------------------

Note
-----
   - rule based optimizer used (consider using cbo)

whse2000:prot\ciopdba> select count(*) from object_timing;

            COUNT(*)
--------------------
              24,630

Elapsed: 00:00:00.11

Execution Plan
----------------------------------------------------------
Plan hash value: 3904142253

----------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |    36   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |               |     1 |            |          |
|   2 |   TABLE ACCESS FULL| OBJECT_TIMING | 22350 |    36   (0)| 00:00:01 |
----------------------------------------------------------------------------

whse2000:prot\ciopdba>

***************************************************
***************************************************
--
-- Copyright (c) 1988, 2004, Oracle Corporation.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command
--
-- USAGE
--   This script is automatically run
--

-- Used by Trusted Oracle
COLUMN ROWLABEL FORMAT A15

-- Used for the SHOW ERRORS command
COLUMN LINE/COL FORMAT A8
COLUMN ERROR    FORMAT A65  WORD_WRAPPED

-- Used for the SHOW SGA command
COLUMN name_col_plus_show_sga FORMAT a24
COLUMN units_col_plus_show_sga FORMAT a15
-- Defaults for SHOW PARAMETERS
COLUMN name_col_plus_show_param FORMAT a36 HEADING NAME
COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE

-- Defaults for SHOW RECYCLEBIN
COLUMN origname_plus_show_recyc   FORMAT a16 HEADING 'ORIGINAL NAME'
COLUMN objectname_plus_show_recyc FORMAT a30 HEADING 'RECYCLEBIN NAME'
COLUMN objtype_plus_show_recyc    FORMAT a12 HEADING 'OBJECT TYPE'
COLUMN droptime_plus_show_recyc   FORMAT a19 HEADING 'DROP TIME'

-- Defaults for SET AUTOTRACE EXPLAIN report
-- These column definitions are only used when SQL*Plus
-- is connected to Oracle 9.2 or earlier.
COLUMN id_plus_exp FORMAT 990 HEADING i
COLUMN parent_id_plus_exp FORMAT 990 HEADING p
COLUMN plan_plus_exp FORMAT a60
COLUMN object_node_plus_exp FORMAT a8
COLUMN other_tag_plus_exp FORMAT a29
COLUMN other_plus_exp FORMAT a44

-- Default for XQUERY
COLUMN result_plus_xquery HEADING 'Result Sequence'

-- Duke's magic
column COUNT(*) format 999,999,999,999,999;
column CNT format 999,999,999,999,999;
SET SERVEROUTPUT ON;
set timing on;
set pagesize 50000;
set linesize 132;
set termout off;
DEFINE sqlprompt=SQL;
COLUMN sqlprompt NEW_VALUE sqlprompt;
SELECT LOWER(SYS_CONTEXT('USERENV','CURRENT_USER'))
    as sqlprompt 
  from dual
;
SELECT SYS_CONTEXT('USERENV','SERVER_HOST')
       ||':'
       || SYS_CONTEXT('USERENV','DB_NAME')
       ||'\'
       || LOWER(SYS_CONTEXT('USERENV','CURRENT_USER'))
    as sqlprompt 
  from dual
;
set termout on;
set sqlprompt '&sqlprompt> ';
alter session set nls_date_format='yyyy-mm-dd HH24:MI:SS';
alter session set nls_timestamp_format = 'yyyy-mm-dd HH24:MI:SS.FF';

SQL> alter session set optimizer_mode = all_rows;

Session altered.

SQL> set autot on exp;
SQL> select count(*) from ciopdba.o_prdcr_prod_wip;

            COUNT(*)
--------------------
           9,635,613

Elapsed: 00:00:02.06

Execution Plan
----------------------------------------------------------
Plan hash value: 2446809081

-------------------------------------------------------------------------------
| Id  | Operation          | Name             | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  |     1 |  7418   (3)| 00:01:30 |
|   1 |  SORT AGGREGATE    |                  |     1 |            |          |
|   2 |   TABLE ACCESS FULL| O_PRDCR_PROD_WIP |    11M|  7418   (3)| 00:01:30 |
-------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

SQL> alter session set optimizer_mode = CHOOSE;

Session altered.

Elapsed: 00:00:00.00
SQL> select count(*) from ciopdba.o_prdcr_prod_wip;

            COUNT(*)
--------------------
           9,635,613

Elapsed: 00:00:04.96

Execution Plan
----------------------------------------------------------
Plan hash value: 2446809081

-----------------------------------------------
| Id  | Operation          | Name             |
-----------------------------------------------
|   0 | SELECT STATEMENT   |                  |
|   1 |  SORT AGGREGATE    |                  |
|   2 |   TABLE ACCESS FULL| O_PRDCR_PROD_WIP |
-----------------------------------------------

Note
-----
   - rule based optimizer used (consider using cbo)

I got confused by only vaguely recalling this:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3310002340673#3310080517320

whse2000:prot\ciopdba> ed
Wrote file afiedt.buf

  1  BEGIN
  2    dbms_stats.gather_table_stats
  3    ( user
  4    ,'O_PRDCR_PROD_WIP'
  5    ,estimate_percent =>  dbms_stats.auto_sample_size );
  6* END;
whse2000:prot\ciopdba> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.15
whse2000:prot\ciopdba> alter session set optimizer_mode = choose;

Session altered.

Elapsed: 00:00:00.01
whse2000:prot\ciopdba> set autot on exp;
whse2000:prot\ciopdba> select count(*) From o_prdcr_prod_wip;

            COUNT(*)
--------------------
           9,635,613

Elapsed: 00:00:04.49

Execution Plan
----------------------------------------------------------
Plan hash value: 2446809081

-------------------------------------------------------------------------------
| Id  | Operation          | Name             | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  |     1 |  7394   (3)| 00:01:29 |
|   1 |  SORT AGGREGATE    |                  |     1 |            |          |
|   2 |   TABLE ACCESS FULL| O_PRDCR_PROD_WIP |  9678K|  7394   (3)| 00:01:29 |
-------------------------------------------------------------------------------

This was already answered by Tom on the other thread.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5671636641855#748832500346812935

It typically runs like this:

a) People are on v7, v8 or v9.

b) People take a backup of their spfile or take a backup by querying v$parameter.

c) They use that backup for some reason, and thus "optimizer_mode" is now specified explicitly to its default of CHOOSE.

d) they upgrade to to 10g and voila....

Hi,

I have one query that take too much time because of use of rule based optimization. but in my database i have optimizer_mode=choose

SQL> SHOW PARAMETER OPTIMIZER_MODE

NAME                                 TYPE        VALUE
--------------------------------- ----------- -----------
optimizer_mode                       string      CHOOSE

and I execute below query to get some constraint information for "Mytable". 
SQL>SELECT A.TABLE_NAME AS CLD,C.COLUMN_NAME CCOL,D.COLUMN_NAME PCOL
 FROM USER_CONSTRAINTS A, user_constraints B, USER_CONS_COLUMNS C, USER_CONS_COLUMNS D
 WHERE A.table_name <> 'MYTABLE' and B.table_name = 'MYTABLE'
    and A.R_CONSTRAINT_NAME=B.constraint_name
    and (B.constraint_type='P' or B.constraint_type='U')
    AND C.CONSTRAINT_NAME=A.CONSTRAINT_NAME
    AND D.CONSTRAINT_NAME=A.R_CONSTRAINT_NAME
 order by A.table_name;

This query take too much time for execution. When I view plan for the execution of query, it  show me use of RULE base optimization. then i use hint with this query to force COST base optimization and it takes only few seconds to complete.
Is, the use of RULE base optimization is because of lack of statistics on dictionary tables that are used in this query? if yes, then is it require to collect statistics of dictionary table also when, we use static DD view in our query?

Thanks

Oracle 11.1.0.7. Init.ora optimizer_mode = ALL_ROWS. optimizer_features_enable = 11.1.0.7. Auto stats gathering jobs are all enabled. So everything should be all CBO all the way. Some queries were mysteriously slow on Friday. Digging into AWR stats finally revealed that a SQL statement (sql_id) had spawned a plan_hash_value that was the culprit. It was doing a full table scan on a large table and consuming a lot of CPU/IO. Dumping out everything AWR knows about this SQL using

select * from table(dbms_xplan.display_awr('45knuy45ha5f2',NULL,NULL,'ADVANCED'))

Pored over the output and sure enough, the "bad" plan_hash_value was using the FTS and the Note in the output said "rule based optimizer used (consider using cbo)". I couldn't believe my eyes.

Why in the world did that one child cursor use the RBO? Any ideas?

Hm, I don't see how that is possible given that the query is generated from a front-end application so the query text, NLS/session environment, etc everything stays the same. I don't see how one specific child cursor of the same sql_id could suddenly decide to use the RBO.

Our DBA has logged a SR with Oracle Support but since we can't reproduce the issue at will, I don't expect much to come out of the SR.

Thanks for your time.