監控工具DPA發現海外一臺Oracle資料庫伺服器DB Commit Time指標告警，超過紅色告警線（40毫秒左右，黃色告警是10毫秒，紅色告警線是20毫秒），如下截圖所示，生成了對應的時段的AWR報告，發現Top 5 Timed Events裡面，log file sync等待事件的平均等待時間... ...
監控工具DPA發現海外一臺Oracle資料庫伺服器DB Commit Time指標告警，超過紅色告警線（40毫秒左右，黃色告警是10毫秒，紅色告警線是20毫秒），如下截圖所示，生成了對應的時段的AWR報告，發現Top 5 Timed Events裡面，log file sync等待事件的平均等待時間為37毫秒，log file parallel write等待事件的平均等待時間為40毫秒
如果對Tanel Poder：:Understanding LGWR, Log File Sync Waits and Commit Performance這篇文章所講述的內容很熟悉的話（經典圖如下），那麼通過等待事件log file sync與log file parallel write的Avg Wait(ms）指標： 37ms & 40ms,基本上可以判斷就是redo log所在的磁碟I/O出現了性能問題
然後在用lfsdiag.sql腳本分析一下詳細的統計數據，如下所示：
HISTOGRAM DATA FOR LFS AND OTHER RELATED WAITS:
APPROACH: Look at the wait distribution for log file sync waits
by looking at "wait_time_milli". Look at the high wait times then
see if you can correlate those with other related wait events.
INST_ID EVENT WAIT_TIME_MILLI WAIT_COUNT
---------- ---------------------------------------- --------------- ---------- 1 log file sync 1 4363 1 log file sync 2 835 1 log file sync 4 1650 1 log file sync 8 4937 1 log file sync 16 146252 1 log file sync 32 606674 1 log file sync 64 263377 1 log file sync 128 253254 1 log file sync 256 21 log file switch completion 1 124
1 log file switch completion 2 9
1 log file switch completion 4 19
1 log file switch completion 8 21
1 log file switch completion 16 35
1 log file switch completion 32 97
1 log file switch completion 64 133
1 log file switch completion 128 326
1 log file switch completion 256 1736
1 log file switch completion 512 3042
1 log file switch completion 1024 2020
1 log file parallel write 1 0
1 log file parallel write 2 0
1 log file parallel write 4 80
1 log file parallel write 8 2142
1 log file parallel write 16 170987
1 log file parallel write 32 779205
1 log file parallel write 64 311463
1 log file parallel write 128 79688
1 log file parallel write 256 42763
1 log file parallel write 512 13052
1 log file parallel write 1024 20468
1 log file parallel write 2048 14020
1 log file parallel write 4096 921
1 log file parallel write 8192 96
1 log file parallel write 16384 18
1 log file parallel write 32768 18
1 log file parallel write 65536 8
1 log file parallel write 131072 2
1 LGWR wait for redo copy 1 8516 1 LGWR wait for redo copy 2 20 1 LGWR wait for redo copy 4 19 1 LGWR wait for redo copy 8 20 1 LGWR wait for redo copy 16 11ORDERED BY WAIT_TIME_MILLIINST_ID EVENT WAIT_TIME_MILLI WAIT_COUNT
---------- ---------------------------------------- --------------- ---------- 1 log file sync 1 43631 log file switch completion 1 124
1 log file parallel write 1 0
1 LGWR wait for redo copy 1 8516 1 log file sync 2 8351 log file switch completion 2 9
1 log file parallel write 2 0
1 LGWR wait for redo copy 2 20 1 log file sync 4 16501 log file switch completion 4 19
1 log file parallel write 4 80
1 LGWR wait for redo copy 4 19 1 log file sync 8 49371 log file switch completion 8 21
1 log file parallel write 8 2142
1 LGWR wait for redo copy 8 20
