动态采样

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