Wait event: log file sync

log file sync: This wait event occurs during redo log writing from log buffer to log file

When the user process commits, LGWR will be notified to write the redo buffer to the redo file. When LGWR completes the write operation, LGWR will notify the user that the process is complete. The user process does not complete the commit until it receives the LGWR notification. Therefore, the user process waits until it receives LGWR notification, and log file sync is generated.

Log writes occur when:

1、commit

2. Output the log buffer every 3 seconds

3. When the log buffer is full by one third

4. Before DBWR writes dirty data to a data file

When log buffer is greater than 1M

Reasons for log file sync:

Frequent commits or rollbacks, check if the app has too many short things, and if so, batch can be used to mitigate

2. The log buffer is too large, because the amount of data in the log buffer cannot reach log_io_size, resulting in more redo entries accumulated in the log buffer. At this time, when the transaction commits or 3s wakes up, LGWR will write all the data to redo file. Due to the excessive amount, LGWR will have to wait more time for redo to finish (In this case, you can adjust the log_io_size parameter to a smaller value, the default value is 1/3 or 1M of log_buffer)-- It is possible to have a large log buffer, but a smaller log_io_size increases the number of background writes, thereby reducing log file sync latency.

3. CPU load is high, LGWR cannot obtain CPU schedule in time, log file sync appears

Log file sync average wait time is more than 7ms, indicating that log write time is too long each time

Solution:

1. Check whether log commits are frequent. If they are too frequent, try to reduce commit.

2. Ensure sufficient CPU resources

3. It is recommended to increase the log buffer

Optimize redo log file storage on faster disks