_log_io_size
Parameter type | boolean | Default value | 0 |
Parameter class | static | Range of Values | unknown |
Syntax: | _log_io_size = integer |
Automatically initiate log write if this many redo blocks in are in the buffer. The default setting of 0 will automatically determine the proper threshold based the size of the log buffer. There are two classes of LGWR writes: background writes, and sync writes. The distinction is merely as to whether another process has to wait for the log write to complete.
A background write by LGWR can be triggered in either of two ways. Whenever a process allocates space in the log buffer, the number of used log buffer blocks is calculated. This calculation is based on the two SGA variables mentioned above, and is performed under the protection of the redo allocation latch. If the number of used blocks is greater than or equal to the number of blocks specified by _log_io_size, and if LGWR is not already active, then it is posted to perform a background write. The default value for _log_io_size is 1/3 of the log buffer expressed in log blocks (with an upper bound equivalent to 1M under Oracle8).
If more than this number of log buffer blocks are used, then the redo allocation latch is released and the redo writing latch is taken to check whether LGWR is already active.
When LGWR is waiting to be posted, it sleeps on an rdbms ipc message wait with a timeout of 3 seconds (as does DBWn). A background process write may also be performed if this sleep times out and LGWR finds that there is some redo available to write.
The most common cause of log sync writes is the end of a transaction. When a process finishes a transaction, normally with a commit, it generates a commit marker in the redo stream. However, the transaction is not recoverable until the log block containing that commit marker has been flushed to disk. Therefore, before the process finishing the transaction can continue, it must wait for LGWR to flush that log block to disk. That process therefore posts LGWR and then sleeps on a log file sync wait with a timeout of 1 second. If several commits occur in distinct transactions before LGWR wakes up, then the commit markers are all flushed to disk in a single sync write. This is sometimes called a group commit.