统计信息的偏差,导致SQL性能影响的一则案例

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 统计信息的偏差,导致SQL性能影响的一则案例

统计信息的偏差,导致SQL性能影响的一则案例

机场部门的同事,咨询了一个问题,

有个机场昨天上线一台新装基于Windows的Oracle 10g服务器,XXX应用在使用时基本正常,但有一项功能执行后没有反应,是个多表联查的SQL。这个在旧的服务器上操作是正常的。

于是做了些试验,把下面where条件中in的范围缩小到4个,能出来结果,扩大到8个,执行就明显变慢,再扩大就干脆没响应了

where a.id in ( 0, 5 , 25 , 31 , 34 , 11      , 13 , 14 , 21 , 22 , 35 , 8 , 24 , 27 , 32 , 33 , 7 , 9 , 15 , 16      , 29 , 4 , 12 , 17 , 28 , 1 , 2 , 19 , 20 , 23 , 3 , 6 , 18 , 30 ,      10 , 26 ) and b.id in ( 0, 5 , 25 , 31 , 34 , 11 , 13      , 14 , 21 , 22 , 35 , 8 , 24 , 27 , 32 , 33 , 7 , 9 , 15 , 16 , 29      , 4 , 12 , 17 , 28 , 1 , 2 , 19 , 20 , 23 , 3 , 6 , 18 , 30 , 10 ,      26 )    

昨天下午以为是TEMP表空间的问题,扩大到500M,没有改观。 昨天晚上10点多,系统突然就好了,当地未做任何修改。后来切到了备机进行观察。

根据现象,我追问了些问题,

1. a表和b表的数据量约为? 2. 这两张表用的是A或A_BAK表空间么? 3. “旧的服务器”,您指的是Oracle数据库装在了一台新的Windows服务器上? 4. 若3描述正确,且昨天晚上10点正常了,请问这台新的服务器,是从前一天晚上10点之后,做的数据迁移,并启动数据库? 5. 如果4描述正确,请问在此期间,DBA是否没有手工采集过统计信息? 6. 能否帮忙生成gqyr7h3t1snmz和ckmq9wkn92x4t这两个sql_id对应的SQL      AWR?若不清楚操作,可以参考《http://blog.csdn.net/bisal/article/details /54565444》。

朋友回复,

  1. a数据量约为7000-10000,b数据量约为100-200,数量级都不大,A表有索引。。
  2. 这两张表用的都是A表空间。A_BAK是备库,白天不涉及,夜间job会将3天前的历史数据转到备库。
  3. 是的,新旧服务器都是windows的,只是旧服务器时间太长担心有隐患,因此新采购了新机器。
  4. 新库基本不涉及数据迁移,只有几个表有静态数据,1中两个表中的数据,是从ZZZ应用直接下载下来的,下载的时间是前一天晚上11:20之后。白天安装,晚上23:00正式启用。
  5. 应该没做过统计信息采集。这个有没日志能看出来?
  6. 联系当地了,正在让他们做。

可能有些朋友,看见上述信息,就能猜出一二了。

select table_name, num_rows, last_analyzed from user_tables where table_name in (‘A’,’B’);

显示A表和B表均为3.15 22:00采集,A表num_rows为13000,B表num_rows为100。静态数据表或这几天没有业务数据的表,last_analyzed显示3.13 22:00,其他这两天有业务数据,则是3.15 22:00。从last_analyzed来看,可以推测,表示自动采集的,不是人工采集。

使用如下SQL,查看历史统计信息记录,

select savtime, rowcnt, blkcnt, avgrln, analyzetime from sys.wri$_optstat_tab_history where obj#=?;

统计信息的偏差,导致SQL性能影响的一则案例

10g以上的数据库,如果安装的时候,采用默认配置,默认会在每天22:00,根据规则,自动采集表、索引等的统计信息。

统计信息的作用,就是让Oracle可以根据这些数据,计算得出正确执行这条SQL的执行计划。

执行计划,就是执行这条SQL的路径,比如是用索引,还是全表扫描,如果用索引,具体用什么索引,两表连接,是用哈希连接,还是嵌套循环。可以说执行计划,就决定了这条SQL的执行效率。

再说现象,13日22:00开启新服务器,一直到14日22:00之前,运行一直比较慢。是因为这期间,有可能因为没有手工采集表的统计信息,对于Oracle来说,这是一张空表,因为错误的统计信息,导致Oracle选择了错误的执行计划,因此从现象上看是执行慢了。

14日22:00,Oracle自动采集统计信息任务开始,采集了正确的统计信息,此时Oracle就有了可能选择正确的执行计划。

IN中值不同,执行效率不同,是因为使用IN,Oracle会自动优化处理,IN值多了,就可能选择全表扫描,IN值少了,就有可能使用索引范围扫描,但是没有一个明确的边界,这是由Oracle根据统计信息,评估相应的成本决定的。因此有可能出现您说的,IN值少,执行快,IN值多,执行慢。

13日-14日22:00之间,数据为空,说明这期间没有采集统计信息,Oracle认为是空表,至少认为统计信息为空,所以选择何种执行计划,都是有可能的。

综合来讲,就问题现象来说,如果数据量、参数配置没变的前提,主要就是由于统计信息不准,导致的SQL执行效率抖动,当人工/自动采集统计信息,在准确的情况下,Oracle就有可能选择正确的执行计划。

比较疑惑的是,从AWR看,SQL语句每一步读取的rows都是1行,这和实际有些出入的,单就执行计划看,并不是一个高效的步骤,但是若按记录数,记录数很少,所以就掩盖了执行计划的不足,

统计信息的偏差,导致SQL性能影响的一则案例

A表采集的rowcnt是200多,实际上应该至少有几千。

我猜测,这可能和默认选择率有关,Oracle会根据默认选择率(10%),采集表的统计信息。

曾经类总提给我这个问题,可以参考《》,

统计信息的偏差,导致SQL性能影响的一则案例

曾经研究了一番,不同版本的默认采集率,并不是相同的,10g的值默认比较小,可能和这有关。

统计信息的偏差,导致SQL性能影响的一则案例

默认采集率比较小些,所以rows返回小了。

优化器在不同版本演进。其实我觉得,这两张表数据量比较小,即使采样100%,也是可以。采样率越高,执行时间就越久,这是影响。

要避免后续发生,可以有两种方法,

  1. 安装完数据库,若有大量数据的导入,可以手工采集统计信息。
  1. 安装完数据库,若条件允许,不要马上投入生产,可以先试运行,再投入使用。

如果新库,有大量数据迁移,建议导入完成,手工采集统计信息,这样可以让Oracle根据正确的数据,选择正确的统计信息。有了数据,再采集统计信息,才有意义。否则在默认统计信息采集前,由于采用错误的统计信息,可能导致SQL效率。所以建议要有大量数据的导入操作,一定要手工采集统计信息,这是dbsnake书中强调的。

其实简单来讲,Oracle选择执行计划,就需要知道这张表、索引,有什么样的数据量、有什么样的索引、索引有怎样的数据量,这些数据准确了,才能选择正确的执行计划。

这些数据就是来自统计信息,统计信息可以手工采集,也可以自动采集,自动采集使用默认选择率, 手工采集可以指定选择率,选择率越高,统计数据越准,但相应需要一些成本消耗,因此需要在准确性和可用性之间,做一些选择。

对于小表,有可能错误的执行计划,也会执行比较快,这是正常的,但是一旦数据量增加了,不同执行计划,执行效率就会有不同了。

同事的回复,

十分感谢,终于明白这个问题了,这几天一直奇怪,没有遇到过,现在看,之前其他机场是因为系统装完后都做了一段时间测试,有了测试数据,所以oracle执行了默认的采集动作,这个机场是下午装的数据库,也没测,晚上直接就上线了,正好赶上了。还一致怀疑和TEMP表空间太小有关系。

针对这一个问题,如果TEMP表小,其实无论是前端,还是数据库日志,都会记录,既然没有记录,说明这并不是根本原因了。

如果您觉得本文有帮助,欢迎关注转发:bisal的个人杂货铺,

统计信息的偏差,导致SQL性能影响的一则案例 统计信息的偏差,导致SQL性能影响的一则案例
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 统计信息的偏差,导致SQL性能影响的一则案例


 上一篇
来自MOS的一篇文章,《为何在查询中索引未被使用》 来自MOS的一篇文章,《为何在查询中索引未被使用》
前两天同事咨询一个EDB上SQL语句没有使用索引的问题,EDB号称是最像Oracle的数据库,所以一些Oracle上未用索引的经验,可以借鉴一下。 下面是从MOS上找的一篇文章,《为何在查询中索引未被使用 (Doc ID 1549
下一篇 
路由冲突对数据库的影响 路由冲突对数据库的影响
虚拟机环境中,执行SQL,提示未登录错误, 正常关库报错, 正常启库还是错, 看一下监听状态, 配置文件信息, /etc/sysconfig/networkNETWORK=yesHOST