oracle dynamic_sampling,optimizer_dynamic_sampling参数的理解

书上说这个参数让ORACLE在表中不存在统计数据，或者虽然存在统计数据，但统计数据太旧，或者存在其它方面的不可靠性的时候，即时估算optimizer统计数据。[@more@]

OPTIMIZER_DYNAMIC_SAMPLING

Parameter type

Integer

Default value

If OPTIMIZER_FEATURES_ENABLE is set to 9.2.0 or higher, then 1

If OPTIMIZER_FEATURES_ENABLE is set to 9.0.1 or lower, then 0

Parameter class

Dynamic: ALTER SESSION, ALTER SYSTEM

Range of values

0 to 10

OPTIMIZER_DYNAMIC_SAMPLING controls the level of dynamic sampling performed by the optimizer.

根据不同的optimizer_dynamic_sampling级别而不同，他总共有10个级别，分别如下：

· Level 0: Do not use dynamic sampling.

· Level 1: The number of blocks sampled is the default number of dynamic sampling blocks (32).

· Level 2: The number of blocks sampled is 2 times the default number of dynamic sampling blocks.

· Level 3: The number of blocks sampled is 4 times the default number of dynamic sampling blocks.

· Level 4: The number of blocks sampled is 8 times the default number of dynamic sampling blocks.

· Level 5: The number of blocks sampled is 16 times the default number of dynamic sampling blocks.

· Level 6: The number of blocks sampled is 32 times the default number of dynamic sampling blocks.

· Level 7: The number of blocks sampled is 64 times the default number of dynamic sampling blocks.

· Level 8: The number of blocks sampled is 128 times the default number of dynamic sampling blocks.

· Level 9: The number of blocks sampled is 256 times the default number of dynamic sampling blocks.

· Level 10: Read all blocks in the table.

下面测试一把，

> show parameter OPTIMIZER_DYNAMIC_SAMPLING;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

optimizer_dynamic_sampling integer 2

> col table_name for a20

> create table test as select object_id a from dba_objects;

Table created.

--此表刚建好还没有做分析

> select table_name,num_rows from user_tables where table_name='TEST';

TABLE_NAME NUM_ROWS

-------------------- ----------

TEST

--执行SQL，扫描此表

> select count(*) from test;

COUNT(*)

----------

49931

--还是没有收集到统计信息

> select table_name,num_rows from user_tables where table_name='TEST';

TABLE_NAME NUM_ROWS

-------------------- ----------

TEST

> set autot on;

> select count(*) from test;

COUNT(*)

----------

49931

Execution Plan

----------------------------------------------------------

Plan hash value: 1950795681

-------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 19 (6)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST | 47534 | 19 (6)| 00:00:01 |

-------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

79 consistent gets

0 physical reads

0 redo size

413 bytes sent via SQL*Net to client

396 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

--发现有动态收集统计信息

总结：ORACLE在遇到没有统计数据的对象时，会很聪明地计算出增加的编译时间是否值得，如果值得，oracle会对对象数据块的一部分进行抽样来估算统计数据。动态收集的统计信息不会保存在数据字典中。