[aCloud] The IO Latency of the Oracle database file type Log File in the monitoring center has exceeded the threshold of 50ms for 3 consecutive times.
Problem Description
【aCloud】Monitoring Center Alert[Critical]IO Latency for Oracle database[xxx:1522/orcl] of file type Log File has been greater than the threshold of 50ms for 3 consecutive times
Effective Troubleshooting Steps
Possible reasons for the delay may include:
- Due to disk configuration, there may be IO performance issues (such as Pre-allocating disks).
- Due to a large number of transaction processing, log archiving, switching, and incomplete checkpoints may cause waiting, resulting in IO delay [increase the number of online log groups and larger log files].
- Query the size of the database redo log.
- SQL> select bytes/1024/1024 size_mb from v$log;
- SIZE_MB
- ———-
- 52
- 52
- 52
- 52
There are 4 redo log groups shown in the figure, each with a size of 52MB. If the business is busy or the data import operation may cause this alarm. It is recommended to increase the size of the redo log according to the size of the business. Generally, it is set to 512M.
3.2 Check the log switch frequency (the unit in the third column is minutes)
- select b.SEQUENCE#, b.FIRST_TIME,
- a.SEQUENCE#, a.FIRST_TIME,
- Calculate the difference between the values of a.FIRST_TIME and b.FIRST_TIME, multiply it by 24, and then multiply the result by 60. Finally, round the final value to 2 decimal places.
- from v$log_history a, v$log_history b
- where a.SEQUENCE# equals b.SEQUENCE# plus one.
- and b.THREAD#=1
- Order by a.SEQUENCE# desc.
[High-risk operation] If the redo log is too small or the switch is too frequent, you need to increase the size of the redo log and the number of redo log groups.
Solution
If there are no performance issues such as slow loading in the business, you can ignore or adjust the threshold of this monitoring item.
Original Link
https://support.sangfor.com.cn/cases/list?product_id=33&type=1&category_id=17562&isOpen=true