动态采样大法好，让Oracle执行计划一步到位！

作者介绍

蒋健，云趣网络科技联合创始人，11g OCM，多年Oracle设计、管理及实施经验，精通数据库优化，Oracle CBO及并行原理，曾为多个行业的客户的 Oracle 系统实施小型机到 X86跨平台迁移和数据库优化服务。云趣鹰眼监控核心设计和开发者，资深Python Web开发者。（文章审校：杨建荣）

Oracle 动态采样（Dynamic Sampling，12c 称为 Dynamic statistics)，是对统计信息的一个重要补充，当数据动态变化，无法用典型的统计信息描述时，动态采样可以给在解析时对表中数据进行采样，为优化器提供准确的估算值（cardinality）。动态采样的主要有以下几个应用场景：

• 一个经典的场景就是业务场景中的临时表，比如 ETL 数据清洗转换过程中的临时表，比如 BI 系统中存放计算报表结果的临时表。这些临时表可能是 Oracle 中的 global temporary table，也可能是正常的堆表。因为临时表中的数据时动态变化的，不同时间点，临时表中的数据量变化很大，没有一种合适的统计信息使优化器产生合适的执行计划。这种场景适合采用动态采样技术，通常会删除临时表上的统计信息，并且锁定统计信息，不让搜集统计信息的 Job 更新临时表上的统计信息，查询临时表时，优化器会对临时表进行动态采样，以确定临时表的 cardinality。

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

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

使用动态采样，优化器往往可以获得高质量的估算值，从而产生更优化的执行计划。本文将介绍三种动态采样的适用场景。

优化器动态采样解析

实际案例

金融行业客户 CRM 系统的分析语句执行时间经常需要5分钟以上，通过分析 Top SQL的执行计划，发现执行计划的估算值偏差离谱，比如下图SQL Monitor 报告中，对于表P_CUST_STAT，优化器估算值为1，实际值为一千四百万行。导致后续连接方式为 nested loop，被驱动表被访问了一千四百万次。

通过表的统计信息，可以发现10月9号搜集统计信息时，表P_CUST_STAT中没有数据，Num_Rows为0行， 所以优化器估算为1行。虽然P_CUST_STAT是正常对表，但是在应用中被用于临时表，数据是动态生成和删除的。

另一个例子，下图 SQL Monitor 报告中，表B_S_CUST_STAT的过滤条件为Data_date = to_date(20161008,'yyyymmdd'), 估算值同样为一行，实际值为一千四百万行，导致后续连接方式为 nested loop outer，被驱动视图表访问了一千四百万次。

通过表B_S_CUST_STAT的统计信息，统计信息收集时间为10月9号早上8点，Num_Rows为一千三百七十万行记录，看起来表上的统计信息是正确的。

继续查看B_S_CUST_STAT列上的统计信息，Data_date 列上只有一个唯一值，为10月7号。表B_S_CUST_STAT只存放一天的数据，当统计信息搜集时，表中的数据为10月7号的数据。之后，数据被替换为10月8号的数据，统计信息并没有及时更新，导致当天之后对表 P_CUST_STAT的使用 Data_date = to_date(20161008,'yyyymmdd') 的查询的估算值都为1。

create or replace function raw_to_date(i_raw raw)

return date

as

m_n date;

begin

dbms_stats.convert_raw_value(i_raw,m_n);

return m_n;

end;

/

select raw_to_date('78740A07010101') stats_value from dual;

STATS_VALUE

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

2016-10-07 00:00:00

解决方案

删除表B_S_CUST_STAT和P_CUST_STAT的统计信息并且进行锁定，保证后续对临时表的查询会使用动态采样，得到准确的估算值。

Exec dbms_stats.delete_table_stats(‘CRM’,’B_S_CUST’);

Exec dbms_stats.lock_table_stats(‘CRM’,’B_S_CUST’);

Exec dbms_stats.delete_table_stats(‘CRM’,’P_CUST_STAT’);

Exec dbms_stats.lock_table_stats(‘CRM’,’P_CUST_STAT’);

对于有复杂的过滤条件的sql, 为了在执行计划中得到正确的cardinality, 统计信息未必有帮助, 包括extended statistics. 比如下面in和like的组合条件, 或者where条件中使用了自定义的函数。

status in (‘COM’, ‘ERR’) and v1 like ‘10%’

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

构造一个1百万行数据的测试表，搜集统计信息。

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

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

测试SQL结果集为13行，12c中采样级别设为11，实际为auto时，CBO估算为12行，准确性很高，并且在表上有统计信息的情况下依然可以进行采样（采样级别设为6时，不使用采样，清除表上的统计信息后，可发现采样级别为6的时候，CBO估算值15743行，差别很大）

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

原文发布时间为：2017-03-22

本文来自云栖社区合作伙伴DBAplus