Oracle某行系統SQL優化案例(三)

問題說明：   

業務人員反饋有一個跑批作業中的一條 update 耗時變長，之前 2 分鐘執行完成，最近需要 30 多分鐘還未返回結果。

環境說明：

DB:Oracle 11.2.0.3.0 RAC

OS:AIX 7.1

問題分析：

猜測update語句變慢有如下幾個原因：
(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"等待事件，可以知道私網通訊的包處理效率低或者包的處理存在異常。

"gc cr block lost" 和 "gc current block lost"等待事件出現的原因：
Troubleshooting gc block lost and Poor Network Performance in a RAC Environment (Doc ID 563566.1)
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的環境上，交換機的錯誤配置

綜上所述，出現 gc cr/current block lost 主要原因：

(1)網線/網路卡/交換機出現問題。
(2)網路相關引數配置不合理。

收集問題期間 AWR 報告：

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 值設定偏小

官方的建議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
三、驗證
切換完網路卡後，業務反饋恢復正常，可以正常跑批，檢查netstat -s|grep fragments值也不在繼續增加。

#####chenjuchao 2021-08-13 13:00#####