CPU飙升！故障分析解决过程详解

数据与人 2022-05-25

1247

点击上方"数据与人", 右上角选择“设为星标”

分享干货，共同成长！

记一次CPU飙升的分析处理过程，聊聊我的思路。技术人人都可以磨炼，但处理问题的思路和角度各有不同，希望这篇文章可以抛砖引玉。

以一个例子为切入点

一、问题背景

一大早接到告警，核心数据库RAC两主机CPU使用很高，90%以上，系统操作缓慢，业务卡顿。需要马上紧急处理，排查一下具体原因，消除潜在隐患。

基础环境：

主机类型：x3850 X6
操作系统：DB:CentOS Linux release 7.4.1708、APP:CentOS Linux release 7.2.1511 (Core)
存储：IBM存储，2TB,MULTIPATH
内存：64 G
CPU型号：E7-4830 v3 @ 2.10GHz ( 4 U * 12 core)
CPU核数：32CORE
数据库环境：11.2.0.4（RAC）

问题现象：

服务器CPU使用率90%以上，业务卡顿。

简单说明：

CPU使用高一般有以下几种原因？

排序、SQL解析、执行计划突变、全表扫描、会话阻塞等都会导致CPU飙升，可能的原因较多，需要层层递进，逐步定位根因；

分析方法包括哪些：

主要由主机top/topas占CPU高的进程查询相应SQL、会话增长趋势、阻塞分析、ASH/AWR报告分析、SQL执行时间/执行计划变化等；
了解业务场景，业务上有没有变更，例如开发功能变更、业务使用量增加等。

在很多应用场景中，数据库的稳定性直接决定了系统的稳定性。本文介绍一些通用的数据库问题处理技巧，健壮的数据库不能解决所有的问题，但是却能增加数据库运行的稳定性。

二、分析说明

通过分析日志定位、分析故障原因；
追溯历史数据，分析关键指标的历史波动,这些关键指标可以用来做为数据库健康度参考指标。
用实际数据来验证推断，排除掉其它干扰因素，定位数据库问题的根本原因，帮助快速修复。

三、疑问点排查及分析思路

1、主机top信息

可以通过top命令看一下单一进程使用CPU的情况，有CPU使用高的可以通过pid定位sid最终定位sql_id。

本次通过主机top信息看到进程使用CPU都很平均，无法直接定位是某个进程某个SQL引起的CPU使用过高的问题。

2、查询CPU top10的语句

set linesize 200 
col username for a15 
col event for a35 
col program for a20
col cpu_p for 99.99
select ta.*, round(ta.cpu_time  tb.total_cpu * 100, 1) cpu_usage
  from (select s.username,
               s.program,
               s.event,
               s.sql_id,
               sum(trunc(m.cpu)) cpu_time,
               count(*) sum
          from v$sessmetric m, v$session s
         where (m.physical_reads > 100 or m.cpu > 100 or
               m.logical_reads > 100)
           and m.session_id = s.sid
           and m.session_serial_num = s.serial#
           and s.status = 'ACTIVE'
           and username is not null
         group by s.username, s.program, s.event, s.sql_id
         order by 5 desc) ta,
       (select sum(cpu) total_cpu from v$sessmetric) tb
 where rownum < 11;

结合AWR信息：

可以看到，故障时段，853by1q5drtc2语句占用最大的CPU时间和逻辑读，接下去就是怎么优化这个SQL的问题。

2、sql信息

执行计划：

历史执行计划：

可以看到执行计划产生了不合理的变化，执行时间差异很大，这是CPU占用过大的根本原因。

3、执行计划为什么会改变？

表统计信息失效了？

select owner,
       table_name,
       partition_name,
       object_type,
       stale_stats,
       last_analyzed
  from dba_tab_statistics
 where table_name = upper('&table_name');

表统计信息确实失效了。

ORACLE是根据数据改变量大于10%才进行统计信息的收集，且对静态表并不收集统计信息。

至此，CPU使用率高的问题得到了最终解决，最终是由于统计信息没有正确收集导致的，

定位了问题，解决就很容易了，更新表统计信息后执行sql看一下效率及执行计划都恢复正常。

本次是表统计失效导致的执行计划改变，如果不是因为统计信息失效导致的，可以采用的优化方案包括不限于修改SQL、指定HINT的方式修正执行计划，实在无法修改SQL的再使用SPM或SQLPROFILE进行绑定等。

4、关于统计信息收集

1）当系统有很大的分区表时，如果总是全部收集则会比较慢，11g之后可以设置INCREMENTAL只对数据有变动的分区做收集，如下示：

exec dbms_stats.set_table_prefs('JD','IMS_RES_MONITOR_2','INCREMENTAL','TRUE');
exec dbms_stats.set_table_prefs(user,'table_name','INCREMENTAL','TRUE'); --只收集数据变动的分区
select dbms_stats.get_prefs('INCREMENTAL',null,'table_name') from dual;  --查看分区表INCREMENTAL的值

2）延长自动统计信息收集时间

自动收集统计信息的时间是周一到周五每晚22:00:00到第二天2:00结束，周六周日两天全天收集。可进一步考虑工作日时段延长2小时，即周一到周五时段统计信息收集到第二天4:00结束。

四、结论

总结

本次故障就是一次统计信息失真导致SQL执行计划走错的简单案例，但有时往往入手的思路错了，导致中间分析的道路过于曲折，在实践中，针对故障和问题需要充分考虑多个可能性，以求一击中的。

- END -

更多精彩内容，关注我们▼▼

cpu时间 sql优化 sql数据库数据库

文章转载自数据与人，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

CPU飙升！故障分析解决过程详解

2）延长自动统计信息收集时间

评论