奇怪的执行计划 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 。
