动态采样大法好,让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。

 

 

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倍,估算值的质量大幅提升。

 

 

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

 

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

 

 

总结

 

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

 

温馨提示:点击以下链接可下载本文档及相关代码。
http://pan.baidu.com/s/1cxkMa6

最新评论
访客 2017年06月23日

为什么小表驱动大表比较快呢?

访客 2017年06月20日

学习

访客 2017年06月16日

水平太差,完全没看懂

访客 2017年06月14日

可以可视化吗?信息是挺全的

访客 2017年06月13日

学习了

活动预告