书上说这个参数让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会对对象数据块的一部分进行抽样来估算统计数据。动态收集的统计信息不会保存在数据字典中。