奇怪的执行计划 10g 开始 oracle 就默认使用cbo 来代替rbo 来选择合适的执行计划，但是今天遇到的一个例子，有点例外：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production
SQL> show parameter optimizer

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.2.0.4
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      CHOOSE
optimizer_secure_view_merging        boolean     TRUE

SQL> create table t as select * from dba_objects;

Table created.

SQL> set timing on
SQL> set autot traceonly
SQL> select count(*) from t;

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1842905362

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

Note
-----
   - rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        737  consistent gets
        732  physical reads
          0  redo size
        517  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

可以看出 该执行计划使用的是rbo 并提示考虑使用cbo 。而当对该表进行信息统计之后，执行计划就会使用cbo。

SQL> exec dbms_stats.gather_table_stats (user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.52
SQL> select count(*) from t;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1842905362

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   163   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE      |      |     1 |                  |          |
|   2 |   TABLE ACCESS FULL | T  | 53482 |   163   (1)| 00:00:02 |
-------------------------------------------------------------------

Statistics
----------------------------------------------------------
         42  recursive calls
          0  db block gets
        741  consistent gets
          0  physical reads
          0  redo size
        517  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

由此看见在没有进行信息统计的情况下，10g 会使用 RBO 而进行了信息统计后就会使用 CBO 。