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执行计划在不同索引之间来回切换,引发执行计划发生不稳定发送变化,导致存储过程执行缓慢