ReaderMX.
IT 邦德

加班1天找出元凶，一个诡异SQL把核心系统干翻车了...

I詹姆斯邦德007约 5 分钟
作者：中国DBA联盟(ACDU)成员，
PostgreSQL ACE Partner,
CSDN博客专家及B站知名UP主，全网粉丝10万+，
出版书籍《MySQL职场私房菜》。
目前服务于工业互联网,擅长主流Oracle、MySQL、PG、
高斯及Greenplum备份恢复，
安装迁移，性能优化、故障应急处理。

前言

Oracle 性能优化就是通过合理安排资源、调整系统参数使Oracle 运行更快、更节省资源，本案例详细分析了一次性能差导致的系统Down的过程

1.故障现象

本次故障刚开始是由于系统卡顿长达一个月之久，
也是近期直接爆发，核心业务系统出站异常，
设备报错，消息超时严重，产线停产
同时出站并发事务长达3秒，直接造成系统hang住
Image
Image

2.故障分析

2.1 AWR报告分析

以看出TP10等待事件在前10位的是什么事件，
基本上就可以判断出性能瓶颈在什么地方。
通常，在没有问题的数据库中，CPU time总是列在第一个。
Image
我们发现出现了gc buffer busy等待事件
gc buffer busy acquire说明：
session1尝试请求访问远程实例(remote instance)buffer，
但是在session1之前已经有相同实例上
另外一个session2请求访问了相同的buffer，
并且没有完成，那么session#1等待gc buffer busy acquire。
gc buffer busy release说明：
session1之前已经有远程实例的session#2请求访问了相同的buffer，
并且没有完成，
那么session1等待gc buffer busy release。
Image
从Load Profile来看
Executes：每秒SQL执行次数达2000多次，
并发很大
Image
Segments by Buffer Busy Waits显示
并发最大的历史表存在index争用，
index最右边的块存在分裂。
索引分裂集中在两个分区索引上

2.2 ASH分析

ASH报告是Oracle数据库中的一种性能诊断工具，
全称为Active Session History（活动会话历史记录），
它用于临时保存系统当前的性能诊断信息
Image
通过对活动会话跟踪发现，
gc current bock busy阻塞 
gc buffer busy release
Image
实例1和实例2都存在相同的
SQLID 25gcvqkbjt7jd，一直在争用

2.3 SQL Statistics

Image
通过对SQL执行计划的分析，发现这条诡异的SQL
执行计划错了，运行长达1个小时，长时间占有历史表
明显执行计划中显示的row跟实际的行数差异巨大
Image

2.4 网络排查

AWR中新引入的后台进程ping会定期测量网络统计信息。
它会定期唤醒(大约每5分钟唤醒一次)，
并测量消息传送和块传送的延迟。
在每次唤醒时，它向所有集群节点发送两条消息(分别为500字节和8192字节)，
计算往返延迟
Image
netstat -s看网路reassembles failed没有较大变化，
排查发现DB服务器和心跳交换机的 mtu 值一致
延迟正常, 平均ms级，网络一切正常

3.解决方案

3.1 LOAD_BALANCE为OFF

应用连接数据库的配置方式 LOAD_BALANCE为OFF
RAC_LS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
    (LOAD_BALANCE = OFF)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac_service)
    )
  )
LOAD_BALANCE=YES 表示的负载均衡，
这个是客户端的负载均衡，就是随机的进行连接分配.
同一数据在不同数据库实例上被请求访问。
如果应用程序可以实现，
那么我们建议不同的应用功能/模块数据分布在不同的数据库实例上被访问，
避免同一数据被多个实例交叉访问，可以减少buffer的争用，避免gc等待.

3.2 减少分区数据量

分区进行停机SPLIT，减少当前分区数据
通过对当前分区拆分，减少当前分区数据，减少热块竞争
ALTER TABLE sales
SPLIT PARTITION sales_q4_2020 AT (TO_DATE('01/01/2021', 'DD/MM/YYYY'))
INTO (PARTITION sales_q4_2020_pre_split VALUES LESS THAN (TO_DATE('01/01/2021', 'DD/MM/YYYY')),
PARTITION sales_q1_2021 VALUES LESS THAN (TO_DATE('01/04/2021', 'DD/MM/YYYY')));

3.3 避免谓词越界

谓词越界常见发生在where谓词是时间字段的，
总的来说统计信息记录的是一个过旧的时间，
而SQL传入的时间是一个最新的时间范围（往往是<time time1<c<time2），
由于统计信息不全，按照CBO计算出来的结果集就很小，
在多表关联的情况下，
CBO就会选择认为的最优的关联方式，而实际执行时发现不是那么回事，
有大量结果集需要扫描，就会爆发SQL性能问题。
谓词越界就是select的谓词的条件不在统计信息low_value 和 high_value 之间，
在实际选择结果集要大于CBO记录的结果集数量，
即实际的selectivity偏大，
这种情况下CBO评估出来的selectivity会出现严重的偏差，
导致CBO选错执行计划。
--如何避免谓词越界呢？
1.在Oracle中，避免谓词越界通常意味着要确保在使用BETWEEN时不要超出范围，
或者在使用IN、LIKE等操作时不要超出字符长度限制。
2.定期的手动收集统计信息

4.结论与建议

- 热点块(hot block)
在AWR中Segments by Global CacheBuffer Busy 记录了访问频繁的gc buffer.
解决方法可以根据热点块的类型采取不同的解决方法，比如采取分区表，分区索引，hash全局分区索引，反向index等等。
这点与单机数据库中的buffer busy waits类似。
- 低效SQL语句
低效SQL语句会导致不必要的buffer被请求访问，增加了buffer busy的机会。在AWR中可以找到TOP SQL。解决方法可以优化SQL语句减少buffer访问。这点与单机数据库中的buffer busy waits类似。
- 数据交叉访问。
RAC数据库，同一数据在不同数据库实例上被请求访问。
如果应用程序可以实现，那么我们建议不同的应用功能/模块数据分布在不同的数据库实例上被访问，避免同一数据被多个实例交叉访问，可以减少buffer的争用，避免gc等待。
- Oracle bug
建议安装Oracle推荐的最新Patch Set和PSU。
Patch set和PSU信息请参考：Oracle RecommendedPatches -- Oracle Database (Doc ID 756671.1)
目前Oracle RAC数据库版本为19.3基础版，建议RU升级到19.21
需求Oracle Database、Grid 和 OJVM 升级到次新或次次新的版本（补丁），版本信息参考DBA推荐的稳定版本

5.总结

性能分析要综合考虑，不能只看等待事件，还有结合低效SQL语句，本次案例就是一次非常好的演示

Oracle数据库优化是多方面的，原则是减少系统的瓶颈，减少资源的占用，提高系统的反应速度。例如，通过优化文件系统，提高磁盘I/O的读写速度；通过优化操作系统调度策略，提高Oracle在高负荷情况下的负载能力；优化表结构、索引、查询语句等使查询响应更快。

KEEP THE THREAD GOING

更多来源文章