深入理解Oracle动态采样.pdf

深⼊理解 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 。 使⽤动态采样，优化器往往可以获得⾼质量的估算值，从⽽产⽣更优化的执⾏计划。本⽂将介绍三种个动态 采样的适⽤场景。 临时表和动态采样 优化器动态采样解析 实际案例 ⾦融⾏业客户 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_dateyyyymmdd) , 估算值同样为⼀⾏，实际值为⼀千四百万⾏， 导致后续连接⽅式为 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_dateyyyymmdd) 的查询的估算值都为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(78740 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