log file parallel write wait

log file parallel write wait

log file parallel write

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 thewrites 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. 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.

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');