ORACLE 11.2.0.4 for HPUNIX 业务SQL处理数据量变化导致的CPU使用率超标触发告警

    某客户oracle 11.2.0.4 rac for HPUNIX系统，由于业务SQL处理的数据量变化导致的CPU使用率超标，引起告警。

一、问题描述

    2018年9月22日，2:50:00核心库的CPU使用率达到87%触发告警。

二、提取的分析日志

 提取数据库问题时段数据库告警日志；取数据库2018年9月22日 1:00:00~2:00:00、2:00:00~3:00:00的awr及其对比；取数据库2018年9月22日 1:00:00~2:00:00、2:00:00~3:00:00的的OSW并生成图表观察2个时间点的CPU使用情况进行对比。

三、问题分析

 1、oswatch提取CPU使用率相关对比

2018-9-21 1:00:00~3:00:00

2018-9-22 1:00:00~3:00:00

根据2018-9-21~22 1:00:00~3:00:00系统使用率对比，确定2018-9-22 1:30:00~2:00:00，系统的CPU使用率接近90%。

 2、对比2018-9-21~22 1:00:00~2:00:00两个时间段的awr

    2.1 数据库2018-9-21~22 1:00:00~2:00:00两个时间的DBTIME对比

    从数据库dbtime对比，2018-9-21 1:00:00~2:00:00 dbtime指标值是3923，2018-9-22 1:00:00~2:00:00 dbtime指标值是4230，相差300并不算太多。

    2.2 2018-9-21~22 1:00:00~2:00:00两个时间的LOAD PROFILE对比

 

    2018-9-21 1:00:00~2:00:00 loadprofile parses指标值高达337每秒，2018-9-22 1:00:00~2:00:00 loadprofile parses指标值只有21.2；2018-9-21 1:00:00~2:00:00 loadprofile Hard parses指标值高达154每秒，2018-9-22 1:00:00~2:00:00 loadprofile parses指标值只有10.7。从loadprofile对比可知，2018-9-22 1:00:00~2:00:00，硬解析比较严重，硬解析严重会导致latch争用事件，引起数据库服务器CPU飙高。

 2018-9-21 1:00:00~2:00:00 数据库顶级等待事件对比，2018-9-22 1:00:00~2:00:00 等待事件latch:row cache objects、latch:cache buffers chans严重，吻合loadproile中硬解析相关指标Hard parses、parses高的情况。

2.3数据库顶级等待事件对比

  2018-9-21~22 1:00:00~2:00:00 数据库TOP SQL ordered by Elapsed Time对比，2018-9-22 1:00:00~2:00:00数据库自动任务SQL tunning Advisor激活并占用比较多的CPU资源。 

   2.4 2018-9-21~22 1:00:00~2:00:00两个时间对比发现的问题SQL

    2018-9-22 1:00:00~2:00:00时段有一条SQL语句占用CPU比较突出：1小时内执行5645次，每次执行平均耗时14.56秒。

 af535njf3m8fv

select * from adkmx where ((((faredm=:b0 and jiluzt='0') and yngyjg=:b1) and jioyrq=:b2) and joyisj='LN_JIEX') order by HUOBDH, DAIKZH, MXXHAO

    

     对比af535njf3m8fv的执行历史，发现2018-9-22日，该sql语句fetch的数据量394304比平时的6300多了近70倍，行处理776277比平时1400多了近554倍，cpu消耗54548.460比平时的700多近80倍，这是af535njf3m8fv在执行时CPU消耗过高的真正原因。

四、问题总结

   1、af535njf3m8fv在2018-9-22日 fetch的数据量394304比平时的6300多了近70倍，行处理776277比平时1400多了近554倍，cpu消耗54548.460比平时的700多近80倍，是af535njf3m8fv在执行时CPU消耗过高的真正原因。

2、另外，2018-9-22 1:00:00~2:00:00相比正常时段2018-9-21 1:00:00~2:00:00，数据库的硬解析比较突出。导致硬解析严重的原因是，应用的部分sql语句没有使用绑定变量，数据库没有关闭sql自适应游标共享。

  3、2018-9-22 1:00:00~2:00:00相比正常时段2018-9-21 1:00:00~2:00:00，数据库有SQL TUNNING ADVISOR自动维护任务执行，并且消耗比较高的CPU资源。

五、建议

 1、  请甲方老师联系业务评估SQL(af535njf3m8fv)处理数据量的合理性，避免数据库服务器夯死；

 2、  数据库关闭sql游标共享；

 3、  调整应用sql语句使用绑定变量；

 4、关闭数据库STA（SQL Tunning advisor）；