Log File sync (Log File sync) and log file parallel write wait event

The log file sync (Log File sync) Wait event has a parameter: Buffer #. In Oracle Database 10 Gb, such wait events are located below commit wait events. When processing Log File sync wait events, pay attention to the following ideas:
◎ Log File sync wait time is related to the transaction middle finger (commit or rollback)
◎ When a process spends a lot of time on the log file sync event, this usually indicates too many commits or short transactions.

 

Common Causes, diagnoses, and actions
Oracle records changes in transactions and blocks in the log buffer in SGA. This is a method for physiological logging. By writing content to a log file at various times, the lgwr process is responsible for setting aside space in the log buffer.

The following conditions are met to trigger the lgwr process:
1. User submission
2. 1/3 of the redo log buffers were not written to the disk
3. The redo log buffer with a size greater than 1 MB is not written to the disk.
4. 3 seconds timeout
5. the SCN of the data to be written by dbwr is greater than that of the lgwr record. dbwr triggers lgwr writing.

The following conditions are met to trigger the dbwr process:
1. dbwr timeout, about 3 seconds
2. There is no extra empty buffer in the system to store data.
3. The ckpt process triggers dbwr.

 

Lgwr is the process responsible for writing the redo log buffer into the redo file. when the process is started, it will write the existing redo record in the redo buffer into the redo file, this process is triggered when the user commit or rollback is used to ensure the security of the data submitted by the user. This operation can be restored only when the data is written to the redo file.

 

Only after all the redo logs generated by a transaction are written to the redo log file does Oracle think that the transaction has been successfully committed. The redo log may be written to the redo log file before the transaction is committed.

Before the lgwr process starts writing the next redo log file, you must confirm that the redo log file to be overwritten has completed the following work:
* If the database is in non-archive mode, the transaction changes corresponding to all the redo records before the fully written redo log files are overwritten
All operation results must have been written to the data file.
* If the database is in archive mode, before overwriting a fully-written redo log file, not only must the modification operation results of all corresponding transactions be written to the data file, you also need to wait for the archiving process to complete Archiving

 

 

Write initiated by the user commit and rollback is called synchronous write; Other writes are written in the background. Log File sync
Wait is only related to synchronous writing. In other words, the user process may be processing a large transaction and generating many redo entries that trigger lgwr to write data in the background. However, the user session never needs to wait for the background write to complete. However, once the user session commits or rolls back its transaction and the _ wait_for_sync parameter is true, the process submits the lgwr and waits for the lgwr to refresh the current redo entry (including the submission mark) to the log file on the log file sync event. During this log synchronization, the lgwr process waits for the synchronization write to complete on the log file parallel write event, and the user session waits for the synchronization process to complete on the log file sync event.

 

Once a process enters Log File sync and waits, there are two possibilities.

One possibility is that lgwr submits the foreground process when the log synchronization is complete.

In another case, when waiting for timeout (usually within 1 second), the foreground process checks the current log SCN (System Change number, system change number ), determine whether the submission has been passed to the disk. If yes, the process continues to process it; otherwise, the process enters the waiting state again.

 

 

 

There are three main reasons for high log file sync waiting for events.
①. High submission frequency

The solution is to simply remove unnecessary commits, and transactions are work units. The Unit should be all successful or all failed.
②. Slow I/O subsystem
High Io throughput improves the average wait time for log file sync and Log File parallel write events. Frequent submission will disrupt the database layout and IO subsystem. The solution is to put the log file on a bare device or bind it to RAID 0 or RAID 0 + 1 instead of RAID 5.
③. Too large log Buffer
A large log buffer may also extend the log file sync wait. Large log buffers reduce the number of logs written in the background, allow lgwr to become lazy, and cause more redo entries to pile up in the log buffer. You can also adjust the parameter _ log_io_size. The default value is 1/3 or 1 MB of log_buffer, and a smaller value is used. In other words, you can have a large log buffer, but a smaller _ log_io_size will increase the number of background writes, thus reducing the log file sync wait time.

 

 

Note:
You must never set the parameter _ wait_for_sync to false. Even in a development database or test database, the committed transactions cannot be recovered when the instance fails. People use this feature to avoid benchmarking.
Generally, log file sync waits frequently. It is very short, and end users generally do not pay attention to it.
To It. However, many such events may generate a long response time, and in V $ system_event and V $ session_wait
View. Use the following query to find the current session. These sessions have spent a lot of processing time waiting on the log file sync event since login.

Log File parallel write

 

Log File parallel write event is an exclusive wait event of the lgwr process. When lgwr writes the redo log information in log_buffer to the member file of the online redo log file group, lgwr waits for the completion of the write process on this event.

The wait time for this event is too long, indicating that the disk where the log file is located is slow or there is a contention.

Solve the problem from two aspects:

(1) Place the log file group on the high-speed I/O disk.

(2) Minimize the number of retries:
-Use the nologging option whenever possible, including the create table... as select... Operation
-Hot Backup may create a large amount of redo information. Therefore, hot backup should run during off-peak hours and try to exclude the tablespace from the hot backup mode.