Several Policies of OracleTuningLogFileSync waiting for events

When a frequent commitrollback or disk I/O problem occurs and a large number of physical read/write contention occurs, we will often see that the LOGFILESYNC wait event appears in the TOP

When a frequent commit/rollback or disk I/O problem occurs and a large number of physical read/write contention occurs, we will often see that the LOG FILE SYNC wait event appears in the TOP

When a frequent commit/rollback or disk I/O problem occurs and a large number of physical read/write contention occurs, we will often see that the LOG FILE SYNC wait event appears in TOP EVENTS, the metrics for evaluating log file sync wait events are the average WAIT time and the wait event histogram of AWR.

For OLTP, the average wait time 7 ms is normal, under normal circumstances the average wait time does not exceed 10 ms

Below are several optimization strategies,

(I) Optimize the I/O of REDO logs

If you can optimize the storage of REDO log files and store them to a faster disk, you can reduce the time for this wait event to wait for a single event.

(Ii) Increase LOG BUFFER

Increase the log buffer and increase the number of REDO bytes written to the redo log file each time on average.

This reduces the number of redo I/O operations and optimizes the REDO log file write wait time.

(Iii) Reduce the number of submissions

By increasing the number of submitted records, reducing the number of submitted batches, and effectively reducing the log file sync wait time

However, this method may need to change the application at a high cost.

(Iv) set some frequently committed transactions to asynchronous commit.

You can set the COMMIT_WRITE parameter to control asynchronous submission. This parameter supports system level, but also session level.

Among them, "IMMEDIATE, NOWAIT" is a commonly used optimization solution, which can be implemented through:

● Change the commit_write Parameter

● Direct command: commit write immediate nowait

Finally, Rocky wants to stick to this issue. We should establish baseline in the routine maintenance of the database)

If this indicator changes abnormally, it is necessary to analyze and solve the problem as soon as possible. Once this indicator deteriorates, it may cause a sharp decline in system performance, or even lead to a short suspension.