动态采样

今天做实验想看看在没有统计信息的情况下，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级别和描述如下:

Level 0: Perform no dynamic sampling at all.

------不进行动态采样

Level 1: Sample tables that appear in join or sub-query conditions that have no indexes and have more blocks than 32 (the default for dynamic sampling)

------被采样的表必须满足以下条件:被采样的表必须出现在join或者子查询中，并且没有索引，而且blocks必须大于32(默认采样数)

Level 2: Dynamically sample all unanalyzed tables that have more than 32 blocks.

------采样block数大于32并且没有被分析的表

Level 3: Dynamic samples tables using a single column that applies selectivity to the table being sampled in addition to the level 2 rules.

------应用level2的规则，并且在单列上应用选择性规则检查

Level 4: Dynamic samples tables using 2 or more columns that applies selectivity to the table being sampled in addition to the level 3 rules.

------应用level3的规则，并且在2列，或者多余2列上应用选择性规则检查

Level 5: Dynamic samples up to 64 blocks on tables using level 4 rules. The table of course has to be larger than 64 blocks in size (2 times the default sampling size).

------应用level4的规则，并且被采样的表的block数必须大于64，同时抽取64个block采样

Level 6: Dynamic samples up to 128 blocks on tables using level 4 rules. The table, of course, has to be larger than 128 blocks in size (4 times the default sampling size).

------应用level4的规则，并且被采样的表的block数必须大于128，同时抽取128个block采样

Level 7: Dynamic samples up to 256 blocks on tables using level 4 rules. The table, of course, has to be larger than 256 blocks in size (8 times the default sampling size).

-------应用level4的规则，并且被采样的表的block数必须大于256，同时抽取256个block采样

Level 8: Dynamic samples up to 1024 blocks on tables using level 4 rules. The table, of course, has to be larger than 1024 blocks in size (32 times the default sampling size).

-------应用level4的规则，并且被采样的表的block数必须大于1024，同时抽取1024个block采样

Level 9: Dynamic samples up to 4096 blocks on tables using level 4 rules. The table has to be larger than 4096 blocks in size (128 times the default sampling size).

-------应用level4的规则，并且被采样的表的block数必须大于4096，同时抽取4096个block采样

Level 10: Dynamic sampling of all blocks on tables using level 4 rules

-------应用level4的规则，并且抽取所有的blocks进行采样