Oracle动态采样深入理解

129次阅读

共计 15005 个字符，预计需要花费 38 分钟才能阅读完成。

动态采样 (Dynamic Sampling) 是在 Oracle 9i Release 2 中开始引入的一个技术，引入它的目的是为了应对数据库对象没有分析（统计信息缺失）的情况下，优化器生成更好的执行计划。简单的说，在数据库段（表、索引、分区）对象没有分析的情况下，为了使 CBO 优化器得到足够多的信息以保证优化器做出正确执行计划而发明的一种技术。它会分析一定数量段对象上的数据块获取 CBO 需要的统计信息。动态采样技术仅仅是统计信息的一种补充，它不能完全替代统计信息分析。

注意：动态采样在 Oracle 11g 之前称为 Dynamic Sampling, ORACLE 12c 之后改名为 Dynamic Statistic.
Oracle11G R2 默认的采样级别：
SQL> show parameter optimizer_dynamic_sampling
NAME TYPE VALUE
———————————— ———– ——————————
optimizer_dynamic_sampling integer 2

SQL> show parameter Dynamic Statistic
NAME TYPE VALUE
———————————— ———– ——————————
optimizer_dynamic_sampling integer 2

动态采样的级别有 11 个级别：请自行查看官方文档
http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF30101

动态采样实验：

1、创建测试表 test
SQL> create table test as select * from dba_objects;

Table created.

SQL> select count(1) from test;

COUNT(1)
———-
86259

2、不使用动态采样，查看执行计划
SQL> set autotrace traceonly explain;
SQL> select /*+ dynamic_sampling(test 0) */ * from test;

Execution Plan
———————————————————-
Plan hash value: 1357081020

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 100K| 19M| 336 (1)| 00:00:05 |
| 1 | TABLE ACCESS FULL| TEST | 100K| 19M| 336 (1)| 00:00:05 |
————————————————————————–

Note
—–
– dynamic sampling used for this statement (level=2)

从上面可以看出，次数优化器估计表 test 的行数显示为 100K，我们再看下面使用动态采样的执行计划，优化器会估算多少行：

3、使用动态采样，查看执行计划（下面是直接查询的，因为在 11G 是默认启用动态采样的）
SQL> select * from test;

Execution Plan
———————————————————-
Plan hash value: 1357081020

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 72258 | 14M| 336 (1)| 00:00:05 |
| 1 | TABLE ACCESS FULL| TEST | 72258 | 14M| 336 (1)| 00:00:05 |
————————————————————————–

Note
—–
– dynamic sampling used for this statement (level=2)

如果启用动态采样(默认情况下，动态采样级别为 2)，优化器根据动态采样得到一些数据信息猜测、估计表 TEST 的记录行数为 86259，已经接近实际记录行数 72258 了。比不做动态采样分析要好很多了。

如果我们将动态采样的级别提高为 3，如下所示，发现优化器根据动态采样得到的信息比默认（默认情况下，动态采样级别为 2）情况获得的信息更准确。优化器估计表 TEST 的行数为 92364，比 72258 又接近实际情况一步了。

SQL> select /*+ dynamic_sampling(test 3) */ * from test;

Execution Plan
———————————————————-
Plan hash value: 1357081020

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 92364 | 18M| 336 (1)| 00:00:05 |
| 1 | TABLE ACCESS FULL| TEST | 92364 | 18M| 336 (1)| 00:00:05 |
————————————————————————–

Note
—–
– dynamic sampling used for this statement (level=2)

4、在 Tom 大师的这篇文章中提到，在没有动态采样的情况下，如果删除了该表数据，CBO 优化器估算的结果集和没有删除之前是一样的。
这是因为当一个表的数据被删除后，这个表所分配的 extent 和 block 是不会自动回收的（高水位线不变），所以 CBO 如果没有采样数据块做分析，只是从数据字典中获取 extend 等信息，就会误认为任然还有那么多数据。下面我们把 test 表数据清空，看看执行计划如何

SQL> delete from test;

86259 rows deleted.

SQL> commit;

SQL> select /*+ dynamic_sampling(test 0) */ * from test; —- 不使用动态采样
Execution Plan
———————————————————-
Plan hash value: 1357081020

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 100K| 19M| 336 (1)| 00:00:05 |
| 1 | TABLE ACCESS FULL| TEST | 100K| 19M| 336 (1)| 00:00:05 |
————————————————————————–

SQL> select * from test; —– 使用动态采样

Execution Plan
———————————————————-
Plan hash value: 1357081020

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 207 | 335 (0)| 00:00:05 |
| 1 | TABLE ACCESS FULL| TEST | 1 | 207 | 335 (0)| 00:00:05 |
————————————————————————–

Note
—–
– dynamic sampling used for this statement (level=2)

从上面的查看可以看出，不采用动态采样和采用动态采样的区别；

更多详情见请继续阅读下一页的精彩内容：http://www.linuxidc.com/Linux/2017-05/143401p2.htm

5、我们对 test 表收集下统计信息：再次查询，该表的执行计划就会少了：dynamic sampling
SQL> select * from test;

Execution Plan
———————————————————-
Plan hash value: 1357081020

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 207 | 335 (0)| 00:00:05 |
| 1 | TABLE ACCESS FULL| TEST | 1 | 207 | 335 (0)| 00:00:05 |
————————————————————————–

SQL>

第二种情况：当表 TEST 即使被分析过，如果查询脚本里面包含临时表，就会使用动态采样技术。因为临时表是不会被分析，它是没有统计信息的。如下所示：

SQL> drop table test;

SQL> create table test as select * from dba_objects;

SQL> exec dbms_stats.gather_table_stats(ownname =>’SYS’,tabname =>’TEST’,cascade=>TRUE);

SQL> create global temporary table tmp (object_type varchar2(19));

SQL> insert into tmp select distinct object_type from dba_objects;

44 rows created.

SQL> commit;

然后查看下面查询语句的执行计划：

SQL> select t.owner,l.object_type from test t inner join tmp l on t.object_type=l.object_type;
Execution Plan
———————————————————-
Plan hash value: 19574435

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 1 | 26 | 338 (1)| 00:00:05 |
|* 1 | HASH JOIN | | 1 | 26 | 338 (1)| 00:00:05 |
| 2 | TABLE ACCESS FULL| TMP | 1 | 11 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST | 86260 | 1263K| 336 (1)| 00:00:05 |
—————————————————————————

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

1 – access(“T”.”OBJECT_TYPE”=”L”.”OBJECT_TYPE”)

Note
—–
– dynamic sampling used for this statement (level=2)

SQL>
从上面可以看到虽然是对 tmp 表执行的而是全表扫描，但是优化器只是估算了 1 行数据

6、动态采样还有一个独特能力，可以对不同列之间的相关性做统计。
表统计信息都是相对独立的。当查询涉及列之间的相关性时, 统计信息就显得有些不足了, 请看 Tom 大师的例子

6.1、创建一个特殊的表 t，然后对字段 flag1、flag2 创建索引 t_idx，然后分析收集统计信息
SQL> create table t as select decode(mod(rownum,2),0,’N’, ‘Y’) flag1, decode(mod(rownum,2),0,’Y’, ‘N’) flag2, a.* from all_objects a;
SQL> create index t_idx on t(flag1, flag2);

SQL> begin
dbms_stats.gather_table_stats(user, ‘T’,
method_opt =>’for all indexed columns size 254′);
end;
/

PL/SQL procedure successfully completed.

6.2、查看表的行数：
SQL> select num_rows, num_rows/2, num_rows/2/2 from user_tables where table_name=’T’;

NUM_ROWS NUM_ROWS/2 NUM_ROWS/2/2
———- ———- ————
84396 42198 21099

6.3、看看对 flag1 过滤条件的 SQL 语句的执行计划：
SQL> select * from t where flag1=’N’;

Execution Plan
———————————————————-
Plan hash value: 1601196873

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 42937 | 4276K| 342 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 42937 | 4276K| 342 (1)| 00:00:05 |
————————————————————————–

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

1 – filter(“FLAG1″=’N’)

从上面的执行计划可以看出：CBO 优化器猜测、估计的行数 42937，相当接近 42198 记录数了

6.4、看看对 flag2 过滤条件的 SQL 语句的执行计划：
SQL> select * from t where flag2=’N’;

Execution Plan
———————————————————-
Plan hash value: 1601196873

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 41459 | 4129K| 342 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 41459 | 4129K| 342 (1)| 00:00:05 |
————————————————————————–

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

1 – filter(“FLAG2″=’N’)

从上面的执行计划可以看出：CBO 优化器猜测、估计的行数 41459，相当接近 42198 记录数了

6.5、如果条件 flag1 = ‘N’ and flag2 = ‘N’, 我们根据逻辑推理判断这样的记录肯定是不存在的，这也是苦心构造这个特例的初衷。下面看看 CBO 优化器怎么探测、预测的

SQL> select * from t where flag1 = ‘N’ and flag2 = ‘N’;

Execution Plan
———————————————————-
Plan hash value: 1601196873

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 21093 | 2101K| 342 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 21093 | 2101K| 342 (1)| 00:00:05 |
————————————————————————–

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

1 – filter(“FLAG2″=’N’ AND “FLAG1″=’N’)

从上面看：CBO 估计的记录数为 12468，和实际情况相差非常远。其实是 CBO 优化器这样估算来的：

flag1=‘N’ 的记录数占总数的 1 /2
flag2= ‘N’ 的记录数占总数的 1 /2

6.6、根据 NUM_ROWS/2/2 =12468. 这样显然是不合理的。下面我们通过提升动态采样级别，来看看动态采样是否能避免 CBO 的错误：
SQL> select /*+ dynamic_sampling(t 3) */ * from t where flag1 = ‘N’ and flag2 = ‘N’;

Execution Plan
———————————————————-
Plan hash value: 470836197

————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————-
| 0 | SELECT STATEMENT | | 6 | 612 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 6 | 612 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 6 | | 1 (0)| 00:00:01 |
————————————————————————————-

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

2 – access(“FLAG1″=’N’ AND “FLAG2″=’N’)

Note
—–
– dynamic sampling used for this statement (level=2)

注意：
①：采样级别越高，采样的数据块越多，得到的分析数据就越接近于真实，但同时伴随着资源消耗的开销也增加了。这时一个需要权衡考虑的东西。Oracle 10 g & 11g 的默认采样级别都为 2, 一般使用在会话中使用 dynamic_sampling 提示来修改动态采样级别。

②：凡事有利必有弊，动态采样也不是神器。它采样的数据块越多，系统开销就越大，这样会增加 SQL 硬解析的时间，如果是数据库仓库（DW、OLAP）环境，SQL 执行时间相当长，硬解析时间只占整个 SQL 执行时间的一小部分，那么可以适当的提高动态采样级别，这样是有利于优化器获取更加正确的信息。一般设置为 3 或 4 比较合适。

③：在并发比较严重的 OLTP 系统中，每秒中有成千上万的 SQL 语句执行，它要求 SQL 语句短小、执行时间短，所以在 OLTP 系统中应该减低动态采样级别或不用动态采样。

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-05/143401.htm