log file parallel write

log file parallel write

As the db file parallel write wait event belongs to the Oracle DBWR process, the log file parallel write wait event belongs to the Log Writer (LGWR) process. When it is time to write, the LGWR process writes the redo buffer to the online redo files in parallel and waits on the log file parallel write event until the last I/O is on disk.

V$SESSION_WAIT Parameters:

• P1=The number of online redo files Oracle is writing to. If there are two log members in the current group, then P1 shows 2.
   
• P2=The number of redo blocks to be written to each log member.
   
• P3=Total number of I/O requests needed to satisfy the write load (i.e. P2).

Common causes and actions

The log file parallel write latency is typically a symptom of poor placement of online redo log files, i.e. when files are placed on slow disks or LUNs bound in RAID 5. The AVERAGE_WAIT time of this event is a good indicator of the efficiency of the I/O device. Values less than 1 centisecond are desirable, although values up to 3 centiseconds are common and acceptable. If the average I/O wait time is an issue, the DBA needs to move the online redo log files to high speed disks, or disks with little or no I/O activities from external sources, or LUNs that are striped over many disks and not bound in RAID 5, or raw devices.

A high commit frequency produces an over active LGWR process, and in a database where the online redo files are poorly placed, this increases the log file parallel write waits significantly. The application may be processing a large set of data in a loop and committing each change, which causes the log buffer to be flushed too frequently. In this case, modify the application code to commit at a lower frequency. There could also be many short sessions that log in, perform a small DML operation, and quickly log out. In this case, the application design may need to be reviewed. Hot backup that goes off during the peak hours generates a large amount of redo entries and may increase the log file parallel write waits. The DBA should schedule hot backup in off-peak hours and take tablespaces off the hot backup mode as soon as the backup completes.

Reducing the amount of redo generation can minimize the log file parallel write waits, but the cure is proper placement of online redo files. One of the ways to reduce the amount of redo generation is to use the NOLOGGING option whenever possible. All indexes should be created or rebuilt with the NOLOGGING option. If application permits, CTAS operations should also use this option.

The _LOG_IO_SIZE parameter sets the threshold for the number of used log blocks in the Log Buffer. If the number of used log blocks is equal to or greater than the _LOG_IO_SIZE, the LGWR process will be posted to perform a background write if it is not already active. By default the _LOG_IO_SIZE is 1/3 of the LOG_BUFFER expressed in log blocks. Query the X$KCCLE.LEBSZ for the database's log block size. Typically it is 512 bytes. For example, if the LOG_BUFFER is 1,048,576 bytes (1MB), and the log block size is 512 bytes, then the default value for _LOG_IO_SIZE is 683 used log blocks. At this size, the LGWR process normally writes only on transaction terminations (sync writes) or when it wakes up from its 3-second timeouts. If the _LOG_IO_SIZE is much smaller (by means of a much smaller LOG_BUFFER or INIT.ORA setting), it can increase the number of background writes and subsequently increase the log file parallel write waits. While a larger _LOG_IO_SIZE reduces the number of background writes, it can slow down the commit response time because sync writes are longer. In other words, user processes wait longer on the log file sync event. Therefore the _LOG_IO_SIZE should not be too small that it creates a hyperactive LGWR or too large that it increases the log file sync waits. As a rule of thumb, the _LOG_IO_SIZE should be about the maximum physical I/O size (maxphys) of the platform.

For system-level diagnosis, query the V$SYSTEM_EVENT view to determine if the AVERAGE_WAIT is an issue. Also look at the log file sync event. Each time a user session performs a commit or rollback, the LGWR process writes the redo buffer to disks and waits on the log file parallel write event for the I/O to complete, while the user session waits on the log file sync event for the transaction to complete. Therefore, reducing the log file parallel write wait time will also reduce the user wait time on the log file sync event.

Check for high commit rate. As mentioned above, a high commit rate forces the LGWR process to be overactive. A database has a high commit rate when the average log I/O size is far smaller than _LOG_IO_SIZE, which defaults to 1/3 of the LOG_BUFFER size, expressed in log blocks. In other words, there are many small transactions and the LGWR process is forced to write before filling up to the _LOG_IO_SIZE threshold. The average log I/O size is obtained by dividing the statistics redo blocks written with redo writes.

If the commit rate is high, the LOG_BUFFER need not be large. A larger LOG_BUFFER and lower commit rate can help improve the performance of imports, conventional loader, and concurrent inserts, but the gain may be diminished by the log file parallel write waits that are caused by poor placement of redo files.

Writing redo records to the redo log files from the log buffer.

Wait Time:

Time it takes for the I/Os to complete. Even though redo records are written in parallel, the parallel write is not complete until the last I/O is on disk.

Parameter	Description
files	Number of files to be written
blocks	Number of blocks to be written
requests	Number of I/O requests