今天做实验想看看在没有统计信息的情况下，oracle会采取哪种方式执行SQL，结果偶然发现了动态采样机制，实验过程:
SQL> select * from v$version;
BANNER
-----------------------------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> create table test as select * from dba_objects;
表已创建。
SQL> update test set object_id=1000 ;
已更新49792行。
SQL> create index i_object_id on test(object_id);
索引已创建。
SQL> exec dbms_stats.delete_table_stats('ROBINSON','TEST');
PL/SQL 过程已成功完成。
SQL> exec dbms_stats.delete_index_stats('ROBINSON','I_OBJECT_ID');
PL/SQL 过程已成功完成。
SQL> set autot traceonly
SQL> select * from test where object_id=10;
未选定行
已用时间: 00: 00: 00.14
执行计划
----------------------------------------------------------
Plan hash value: 915613353
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 177 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 177 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=10)
Note
-----
- dynamic sampling used for this statement ---------动态采样
SQL> select * from test where object_id=1000;
已选择49792行。
已用时间: 00: 00: 09.14
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52774 | 9122K| 160 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TEST | 52774 | 9122K| 160 (3)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1000)
Note
-----
- dynamic sampling used for this statement ---------动态采样
实验做到这里，我有点惊讶，为什么CBO选择了正确的执行计划？按照我的猜想CBO应该执行索引扫描的，结果执行了全表扫描，选择了正确的执行计划。我就说ORACLE怎么这么聪明，在没有统计信息的情况下居然还能选择正确的执行计划，原来是引入了动态采样的机制，从oracle9iR2开始引入。
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
optimizer_mode string ALL_ROWS
SQL> alter system set optimizer_mode=choose;
系统已更改。
SQL> set autot traceonly
SQL> select * from test where object_id=1000;
已选择49792行。
执行计划
----------------------------------------------------------
Plan hash value: 915613353
---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST |
|* 2 | INDEX RANGE SCAN | I_OBJECT_ID |
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1000)
Note
-----
- rule based optimizer used (consider using cbo) --------使用基于规则的优化方式
当更改优化器模式之后，走了索引扫描，和我第一个猜想一样
SQL> show parameter dynamic
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
optimizer_dynamic_sampling integer 2
参数optimizer_dynamic_sampling用于控制动态采样的级别，10g中默认为2，9.2.0为1,以下版本为0，它的最高级别为10.
level级别和描述如下:
------不进行动态采样
------被采样的表必须满足以下条件:被采样的表必须出现在join或者子查询中，并且没有索引，而且blocks必须大于32(默认采样数)
------采样block数大于32并且没有被分析的表
------应用level2的规则，并且在单列上应用选择性规则检查
------应用level3的规则，并且在2列，或者多余2列上应用选择性规则检查
------应用level4的规则，并且被采样的表的block数必须大于64，同时抽取64个block采样
------应用level4的规则，并且被采样的表的block数必须大于128，同时抽取128个block采样
-------应用level4的规则，并且被采样的表的block数必须大于256，同时抽取256个block采样
-------应用level4的规则，并且被采样的表的block数必须大于1024，同时抽取1024个block采样
-------应用level4的规则，并且被采样的表的block数必须大于4096，同时抽取4096个block采样
-------应用level4的规则，并且抽取所有的blocks进行采样