Redo Write Triggers

There are four conditions that cause LGWR to perform a redo write.

1. When LGWR is idle, it sleeps on an rdbms ipc message wait with a timeout of 3 seconds (as does DBWn). If this sleep times out and LGWR finds that there is some redo available to write, a background write is performed spontaneously.

2. Whenever a process allocates space in the log buffer, the number of used log buffer blocks is calculated. If the number of used blocks is greater than or equal to the number of blocks specified by the _log_io_size parameter, and if LGWR is not already active, then LGWR is posted to perform a background write. The default value for _log_io_size is ¹/3 of the log buffer expressed in log blocks, with an upper bound equivalent to 1 Mb from release 8.0. As with many other hidden parameters, unless set explicitly, the _log_io_size parameter reports as zero in X$KSPPSV.

3. When a transaction commits, 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. So, before the process finishing the transaction can return a response to the user, it must wait for LGWR to flush that log block to disk. The process therefore posts LGWR and then sleeps on a log file sync wait with a timeout of 1 second. For completeness, the _wait_for_sync parameter can be set to FALSE to avoid waiting for redo to be synced, but doing so voids any guarantee of recoverability for committed transactions upon instance failure.

   Note that commits within recursive calls (such as procedural code execute calls) do not need to sync their redo until a response is about to be returned to the user. Therefore recursive calls just sync the commit RBA of their most recent COMMIT upon return to the user call. They do not sync each commit.

   An SGA variable (kcrfshsb, according to bug 182447) is used to communicate the log block number up to which the redo thread needs to be synced. If several commits occur in distinct transactions before LGWR wakes up, this variable records the highest log block number that needs to be synced, and the commit markers are all flushed to disk in a single redo write. This is sometimes called a group commit.

4. When DBWn needs to write one or more blocks for which the is beyond LGWR's , from Oracle 8i, it places those blocks into its deferred write queue and posts LGWR to sync the highest high RBA, but it does not wait. Instead DBWn continues to process other writes that do not need to be deferred. Prior to release 8i, DBWn used to sleep on a log file sync wait in this case.

 

How to query this hidden initialization parameters in sql ?

SQL>

SELECT
a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value",a.KSPPDESC      
FROM
x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE
a.indx = b.indx
AND
a.indx = c.indx
AND
a.ksppinm LIKE '/_%' escape '/'