Oracle-job跑批变慢案例

前言:

        近期处理了一起应用job跑批变慢的案例，在一天job执行时间突然增加了几十倍，后面通过分析是由于索引的设计不合理导致执行计划的异常，把当时的问题分析过程记录下来，分享给大家。

问题:

        数据库job存储执行时间增加几十倍

原因：

job存储过程里面的sql语句由于索引设计的不合理列XXX_COLOR，XXXTIME分别有单列索引，在执行计划生成时会通过比较走不同索引的cost消耗来选择索引，由于列数据的分布，统计信息收集的时间点，优化器算法，都会导致sql执行计划在不同索引之间来回切换，引发执行计划发生不稳定发送变化，导致存储过程执行缓慢

问题修复:

1 对列XXX_COLOR，XXXTIME创建组合索引。

2 对语句gm9pk0puuzqqm进行执行计划绑定，绑定169034354执行计划

分析过程:

        通过awr对比异常时间段8月4号11点-12点与正常时间段8月10号11点-12点的job存储过程SP_XXXXX_STATISTICS_RUYUE执行消耗差异，可以看到执行时间增长了1000+秒，主要的消耗在于逻辑读

        查看主要的逻辑读消耗对象为XXX_LOG表

        查看GPS_lOG对应的高消耗SQL: gm9pk0puuzqqm为SELECT AVG(L.XXX) FROM XXX_LOG L WHERE L.XXX_COLOR = :B3 AND L.XXXTIME >= :B1 AND L.XXXTIME < LEAST(:B2 ,  TRUNC(:B1 + 1))

        通过查看存储过程SP_XXX_STATISTICS_RUYUE里面的执行sql，确认有XXX_LOG的高消耗SQL: gm9pk0puuzqqm,这里可以确认存储过程执行的主要消耗在于高消耗SQL: gm9pk0puuzqqm

        查看SQL: gm9pk0puuzqqm的历史执行消耗，可以发现在20220804 7点-13点期间，执行计划发生了改变，从原来的169034354变化为2660240900，sql的执行消耗逻辑读，执行时间也发生增长

查看执行计划的消耗，169034354的平均执行时间为0.004秒，平均逻辑读为2176，2660240900的平均执行时间为0.051秒，平均逻辑读为35925，执行计划的差异主要在于索引的选择IDX_XXXLOG_XXX_COLOR以及IDX_XXXLOG_XXXTIME

由于列XXX_COLOR，XXXTIME分别有单列索引，在执行计划生成时会通过比较走不同索引的cost消耗来选择索引，由于列数据的分布，统计信息收集的时间点，优化器算法，都会导致sql执行计划在不同索引之间来回切换，导致执行计划的不稳定，引发sql执行缓慢问题

总结:

job存储变慢的原因为存储过程里面的sql语句由于索引设计的不合理列XXX_COLOR，XXXTIME分别有单列索引，在执行计划生成时会通过比较走不同索引的cost消耗来选择索引，由于列数据的分布，统计信息收集的时间点，优化器算法，都会导致sql执行计划在不同索引之间来回切换，引发执行计划发生不稳定发送变化，导致存储过程执行缓慢