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會對物件資料塊的一部分進行抽樣來估算統計資料。動態收集的統計資訊不會儲存在資料字典中。