据库异常sql耗尽CPU资源
问题描述
核心系统出现数据库卡顿，严重影响前端业务响应，节点一数据库服务器CPU几乎耗尽，多次重启数据库依然没有效果，仔细诊断初步判定业务SQL执行效率不佳所致，特别是SQL_ID为1hwgwzgw0vfrc的SQL，半小时内执行次数比平时多了2300多次。高并发下大量消耗系统资源特别是CPU几乎被耗尽。最后通过修改SQL 1hwgwzgw0vfrc问题得以解决。
问题详细诊断过程
比较正常运行和异常时的awr报告，下面是获取9点半到10点的半个小时的awr报告，节点一正常时的数据库负载性能：
节点一异常时的数据库负载性能：
检查发现节点一DB Time在正常的时候消耗939.91分，cpu核数32核，已经接近满负荷，异常时消耗3421.59分，已经完全超负荷运行，导致数据库性能急剧下降。
检查CPU消耗情况，节点一正常情况
节点一发生性能问题
检查发现正常运行的时候CPU还有9.8%空闲，发生性能问题时，CPU资源根本没有剩余，满负荷在运行。
通过“SQL ordered by CPU Time”检查sql语句消耗cpu情况，正常时，“1hwgwzgw0vfrc”耗CPU整体达47.65%，如下图：
发生性能问题时，耗CPU整体达58.13%，如下图：
通过“SQL ordered by Elapsed Time”检查sql语句执行情况，正常时，“1hwgwzgw0vfrc”运行6549次，每次2.75秒：
发生性能问题时，执行8913次，每次14.62秒
由于原来cpu就快耗尽，异常时半个小时数据执行多了2000多次，导致CPU超负荷运行，整体性能下降，每次执行时间消耗变长。
检查节点二的awr报告，资源情况正常。
现场了解rac配置负载情况，客户是通过vip配置了两个节点，客户现场登录服务器，检查服务器CPU资源消耗情况：
节点一（WW01）
节点二（ww02）
发现两个节点资源使用极不均衡，建议调整应用配置，最好直接通过scanip连接。
获取ash报告检查语句性能消耗情况：
发现主要消耗语句sql_id为“1hwgwzgw0vfrc”，消耗近60%的资源，语句如下：
| SELECT aab001, aab004, nvl((select (select role_id from sys_user_account_role where user_id = t.aac001) from sys_user_account t where (businessid = to_char(si_ab01.aab001) or (select aab003 from sys_user_account_exts t1 where user_id=t.aac001)=si_ab01.aab003) and is_locked=‘0’ and type=‘02’ and rownum < 1.5), ‘-1’) role_id , nvl(bab078, ‘0’), nvl(bab084, ‘-1’), nvl(AAE119, ‘-1’), aab003, AAB019, nvl((select account from sys_user_account t where (businessid = to_char(si_ab01.aab001) or (select aab003 from sys_user_account_exts t1 where user_id = t.aac001) = si_ab01.aab003) and is_locked = ‘0’ and type = ‘02’ and rownum < 1.5), ‘-1’) account from si_ab01 WHERE aab999 = :1 and rownum<1.5 |
|---|
进一步分析
通过sql_id，检查执行计划：
select * from table(dbms_xplan.DISPLAY_CURSOR(‘1hwgwzgw0vfrc’, null, ‘ADVANCED ALLSTATS LAST’));
主要资源消耗在SYS_USER_ACCOUNT表上，其中PK_AC01_ACCOUNT_TYPE数据比较集中，由于数据查询比较复杂，在执行过程中也有做全表查询的，如下图：
这说明走全表查询在数据库层面认为消耗比走索引低，由于整个sql执行消耗资源较高，建议开发做sql语句改写。
开发修改该语句后，业务恢复，CPU消耗情况也恢复正常：
重新获取awr报告，检查CPU资源均正常。
故障总结
由于SQL语句（sql_id: 1hwgwzgw0vfrc）执行性能较差，导致CPU资源耗尽，影响了整体数据库运行，总结情况如下：
1、rac两个节点访问量不均衡，发生性能问题时节点一CPU资源耗尽，建议通过scanip去访问数据库。
2、发生异常情况时，消耗高资源(CPU)的sql语句“1hwgwzgw0vfrc”，sql执行次数比正常情况多，同正常比较，半个小时内就多执行了2300多次。
3、建议优化sql_id为“1hwgwzgw0vfrc”的sql语句，该语句已经优化，业务恢复正常。