Oracle Tutorial: four cases of Redo write triggering

1. When LGWR is idle, it will check whether any data is written from redo buffer to redelog every 3 seconds. If yes, a background process will automatically write the data into it.

2. When a process needs to allocate space from the redo buffer, the occupied space in the redo buffer is calculated first. If the space is greater than the value of _ log_io_size, at this time, the LGWR is idle, and the execution of background write is activated. The default value of _ log_io_size is 1/3 of the redo buffer size. Starting from 8.0, the upper value of _ log_io_size is 1 M, which is the same as that of other hidden parameters, the value found in the X $ KSPPSV view is 0.

3. When a transaction commit, a commit mark will be generated in the redo stream. Before the log including the commit mark in the redo buffer is written to the hard disk, this transaction cannot be recovered, therefore, before returning a message similar to 'commit complete' to the user, you must wait for LGWR to write the redo buffer to the hard disk, after commit, LGWR is activated and sleeps on a log file sync wait with a timeout of 1 second. To avoid waiting

You can set the _ wait_for_sync parameter to false, but the transaction instance that has been committed cannot be restored when it fails.

_ Log_io_size and _ wait_for_sync both hide parameters. You can view the values in the following SQL statement.

1. Select I. ksppinm name, I. ksppdesc description, cv. ksppstvl value,
2. Cv. ksppstdf isdefault, decode (bitand (cv. ksppstvf,7),1,'Modified',4,'System _ mod','False') Ismodified,
3. Decode (bitand (cv. ksppstvf,2),2,'True','False') Isadjusted
4. From sys. x $ ksppi I,
5. Sys. x $ ksppcv cv
6. Where I. inst_id = userenv ('Instance') And
7. Cv. inst_id = userenv ('Instance') And
8. I. indx = cv. indx and I. ksppinm like'/_ %'Escape'/'Order by replace (I. ksppinm,'_','');

Note that the commit in the callback statement does not wait for LWGR to write data. For example, there may be n commit operations in the stored procedure, however, only the LWGR write will wait until the result is returned to the user, that is, only the last commit operation will be synchronized.

An SGA variable (kcrfshsb, according to bug 182447) is used to determine the largest log block number to be synchronized. When LGWR is in the active state and is being written like a disk, the kcrfshsb variable records the highest block number to be synchronized. These commit will all be written to disk in a redo write, called group commit.

4. When DBWn needs to write one or more high RBA blocks and the high rba exceeds the on-disk RBA, from Oracle 8i, DWBn puts these blocks into the delay queue and post LGWR to synchronize the highest RBA, but does not wait. DBWn will continue to execute other writes that do not need delay, before 8i, DBWn will sleep a log file sync wait.

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12