Oracle优化07-分析及动态采样-动态采样

思维导图

这里写图片描述

07系列文章

Oracle优化07-分析及动态采样-直方图

Oracle优化07-分析及动态采样-DBMS_STATS 包

Oracle优化07-分析及动态采样-动态采样

动态采样Dynamic_sampling

动态采样（ Dynamic Sampling）技术的最初提出是在 Oracle 9i R2，在段（表，索引，分区）没有分析的情况下，为了使 CBO 优化器得到足够的信息以保证做出正确的执行计划而发明的一种技术，可以把它看做分析手段的一种补充。

当段对象没有统计信息时（即没有做分析），动态采样技术可以通过直接从需要分析的对象上收集数据块（采样）来获得 CBO 需要的统计信息。

示例演示：

SQL> create table t as select object_id , object_name from dba_objects ;

Table created

SQL> select count(1)  from t;

  COUNT(1)
----------
     35249

SQL> 

这里创建了一张普通表，没有做分析，我们在 hint 中用 0 级来限制动态采样，此时 CBO 唯一可以使用的信息就是表存储在数据字典中的一些信息，如有多少个extent，有多少个 block，但是这些信息是不够的。

SQL> select /*+dynamic_sampling(t 0) */ * from t;

SQL> select a.SQL_ID,a.CHILD_NUMBER ,a.SQL_TEXT from v$sql a where a.SQL_TEXT 
like 'select /*+dynamic_sampling(t 0) */ * from t%';

SQL> select * from table(dbms_xplan.display_cursor('0crj75han452z',0));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0crj75han452z, child number 0
-------------------------------------
select /*+dynamic_sampling(t 0) */ * from t
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    43 (100)|          |
|   1 |  TABLE ACCESS FULL| T    | 12498 |   964K|    43   (0)| 00:00:01 |
--------------------------------------------------------------------------

13 rows selected

SQL> 

在没有做动态分析的情况下，CBO 估计的记录数是 12498 条，与真实的 35249相差甚远。

我们使用动态采样来看一下

这里写图片描述

• dynamic sampling used for this statement (level=2) 在 Oracle 10g 以后默认对没有分析的段做动态采样。

dynamic sampling used for this statement (level=2)

使用了Level 2 级的动态采样， CBO 估计的结果是 37883 与 35249很接近了。

注意一点： 在没有动态采样的情况下，对于没有分析过的段， CBO 也可能错误地将结果判断的程度扩大话。

示例演示：

SQL> delete from t;

35249 rows deleted

SQL> commit;

Commit complete

SQL> alter system flush shared_pool;

System altered

SQL> 

查看执行计划：

SQL> select /*+dynamic_sampling(t 0) */ * from t;
SQL> select * from t;

SQL> select a.SQL_ID,a.CHILD_NUMBER ,a.SQL_TEXT from v$sql a where a.SQL_TEXT 
like 'select /*+dynamic_sampling(t 0) */ * from t%';

SQL> select a.SQL_ID,a.CHILD_NUMBER ,a.SQL_TEXT from v$sql a where a.SQL_TEXT 
like 'select * from t%';

SQL> select * from table(dbms_xplan.display_cursor('0crj75han452z',0));

SQL> select * from table(dbms_xplan.display_cursor('ckzaraqcmkr2f',0));

这里写图片描述

这里写图片描述

可能看出 2 个执行计划的差别。 在没有采用动态分析的情况下， CBO 对 t 表估计的还是 12498 行记录，但是用动态分析就显示 1 条记录。

而表中的数据在查询之前已经删除掉了。

出现这种情况的原因是因为高水位。 虽然表的数据已经删除，但是表分配的 extent 和 block 不会被回收（高水位值不变），所以在这种情况下 CBO 依然认为有那么多的数据在那。

通过这一点，我们可以看出，此时 CBO 能够使用的信息非常有限，也就是这个表有几个 extent，有几个 block。 但动态采样之后， Oracle 立即发现，原来数据块中都是空的。

动态采样的作用

动态采样有两方面的作用：

• CBO 依赖的是充分的统计分析信息，但是并不是每个用户都会非常认真，及时地去对每个表做分析. 为了保证执行计划都尽可能地正确，Oracle 需要使用动态采样技术来帮助 CBO 获取尽可能多的信息。
• 全局临时表。 通常来讲，临时表的数据是不做分析的，因为它存放的数据是临时性的，可能很快就释放了，但是当一个查询关联到这样的临时表时， CBO 要想获得临时表上的统计信息分析数据，就只能依赖于动态采样了。

动态采样除了可以在段对象没有分析时，给 CBO 提供分析数据之外，还有一个独特的能力，它可以对不同列之间的相关性做统计。

相对的，表分析的信息是独立的。 如：

（ 1） 表的行数，平均行长。 （ 2） 表的每个列的最大值，最小值，重复率，也可能包含直方图。 （ 3） 索引的聚合因子，索引叶的块数目， 索引的高度等。

这些信息相互之间是独立的，当查询涉及到列之间的相关性时，这些信息就显得不足够了。

案例说明：

##建立T表，2个列 Flag1和Flag2 ，对应 Y，N
SQL> create table t as
  2         select  decode(mod(rownum ,2) , 0 , 'Y','N') flag1 ,
  3                       decode(mod(rownum ,2),0,'N','Y') flag2
  4                  from all_objects a;


Table created

##在两个列上建立一个联合索引 
SQL> create index ind_t on t(flag1,flag2);

Index created

 ##对表和索引进行分析
SQL> begin 
  2  dbms_stats.gather_table_stats(user,'t',method_opt => 'for all indexed columns size 254');
  3  end ;
  4  /

PL/SQL procedure successfully completed

SQL> 

这里写图片描述

SQL> select  a.NUM_ROWS ,a.NUM_ROWS/2 ,a.NUM_ROWS/2/2  from user_tables a where a.TABLE_NAME='T';

  NUM_ROWS A.NUM_ROWS/2 A.NUM_ROWS/2/2
---------- ------------ --------------
     33872        16936           8468

SQL> 

查看表的总量 , 总量的1/2 , 总量的1/4.

我们获取这个值是要和CBO估算出来的结果集的数量做对比，以判断CBO估算的正确性。

下面我们来看下面两条SQL的执行计划：

select * from t where t.flag1=’N’;

这里写图片描述

因为我们对表做了分析，所以CBO使用了统计分析数据，而不会再使用动态采样。

在这个试验中，CBO估算出来的结果集我们从图中可以看到16744 非常接近与实际值 ，因为对已flag1来讲 ，等于N的记录是整个记录的一半（等于Y的记录是另一半），这在统计信息里面可以获取到，此时CBO表现正常。

select * from t where t.flag2=’N’

这里写图片描述

同样对于 flag2=N的情况，CBO表现也是完美的。

那我们来看下下面这个情况呢？

select * from t where t.flag1=’N’ and t.flag2=’N’;

这里写图片描述

我们从表中的数据可以推断出，其实是不存在这种这种数据的，结果为0。

我们来看下CBO的估算结果集的表现 8467 ，和0天相差甚远了….

那么CBO是如何估算的呢？

Flag1=N 占总数的1/2 Flag2=N 占总数的1/2

所以Flag1=N and Flag2=N 的记录数是 1/2*1/2=1/4*33872=8468

所以CBO估算出的结果集比较错误，原因是因为它没有能够对列之前的相关性做出估算，所以得出了偏差非常大的估算。

那我们来看下动态采样的效果吧

这里写图片描述

此时 CBO的估算的结果集是1，和实际数0 非常相近了。

由于结果集很小，所以执行计划选择了索引，这才是最优的执行计划。

这就是动态采样， 尽管看到动态采样的优点，但是它的缺点也是显而易见，否则 Oracle 一定会一直使用动态采样来取代数据分析： （ 1） 采样的数据块有限，对于海量数据的表，结果难免有偏差。 （ 2） 采样会消耗系统资源，特别是 OLTP 数据库，尤其不推荐使用动态采样。

动态采样的级别

Level 0

不做动态分析

Level 1

Oracle 对没有分析的表进行动态采样，但需要同时满足以下 4 个条件。

（ 1） SQL 中至少有一个未分析的表 （ 2） 未分析的表出现在关联查询或者子查询中 （ 3） 未分析的表没有索引 （ 4） 未分析的表占用的数据块要大于动态采样默认的数据块（ 32 个）

Level 2

对所有的未分析表做分析，动态采样的数据块是默认数据块的 2 倍。

Level 3

采样的表包含满足 Level 2 定义的所有表，同时包括，那些谓词有可能潜在地需要动态采样的表，这些动态采样的数据块为默认数据块，对没有分析的表，动态采样的默认块为默认数据块的 2 倍。

Level 4

采样的表包含满足 Level 3 定义的表，同时还包括一些表，他们包含一个单表的谓词会引用另外的 2 个列或者更多的列；采样的块数是动态采样默认数据块数；对没有分析的表，动态采样的数据块为默认数据块的 2 倍。

Level 5， 6， 7， 8， 9

采样的表包含满足 Level 4 定义的表，同时分别使用动态采样默认数据块的2， 4， 8， 32， 128 倍的数量来做动态分析。

Level 10

采样的表包含满足 Level 9 定义的所有表，同时对表的所有数据进行动态采 样。

采样的数据块越多，得到的分析数据就越接近与真实，但同时伴随着资源消耗的也越大。

什么时候使用动态采样

动态采样也需要额外的消耗数据库资源，所以，如果 SQL 被反复执行，变量被绑定，硬分析很少，在这样一个环境中，是不宜使用动态采样的，就像 OLTP系统。 动态采样发生在硬分析时，如果很少有硬分析发生，动态采样的意义就不大.

而在 OLAP 或者数据仓库环境下， SQL 执行消耗的资源要远远大于 SQL 解析，那么让解析在消耗多一点资源做一些动态采样分析，从而做出一个最优的执行计划是非常值得的。 实际上在这样的环境中，硬分析消耗的资源几乎是可以忽略的。

所以，一般在 OLAP 或者数据仓库环境中，将动态采样的 level 设置为 3 或者 4 比较好。 相反，在 OLTP 系统下，不应该使用动态采样。