log file parallel write wait event
The ‘log file parallel write’ event is caused by the Log writer (LGWR) process.
The LGWR writes the redo buffer to the online redo log files . It issues a series of write calls to the system IO.
The LGWR waits for the writes to complete on log file parallel write. A slow LGWR process can introduce log file sync waits
which makes the user to experience wait times during commit or rollback operation. The log file parallel write and log file sync
wait events are interrelated and must be dealt simultaneously.
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.
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');