Save big on AI tools today!

Top 5 Waits In Last 1 Year – “Log File Parallel Write”

Database Low Performance

Angudi Tech

6/8/20262 min read

Among the primary waits, we observe that "Log File Parallel Write" accounts for a substantial portion when we examined the database for waiting events over the past year. This wait event arises when LGWR requires time to transfer redo buffer data to the physical redo logs.

The Oracle database implements a recovery mechanism by generating Redo for each data modification within the database. This Redo information is subsequently stored in redo logs. The physical redo logs on the storage devices are a vital component of any Oracle database, as they play an essential role in recovery scenarios; without these Redo logs, point-in-time recovery becomes impossible.

Redo log files contain redo records. A redo record, which is also referred to as a redo entry, consists of a collection of change vectors. Each change vector describes a modification made to a specific block within the database. For instance, when you alter a salary value in an employee table, a redo record is created that includes change vectors detailing the modifications to the data segment block of the table, the undo segment data block, and the transaction table associated with the undo segments. Redo entries document data that can be utilized to recreate all modifications made to the database, encompassing the undo segments. Consequently, the redo log also safeguards rollback data. When recovering the database with redo data, the database interprets the change vectors within the redo records and implements the changes to the appropriate blocks. Redo records are stored in a circular manner within the redo log buffer of the SGA and are subsequently written to one of the redo log files by the Log Writer (LGWR) database background process. Each time a transaction is committed, LGWR transfers the transaction redo records from the redo log buffer of the SGA to a redo log file, while also assigning a system change number (SCN) to uniquely identify the redo records corresponding to each committed transaction. The user process is only informed that the transaction has been committed once all redo records related to that specific transaction are securely written to disk in the online logs. Redo records can also be written to a redo log file before the corresponding transaction is committed. If the redo log buffer fills, or another transaction commits, LGWR flushes all of the redo log entries in the redo log buffer to a redo log file, even though some redo records may not be committed. If necessary, the database can roll back these changes.

What steps can we take to eliminate this error? Is merely offering faster storage the answer to this issue? From the perspective of application design, it is advisable to structure it in a manner that reduces the frequency of commits. This is important because with each commit, the database must guarantee that the data from the log buffer is recorded in the redo log. From the DBA's standpoint, consider increasing the log buffer size within the SGA, allowing it to hold more log data, thereby reducing the frequency at which LGWR needs to switch.

Thus, following the aforementioned corrections, will the "Log file parallel write" cease to exist? Perhaps not!!

Angudi Tech provides assistance in this regard by delivering tailored scripts that report the precise remedy for eliminating the "Log file parallel write" wait. Furthermore, it automates the process, ensuring that any future circumstances that may trigger the issue are identified and resolved with an compliant solution.