前一段时间,稳定运行了两个月的系统,其中一个功能突然变得非常慢,平常只需要零点零几秒完成的操作,需要等上百秒。一度导致系统无法使用影响生产。通过查看AWR报告、查看历史执行计划,发现一条Oracle为一条SQL选用了完全错误的执行计划:连接方式、索引使用情况和数据量预判都完全错误。开始认为是由于统计信息过期,就手动收集了这张表的统计信息。系统也立即恢复了正常。我当时判断这是一个偶然事件,但没有料到刚过了一周,同样的问题再次发生了。

虽然解决方案简单但由于影响严重,决定跟踪一下代码。发现这个操作所涉及到关联一张变更频率非常高的表,仅几分钟到几十分钟,这张表里的数据就完全变样。Oracle默认的统计信息收集频率应该是一天一次,大大低于表的变更频率。对于这个问题有两种方案:

  • 一是等到表张表里的数据分布有代表性时,手动将这张表的统计信息锁住,之后每次查询都使用相同的统计信息。
  • 二是利用oracle动态样例收集(Dynamic Sampling)的特性:清空这张表中所有的统计信息、相关索引的统计信息并锁住。当oracle执行语句前,若发现这张表没有可用的统计信息,则会按照配置参数,对这张表数据做一次采集,以生成较优的执行计划。


第一种方案可操作性不强。第二种看似影响效率,但是由于这些高频变更的表都不会是大表(大数据量),即便做采样也花销不大,况且总体对比起来,采样的时间比生成一个完全错误的执行计划花费的时间少得多。可使用以下语句清空锁定统计信息:

BEGIN
    DBMS_STATS.DELETE_TABLE_STATS(tablespace,tabname);
    DBMS_STATS.LOCK_TABLE_STATS(tablespace,tabname);
END;

oracle 11g中,dynamic sampling的默认开启,级别是2(级别配置详细介绍)。在11gR2版本后,oracle会自动决定是否开启dynamic sampling及使用的级别(详细介绍一)。可以通过dbms_xplan查看某条sql的执行计划,执行计划中最后note信息中会提示。

总得来说,这方案是牺牲best case的效率来换取worst case的效率与稳定性:开始动态样例后,肯定没有直接使用(正确)的统计信息更加快速,但也避免的worst case下,统计信息失效,误导生成错误的执行计划。

另外这期间找到一篇关于应对SQL突然变慢的文章。