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

蒋健 2017-03-22 10:10:55

作者介绍

蒋健，云趣网络科技联合创始人，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。