• 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.

下面做一些测试

SQL> create table test as select object_id a from dba_objects;

Table created.

SQL>  show parameter optimizer_dynamic_sampling

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     1

SQL> set autotrace trace explain;

SQL> select count(*) from test;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'TEST'

SQL> alter session set optimizer_dynamic_sampling=10;

Session altered.

SQL> select count(*) from test;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'TEST'

dynamic sampling没有起作用，因为在9i里面dynamic sampling需要在多表关联的语句里面才会起作用。在10g里面optimizer_dynamic_sampling在2及以上级别时单表就能dynamic sampling。

SQL 10G>alter session set optimizer_dynamic_sampling=2;

Session altered.

SQL 10G>select count(*) from test;

Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    21   (5)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST | 48065 |    21   (5)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

看看关联查询的情况

SQL> create table test1 as select * from test;

Table created.

SQL> select count(*) from test a,test1 b where a.a=b.a;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     MERGE JOIN
   3    2       SORT (JOIN)
   4    3         TABLE ACCESS (FULL) OF 'TEST1'
   5    2       SORT (JOIN)
   6    5         TABLE ACCESS (FULL) OF 'TEST'

如果两个表都没分析，那么dynamic sampling不起作用，分析其中一个表

SQL> analyze table test1 compute statistics;

Table analyzed.

SQL> select count(*) from test a,test1 b where a.a=b.a;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=1 Bytes=17)
   1    0   SORT (AGGREGATE)
   2    1     HASH JOIN (Cost=19 Card=23706 Bytes=403002)
   3    2       TABLE ACCESS (FULL) OF 'TEST1' (Cost=6 Card=23706 Byte
          s=94824)

   4    2       TABLE ACCESS (FULL) OF 'TEST' (Cost=6 Card=23706 Bytes
          =308178)

dynamic sampling起作用了。

在10g里面有点区别，即使2个表都没分析过，dynamic sampling也可以起作用

SQL 10G>alter session set optimizer_dynamic_sampling=1;

Session altered.

SQL 10G>select count(*) from test a,test1 b where a.a=b.a;

Execution Plan
----------------------------------------------------------
Plan hash value: 2909046986

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

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

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

|   0 | SELECT STATEMENT    |       |     1 |    26 |       |   151   (4)| 00:00
:02 |

|   1 |  SORT AGGREGATE     |       |     1 |    26 |       |            |
    |

|*  2 |   HASH JOIN         |       | 43244 |  1097K|  1056K|   151   (4)| 00:00
:02 |

|   3 |    TABLE ACCESS FULL| TEST1 | 43235 |   548K|       |    21   (5)| 00:00
:01 |

|   4 |    TABLE ACCESS FULL| TEST  | 45439 |   576K|       |    21   (5)| 00:00
:01 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."A"="B"."A")

Note
-----
   - dynamic sampling used for this statement

其实归根到底是由于10g没有了基于规则的优化器，在9i里面如果一个查询中的所有表没有统计数据，那么他将选择基于规则的优化器而忽略dynamic sampling，而在10g因为这个原因所以dynamic sampling生效。