应用程序执行周期性时快时慢问题解决一例

       做Oracle DBA，经常会遇到一些性能问题，有些性能问题是一开始就很慢的，有些性能问题是逐渐变慢的，有些性能问题是突然变慢的，而有性能问题时快时慢的，不知道其他同行觉得哪种性能问题比较好处理，今天我在这里分享一个性能问题周期性问题时快时慢的案例，用于总结反思，如有错误请不吝指正。

       最近一位应用运维同事发邮件请求协助，反映有一套应用系统跑批期间周期性时快时慢，而且很规律。周一到周五晚上批量的计算耗时很长，要6-8小时；而周六和周日晚上批量计算耗时很快，只需要15分钟左右。看到邮件中描述的这一现象，有经验的老司机可能一下子就想到了问题可能出现在哪里。但对于经验不足我来说，解决这种问题还是比较吃力的。

        于是拉了一个微信群，在群里详细询问了，跑批快和慢的具体时间。这里多提一句，在我接手这个case之前有一个同事也查了这个问题，他根据awr从dbtime到redo log生成量再到事务都做了一些分析，还做成了折线图，在群里发了一堆，但就是没有做一些详细的询问和分析。最终也没有一个结论。

        言规正传，从开发和应用运维那里得到一些详细信息之后，首先从awr下手。分别取到快的时间和慢的时间区间的awr报告。以我的能力，从各项指标没有看出数据库整体负载有什么问题。但在top SQL那部分发现了两个时间段的TOP SQL确实是不同的。在慢的时间段有一条update语句73320次，耗时9869.7s，而在快的时间段的TOP SQL中却没有显示这条update语句。难道这就是问题所在吗？

        于是询问开发同事，update语句是否为批量期间执行的语句。得到肯定的答复后，开始怀疑是不是工作日和周末跑批的逻辑不同的，但awr中信息否定了我的猜测，原来这条update语句也在周末也跑了，只不是跑的很快，跑了75331次，耗时24.07s。从目前 的线索来看，之条update语句执行效率无疑是导致程序批量时快时慢的原因。但是为什么会出现这种现象现在还不得而知。

        问题sql定位了，剩下的问题就简单了，把sql优化掉，每次批量都在24s完成，那问题自然就解决了。现在开始寻找sql执行时快时慢的原因。这里介绍一个很强大的工具awrsqrpt，这个工具可以获取awr中记录的sql语句的历史执行计划。使用awrsqrpt取到两个时间段sql的执行计划，分别如下两个图所示：

        从执行计划中可以看出，都走了表的主键，选择了不同的方式，其中INDEX RANGE SCAN平均单次执行时间为384.3ms，而INDEX SKIP SCAN的平均单次时间是0.3ms，差了3000多倍，难怪批量执行时间差异如此之大。看到这个，脑子中反映出来的第一个解决方法是固定执行计划，强制走INDEX SKIP SCAN。但这种方法治标不治本。只能用在实在找不到问题原因的情况下，或紧急的情况下。

        继续与开发沟通，得知，update语句中的表，每天批量后都会被truncate掉，这是一个很重要的信息。难道就是由于这一个truncate操作导致sql语句执行效率差异如此之大吗？我们继续往下分析。

        我们都知道，Oracle的执行计划都是通过CBO，根据表上的统计信息，而估算出来Oracle认为最做优的执行计划。也就是不论INDEX RANGE SCAN 还是SKIP SCAN，Oracle都认为是最优的，难道是Oracle优化器出现了问题了吗？应该不是的，如果优化器这么容易出问题，那Oracle在商用数据库也不会称霸这么久了。于是想到了表上的统计信息，通过查询视图sys.wri$_optstat_tab_history得到表上的历史统计信息如下图：

        从上面的图上可以看到，表上的统计信息时而为0，时而为很大，看来就是统计信息导致CBO在选择执行计划时，没有选择它应该选的最优执行计划。

        从上面的分析来看，应该是找到了问题的根本原因，批量表每次批量完成后都会做truncate操作，数据库默认每天都会自动收集表的统计信息，周一到周五22：00开始收集，周末6：00开始收集。从而导致数据库在不同时间点收集到表的统计信息是不同的，进而导致了优化器基于统计信息来选择了慢的执行计划。

        原因找到了，就开始讨论解决方法，这里列出来几种方法应该都可以解决这个问题：

        1、在批量导入数据后，对批量表做一次统计信息收集

        2、锁定批量表在有数据时的统计信息

        3、truncate操作改为批前执行(开发提出)

        4、固定问题sql的执行计划(可能解决不了问题)

        反思：

        1、我们在解决问题时，不能只从数据库整体层面来分析，有时可能是一条本身性能不是很差的sql导致出的性能问题

        2、多沟通、细沟通，把尽可能掌握多的信息点，有助于问题的解决

        3、从性能慢的sql中应该也可以猜想到问题原因，Oracle评估的cost 为0

        以上为整个case的一个解决过程，欢迎指正。