深入理解 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 aswith v1 as (select /*+ materialize */rownum id from dual connect by level <= 1000)selectrownum id,rpad(rownum, 10, '0') v1,trunc((rownum - 1)/100) n1,casewhen 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) paddingfrom v1, v1where rownum <= 1e6;begindbms_stats.gather_table_stats(user,'t1');end;/




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

selectcount(*)fromt1wherestatus in ('COM', 'ERR')* and v1 like '10%'
SQL> /COUNT(*)----------
11113Execution 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(*)fromt1wherestatus in ('COM', 'ERR')and v1 like '10%';COUNT(*)----------11113Execution 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中更是得到加强,更深⼊入的了解动态采样的特性对性能优化有着重要的意义。

聚焦技术与人文,分享干货,共同成长
更多内容请关注“数据与人”

为您推荐

发表评论

您的电子邮箱地址不会被公开。