Oracle & Starcraft

dynamic sampling对于我们来说其实不算太陌生，从9i r2开始，dynamic sampling其实已经不动声色地融入到数据库中了。
我们经常会碰到由于一些表没有分析导致执行计划错误的情况，但是dynamic sampling的出现一定程度的减少了错误的产生。dynamic sampling针对没有分析过的表可以采样估计表的选择性，对于生产正确的执行计划有一定的帮助。

dynamic sampling分为10个级别，从0－10，由参数optimizer_dynamic_sampling控制

下面是取自”Performance Tuning Guide and Reference “的对10种级别的定义

Level 0: Do not use dynamic sampling.
Level 1: Sample all tables that have not been analyzed if the following criteria are met: (1) there is at least 1 unanalyzed table in the query; (2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view; (3) this unanalyzed table has no indexes; (4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).
Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is the default number of dynamic sampling blocks.
Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for some predicate that is a potential dynamic sampling predicate. The number of blocks sampled is the default number of dynamic sampling blocks.
Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks.
Level 5: Apply dynamic sampling to all tables that meet the Level 4 criteria using 2 times the default number of dynamic sampling blocks.
Level 6: Apply dynamic sampling to all tables that meet the Level 5 criteria using 4 times the default number of dynamic sampling blocks.
Level 7: Apply dynamic sampling to all tables that meet the Level 6 criteria using 8 times the default number of dynamic sampling blocks.
Level 8: Apply dynamic sampling to all tables that meet the Level 7 criteria using 32 times the default number of dynamic sampling blocks.
Level 9: Apply dynamic sampling to all tables that meet the Level 8 criteria using 128 times the default number of dynamic sampling blocks.
Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.
dynamic_sampling hint的级别定义如下

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生效。