Oracle某行系统SQL优化案例(三)

业务人员反馈有一个跑批作业中的一条update耗时变长，之前2分钟执行完成，
最近需要30多分钟还未返回结果。
复制

DB:Oracle 11.2.0.3.0 RAC
OS:AIX 7.1
复制

(1)数据量发生了变化。
经检查，数据量没有太大变化。
(2)执行计划发生了变化。
经检查，执行计划并没有发生改变。
(3)出现锁等待。
问题期间，检查此update操作并没有被阻塞。
(4)触发器
并没有触发器。
(5)数据库出现性能瓶颈。
通过等待事件可以看到最严重的的两个等待事件分别是"gc cr block lost" 和 "gc current block lost"
复制

通过tfactl收集问题时间段的日志。
$ORACLE_HOME/bin/tfactl diagcollect -asm -crs -database all -os -from "Jul/26/2021 08:00:00" -to "Jul/26/2021 08:10"
没有执行成功，默认11203没有安装tfactl。
复制

在检查update语句时，业务又反馈出现大批量select语句也执行慢的问题，比之前慢很多。
此刻意识到并不是某一个SQL出现了问题，可能是整个数据库出现了瓶颈。
根据"gc cr block lost" 和 "gc current block lost"等待事件，可以知道私网通信的包处理效率低或者包的处理存在异常。
复制

1.网线/网卡/交换机问题
2.UDP设置太小/UDP buffer socket溢出
3.私网性能差，出现packet reassembly failures
4.网络传输坏块
5.通信通道中设置了不匹配的MTU的值
6.使用非专用的私网链接
7.服务器/交换机缺少“邻接”（adjacency）配置
8.配置了 IPFILTER
9.过时的网卡驱动程序
10.特别的私网链接和网络协议
11.错误配置的网卡绑定/链路聚合
12.错误的巨帧（Jumbo Frame）配置
13.网卡双工（ Duplex）模式不匹配
14.私网通信链路流量控制（Flow-control）不匹配
15.OS，网卡，交换机层面的数据包丢弃
16.网卡驱动/固件配置问题
17.网卡发送(tx)和接受（rx）队列的长度
18.有限的负载能力和过于饱和的带宽
19.过度的CPU申请和调度延迟
20.和交换机相关的数据包处理问题
21.QoS对私网数据包处理产生的负面影响
22.重聚过程中生成树限电
23.STREAMS队列的sq_max_size 配置太小
24.VIPA和DGD设置不正确（仅限Aix平台）
25.Solaris+Vertis LLT的环境上，交换机的错误配置
复制

(1)网线/网卡/交换机出现问题。
(2)网络相关参数配置不合理。
复制

gc cr block lost等待了1万多次，平均等待时长568ms
通过AWR报告中的Global Cache Load Profile可以看到Estd Interconnect traffic(KB)只有400多KB，流量也不大。
复制

发现有大量的fragments dropped after timeout
sy-creditrisk-db02[/home/oracle]$netstat -s|grep fragments
21625481 fragments dropped after timeout
no -a | grep -E
'udp_sendspace|udp_recvspace|tcp_sendspace|tcp_recvspace|rfc1323|sb_max|ipqmaxlen|tcp_ephemeral|udp_ephemeral'
no -a | grep ephemeral
复制

官方的建议udp_sendspace = ((DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT) + 4 KB)
数据库的block_size应该是8192,多块读是128
可以考虑调整以下值：
# usr/sbin/no -p -o sb_max=20971520
# usr/sbin/no -p -o tcp_sendspace=1048576
# usr/sbin/no -p -o tcp_recvspace=1048576
# usr/sbin/no -p -o udp_sendspace=2097152
# /usr/sbin/no -p -o udp_recvspace=20971520
复制

但是由于这些值一直没有改过，并且其他很多数据库都是这种配置，
并且出现问题期间，RAC节点间通信流量并不大，
所以问题很有可能出在网络上，需要重点排查网卡、交换机的。
复制

测试两节点私有网络ping无延时和丢包现象。
最后发现节点1私有网卡光衰特别大，怀疑光模块损坏。
复制

1.查看bond网卡信息
lsdev -Cc adapter
2.查看bond卡绑定信息
lsattr -El ent01 ---绑定后的网卡名
确认ent01由ent2和ent3绑定的bond网卡
3.查看bond卡网卡流量输出状态
entstat -d ent01 |more
确认ent2为主网卡，流量包输出
4.强制漂移网卡
输入如下命令，点击回车
smitty etherchannel
5.选择：Force A Failover In An EtherChannel / Link Aggregation
选择要漂移网卡回车-回车确认
6.查看bond卡网卡流量输出状态
entstat -d ent01 |more
复制