深入理解 Oracle 动态采样

动态采样介绍

Oracle 动态采样（Dynamic Sampling，12c 称为 Dynamic statistics)，是对统计信息的⼀个重要补充。

有这样一种业务场景，当数据动态变化，无法用典型的统计信息描述时，怎么保证表的统计信息是准确的？

这时候动态采样就可以派上用场了。动态采样可以给在解析时对表中数据进行采样，为优化器器提供准确的估算值（cardinality）。

动态采样的主要有以下几个应用场景：

一个经典的场景就是业务场景中的临时表，ETL数据清洗转换过程中或者BI 系统中存放计算报表都会用到临时表。

这些临时表可能是 Oracle 中的 global temporary table，也可能是正常的堆表。

临时表中的数据时动态变化的，数据量在不同时间点变化很大，这就会对表的统计信息收集造成很大影响，没有一种合适的统计信息使优化器产生合适的执行计划。

这种场景适合采用动态采样技术，删除临时表上的统计信息，并且锁定统计信息，不让搜集统计信息的Job更新临时表上的统计信息，查询临时表时，优化器会对临时表进行动态采样，以确定临时表的 cardinality。

另一个场景是在单表上使用组合过滤条件，并且组合过滤条件并不是简单的相等操作，或者在过滤列上使用转换函数，无法使用 column group 扩展统计信息，简单使用多个列上的统计信息也无法产生合适的统计信息。

12c 之前，动态采样只能预估单表 cardinality，12c 版本，Oracle 对动态采样做了很大的增强，可以估算 group by 的聚合结果集和连接结果集的 cardinality。

使用动态采样，优化器往往可以获得高质量的估算值，从⽽而产生更优化的执行计划。

本文将介绍三种动态采样的适用场景。

1、临时表和动态采样，优化器器动态采样解析

以笔者优化过的一家客户CRM系统为例：

分析语句执行时间经常需要5分钟以上，通过分析SQL的执行计划，发现执行计划的估算值偏差离谱。

比如SQL Monitor 报告中，对于表A优化器器估算值为1，表连接方式采用nested loop最佳，实际执行计划也是nested loop ，但表实际数据量为一千万行，被驱动表被访问了一千万次。

通过查看表的统计信息，可以发现搜集统计信息时，表A中没有数据， Num_Rows 为0行， 所以优化器器估算为1行。

虽然A是正常堆表，但是在业务中做临时表使用，数据是动态生成和删除的。

解决方案：

删除表A统计信息并且进行锁定，保证后续对临时表的查询会使用动态采样，得到准确的估算值。
Exec dbms_stats.delete_table_stats(‘CRM’,’A’);
Exec dbms_stats.lock_table_stats(‘CRM’,’A’);

优化后再执行结果秒出。

2、复杂查询的动态采样

对于有复杂的过滤条件的sql, 为了在执行计划中得到正确的cardinality, 统计信息未必有帮助, 包括extended statistics. 

比如下面in和like的组合条件, 或者where条件中使用了自定义的函数.
status in (‘COM’, ‘ERR’) and v1 like ‘10%’

这时候dynamic sampling可能是唯一的选择. 下面是一个例子, 采用level为6的采样之后, cardinality更更为接近真实的数据.

构造一个1万行数据的测试表，搜集统计信息。
create table t1 as
with v1 as (
select /*+ materialize */
rownum id from dual connect by level <= 1000
)
select
rownum id,
rpad(rownum, 10, '0') v1,
trunc((rownum - 1)/100) n1,
case
when mod(rownum,100000) = 7 then 'ERR'
when rownum <= 9990000 then 'COM'
when mod(rownum,10) =0 then 'NEW'
when mod(rownum,10) between 1 and 5 then 'PRP'
when mod(rownum,10) between 6 and 8 then 'FKC'
when mod(rownum,10) = 9 then 'LDD'
end status,
rpad(rownum, 100) padding
from v1, v1
where rownum <= 1e6;
begin
dbms_stats.gather_table_stats(user,'t1');
end;
/

测试 SQL，估算值为395行，实际值为11113行，差距为30倍左右。

select
count(*)
from
t1
where
status in ('COM', 'ERR')
* and v1 like '10%'

SQL> /
COUNT(*)
----------
11113
Execution Plan
----------------------------------------------------------
Plan hash value: 4096694858
----------------------------------------------------------------------------------
-
| Id | Operation                | Name | Rows | Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------------
-
| 0  | SELECT STATEMENT         |      | 1    | 15    | 4983 (1)   | 00:00:01
|
| 1  | SORT AGGREGATE           |      | 1    | 15    |            |
|
|* 2 | TABLE ACCESS STORAGE FULL| t1   | 395 | 5925   | 4983 (1)   | 00:00:01
|
----------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("V1" LIKE '10%' AND ("STATUS"='COM' OR "STATUS"='ERR'))
filter("V1" LIKE '10%' AND ("STATUS"='COM' OR "STATUS"='ERR'))

使用动态采样，级别为6，估算值为16595行，实际为11113行，差距不到2倍，估算值的质量大幅提升。

SQL> select /*+ OPT_PARAM('OPTIMIZER_DYNAMIC_SAMPLING', 6) */
count(*)
from
t1
where
status in ('COM', 'ERR')
and v1 like '10%';
COUNT(*)
----------
11113
Execution Plan
----------------------------------------------------------
Plan hash value: 4096694858
----------------------------------------------------------------------------------
-
| Id | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------------
-
| 0  | SELECT STATEMENT         |      | 1     | 15    | 4983 (1)| 00:00:01
|
| 1  | SORT AGGREGATE           |      | 1     | 15    |         |
|
|* 2 | TABLE ACCESS STORAGE FULL| t1   | 16595 | 243K  | 4983 (1)| 00:00:01
|
----------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("V1" LIKE '10%' AND ("STATUS"='COM' OR "STATUS"='ERR'))
filter("V1" LIKE '10%' AND ("STATUS"='COM' OR "STATUS"='ERR'))
Note
-----
- dynamic statistics used: dynamic sampling (level=6)

3、12c 动态采样的增强，对连接和 group by 结果集的统计

测试SQL结果集为13行，12c中采样级别设为11，实际为auto时,CBO估算为12⾏，准确性很高，并且在表上有统计信息的情况下依然可以进行采样（采样级别设为6时，

不使用采样，清除表上的统计信息后，可发现采12c 动态采样的增强，对连接和 group by 结果集的统计样级别为6的时候，CBO估算值15743行，差别很大）

11G 版本采样级别为6时与12c 相同（注：动态采样的级别对于信息收集的准确度会有一定的影响，当然也会消耗额外的资源）。

总结

Oracle 动态采样在性能优化上有诸多应用场景，12c中更是得到加强，更深⼊入的了解动态采样的特性对性能优化有着重要的意义。