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.

下面测试一把，
sys@LISA> show parameter OPTIMIZER_DYNAMIC_SAMPLING;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
sys@LISA> col table_name for a20
sys@LISA> create table test as select object_id a from dba_objects;

Table created.

--此表刚建好还没有做分析
sys@LISA> select table_name,num_rows from user_tables where table_name='TEST';

TABLE_NAME NUM_ROWS
-------------------- ----------
TEST

--执行SQL，扫描此表
sys@LISA> select count(*) from test;

COUNT(*)
----------
49931

--还是没有收集到统计信息
sys@LISA> select table_name,num_rows from user_tables where table_name='TEST';

TABLE_NAME NUM_ROWS
-------------------- ----------
TEST

sys@LISA> set autot on;
sys@LISA> 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会对对象数据块的一部分进行抽样来估算统计数据。动态收集的统计信息不会保存在数据字典中。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/271283/viewspace-1003367/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/271283/viewspace-1003367/