Oracle update语句引起大量业务卡顿

记一次update语句引起大量业务卡顿分析处理过程，聊聊我的思路。技术人人都可以磨炼，但处理问题的思路和角度各有不同，希望这篇文章可以抛砖引玉。

以一个例子为切入点

一、问题背景

某业务模块反馈最近出现过几次业务卡顿，数据库中定位到有几个 insert into 语句的gc等待比较严重，虽然过一会就恢复了，还是想排查一下具体原因，消除潜在隐患。

基础环境：

• 主机类型：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）

问题现象：

业务卡顿，数据库层面gc等待严重。

简单说明：

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

二、分析说明

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

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

1、分析最近一次异常期间数据库日志

根据业务模块反馈，最近一次异常八点三十分左右。

AWR信息：

gc等待严重，等待次数高。

通过报告来看主要是三个insert语句引起的等待。

ASH信息：

查询各实例节点等待次数趋势情况，发现实例1并没有等待暴增的情况，而实例2在8：30时等待暴示，

进一步查询gc等待严重的sql语句是哪些：

可以看到这三个gc等待严重的SQL语句都是insert into语句，且是插入同一个表。这里和AWR的分析相吻合。

2、分析gc buffer busy acquire等待按块类型分类情况：

 (select *
    from (select /*+ materialize */
           inst_id,
           event,
           current_obj#,
           current_file#,
           current_block#,
           count(*) cnt
            from gv$active_session_history
           where event = 'gc buffer busy acquire'
           group by inst_id,
                    event,
                    current_obj#,
                    current_file#,
                    current_block#
          having count(*) > 5)
   where rownum < 101)
select *
  from (select inst_id,
               owner,
               object_name,
               object_type,
               current_file#,
               current_block#,
               cnt
          from ash_gc a, dba_objects o
         where (a.current_obj# = o.object_id(+))
           and a.current_obj# >= 1
        union
        select inst_id,
               '',
               '',
               'Undo Header/Undo block',
               current_file#,
               current_block#,
               cnt
          from ash_gc a
         where a.current_obj# = 0
        union
        select inst_id,
               '',
               '',
               'Undo Block',
               current_file#,
               current_block#,
               cnt
          from ash_gc a
         where a.current_obj# = -1)
 order by 7 desc

可以看到Undo Header/Undo block的统计次数最大，最严重的GC等待来自undo上的数据块，都是同一个表的gc传输。

3、跑批任务引起的卡顿？

根据开发反馈，昨晚有业务跑批，但一直没有执行玩所以kill了没提交，跑批的表正好是之前定位的问题表，写入表慢是否跟这个有关？

看了下跑批SQL，更新一张表，表中有六亿条数据，且没有分区。

另外实例2的lgwr写入存在写入延迟的问题，lgwr写入抖动很严重，2KB都要写516ms，lgwr写入慢，如果碰上大量的gc块获取，就会产生大量的gc等待，这里lgwr刷新需求和lgwr写入慢相应验证插入业务卡顿的故障现象。

继续查log file parallel write直方图：

同样验证log写入有比较严重的抖动现象。

四、结论

总结

综合以上的分析，可以确认本次故障是由于开发一条update语句条件错误导致大量的undo事务回滚，使在另一实例上的相同表的几个业务上insert into语句产生大量的gc buffer busy acquire等待，加上lgwr写入抖动加剧了等待时长，最终引起了前台业务卡顿。

针对本次故障，给予以下几个建议：

1. 应用上要尽量避免这样的操作异常造成的大量回滚，针对大表的DML/DDL操作需要更加慎重。
2. 为尽量避免GC等待，可以考虑进行应用划分，某个业务功能限制在一个节点中执行。
3. log file parallel write日志写入有严重的延迟，需要存储厂商配合进一步分析。
4. 当前大表创建成全局HASH分区表可能较合适的，索引也相应创建成分区索引，需要根据业务再讨论设计。