Log file parallel write wait event tuning tips in Oracle

The ‘log file parallel write’ event occurs when the Log writer (LGWR) process writes the redo buffer to the online redo log files by making several write calls to the system IO.

The LGWR waits for write operations to finish on the log file. A slow LGWR can cause delays, making users wait during commit or rollback. Log file writes and sync waits are connected and should be addressed together.

If the average_wait time is high (above 10 milliseconds) it indicates that the system IO throughput is slow.

To improve the average_wait time follow the same techniques used in db file parallel write wait event.

Tuning options:

1. Avoid running hot backups during peak hours.
2. Check for high commit sessions and try to change the application logic to commit less frequently.

Use the following queries to find high commit sessions:

SQL> select sid, value from v$sesstat
where statistic# = select statistic# from v$statname where name = 'user commits') order by 2 desc;

A high redo wastage also indicates high frequency commits

SQL> select b.name, a.value, round(sysdate - c.startup_time) days_old
from v$sysstat a, v$statname b, v$instance c
where a.statistic# = b.statistic#
and b.name in ('redo wastage','redo size');

SQL> select sid, value from v$sesstat
where statistic# = select statistic# from v$statname where name = 'user commits') order by 2 desc;

A high redo wastage also indicates high frequency commits

SQL> select b.name, a.value, round(sysdate - c.startup_time) days_old
from v$sysstat a, v$statname b, v$instance c
where a.statistic# = b.statistic#
and b.name in ('redo wastage','redo size');