数据库性能故障—SQL案例2

问题描述：

某业务系统相继出现sql问题导致cpu资源飙升，达到90%以上甚至100%，影响了部分业务的正常进行。

问题分析：

1. 分析系统11月15日的awr报告（08:00—16:00时间段），发现sql_id为1gvyjy5z5mvgw的sql占用了系统29.3%的CPU资源（实际在13:30之后已经恢复正常） sql如下：
   

SELECT *

FROM CC_SERVICE_ORDER_ASK_HIS

WHERE PROD_NUM = :1

AND ACCEPT_DATE >= add_months(sysdate, –2)

AND ORDER_STATU = 700000103

1. 查看11月14日的同时段的awr报告，该sql没有出现在top 10中。说明该sql在11月14日是正常的，不存在问题。
   

1. 针对sql_id为1gvyjy5z5mvg的sql，生成2个时段的sql详细报告，发现11月15日sql plan发生了改变，走的是accept_date（CC_SER_ORD_ASK_HIS_SEC_IDX）的时间索引，而11月14日之前走的是prod_num（CC_SER_ORD_ASK_HIS_THR_IDX）索引
   

11月14日之前该sql正确的sql plan（节选）plan hash value:
1723649039

11月15日该sql错误的sql plan（节选）plan hash value:
555285981

1. 从上述的sql plan可以看出，15日和15日之前的sql plan有明显的差别，虽然走的都是索引，但是索引列不同，导致了cpu资源消耗的差别。
   
2. 同样的sql，为何sql plan会不同，而且是发生在11月15日这天。查看sql plan的改变的具体时间，如下：为11月15日凌晨3:54。最后一次该sql的正常执行是在11月14日晚上23:46（该sql并不是一直都在执行，因此在时间上可能不会太连续）。
   

1. 查询该表及相关列的历史统计信息，由于是11月15日13点完成的统计信息重新收集，因此运行如下sql查询accept_date列的相关信息，可以看到，该表最新的分析时间是2012年9月14日，而列accept_date的最大值为2012/9/14 23:27
   

1. 由于accept_date的最大值为2012/9/14 23:27，而sql语句的where条件为accept_date>add_months(sysdate,-2)。理论上，在2012/11/14 23:28开始这个语句应该是走的accept_date索引（因为从统计信息上来分析，走accept_date索引为最佳方案，在这之后已没有数据，也就是走该索引不存在数据选择）。但由于2号节点还保留有该sql的sq plan，即该sql未被aged out（还保留在shared pool），因此在2012/11/14 23:46的时候sql plan还是正确的。而在1号节点由于该sql的sql plan已经被aged out了，因此该sql重新做硬解析的时候就自然地走了accept_date索引。导致了sql plan的异常，cpu开销增大。
   
2. 基于上述故障，我们可以重现。查询目前表CC_SERVICE_ORDER_ASK_HIS中accept_date列的最大值，最后统计信息收集时间为2012/11/15 12:05，因此accept_date的最大值为2012/11/15 11:46:52。
   

1. 如果修改刚刚执行的sql，将where条件修改为如下accpet_date>sysdate -5，则该sql语句肯定是走accept_date（CC_SER_ORD_ASK_HIS_SEC_IDX）索引。如下：
   

1. 但实际上，走prod_num（CC_SER_ORD_ASK_HIS_THR_IDX）才是最佳选择，我们分别按2个sql plan执行该sql得出时间比较如下：
   

1. 手工指定hint走prod_num（CC_SER_ORD_ASK_HIS_THR_IDX）索引，得到的结果及执行时间如下：
   

1. 比较上述2个结果发现，2个sql plan执行时间分别为1.9s和0.5s。当然这只是模拟5天的数据差别，而实际上我们发生故障的sql条件为add_months(sysdate,-2)，为2个月之前，数据量明显增加，相应地sql执行时间以及消耗cpu的资源就明显增加。
   
2. 上述故障原因就是由于表统计信息未及时更新导致了accept_date列的内容停留在2012/09/14 23:27:04，从而使得sql plan认为where条件的accept_date>add_months(sysdate,-2)的sql plan应该是走accept_date为最佳路径（因为在2个月之内通过accept_date索引没有数据，不存在数据选择）。
   
3. 出现故障之后对该表重新进行统计信息收集，使得accept_date及相关列的统计信息得到了及时更新，sql plan恢复正常，系统也从而恢复正常。
   

相关建议：

1. 由于Oracle 10g之后增加了自动收集表统计信息的定时任务，在一定程度上简化了dba的工作。但也存在着局限性，首先该任务依赖于系统参数job_queue_processes，该参数如果设置为0，则任务无法正常进行。再者就是Oracle的机制为表的数据量变动必须是超过了10%才会触发该任务。上述故障就是由于表统计信息未更新所导致（表的数据变动量未超过10%）。
   
2. 出现类似的故障，可以通过如下技术手段来进行快速处理，恢复业务：
   
   1. 重新收集表的统计信息，使得sql plan恢复正常
      
   2. 对于11g以下的版本，通过sql profile以及outline来固定sql plan（正确的sql plan存在的前提下），对于11g版本，则可以通过sql plan management实现sql plan的管理
      
   3. 可以通过手工修改相关列的maxvalue值（DBMS_STATS.set_column_stats）来实现统计信息的更新
      
3. 对于时间与其他字段相混合的条件，建议评估时间索引字段的索引的必要性，如果无必要则可删除时间字段索引或者创建联合索引。如果确实需要，则必须保持表统计信息的实时性。或者通过sql profile或者强制 hint来固定sql plan

未经允许不得转载：SRE空间 » 数据库性能故障—SQL案例2