触发写Redo Log的条件

参见；http://www.ixora.com.au/notes/redo_write_triggers.htm

     http://www.eygle.com/archives/2005/02/redoaeoeio.html

 

There are four conditions that cause LGWR to perform a redowrite.

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

    

2. Whenever a process allocates space in the log buffer, thenumber of used log buffer blocks is calculated. If the number ofused blocks is greater than or equal to the number of blocksspecified by the _log_io_size parameter, and if LGWR is notalready active, then LGWR is posted to perform a background write.The default value for _log_io_size is ¹/3 of the log bufferexpressed in log blocks, with an upper bound equivalent to 1 Mbfrom release 8.0. As with many other hidden parameters, unless setexplicitly, the _log_io_size parameter reports as zero inX$KSPPSV.

    

3. When a transaction commits, it generates acommit marker in the redo stream. However, the transaction is notrecoverable until the log block containing that commit marker hasbeen flushed to disk. So, before the process finishing thetransaction can return a response to the user, it must wait forLGWR to flush that log block to disk. The process therefore postsLGWR and then sleeps on a log file sync wait with a timeoutof 1 second. For completeness, the _wait_for_sync parametercan be set to FALSE to avoid waiting for redo to be synced, butdoing so voids any guarantee of recoverability for committedtransactions upon instance failure.

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

   An SGA variable (kcrfshsb, according to bug 182447) isused to communicate the log block number up to which the redothread needs to be synced. If several commits occur in distincttransactions before LGWR wakes up, this variable records thehighest log block number that needs to be synced, and the commitmarkers are all flushed to disk in a single redo write. This issometimes called a group commit.

    

4. When DBWn needs to write one or more blocks for which thehigh RBA is beyond LGWR's on-disk RBA, from Oracle 8i, it placesthose blocks into its deferred write queue and posts LGWR to syncthe highest high RBA, but it does not wait. Instead DBWn continuesto process other writes that do not need to be deferred. Prior torelease 8i, DBWn used to sleep on a log file syncwait in this case.

为了保证用户可以快速提交，LGWR的写出必须非常活跃，我们非常熟悉的LGWR写触发条件就有4条。

 

1.每3秒超时（Timeout）
当LGWR处于空闲状态时，它依赖于rdbms ipcmessage等待，处于休眠状态，直到3秒超时时间到。如果LGWR发现有redo需要写出，那么LGWR将执行写出操作，logfile parallel write等待事件将会出现。

2. 阀值达到
我们在各种文档上经常会看到的2个触发日志写的条件是：

·Redo Log Buffer 1/3满；
·Redo Log Bufrer 具有1MB脏数据。

这两个都是限制条件，在触发时是协同生效的。我们知道，只要有进程（Process）在LogBuffer中分配和使用空间，已经使用的LogBuffer的数量将被计算。如果使用的块的数量大于或等于一个隐含参数_log_io_size的设置，那么将会触发LGWR写操作。

如果此时LGWR未处于活动状态，那么LGWR将被通知去执行后台写操作。

缺省的_log_io_size等于1/3 LogBuffer大小，上限值为1MB，此参数在X$KSPPSV（[K]ernel [S]ervice [P]arameter Component[S]ystem [V]alues）中显示的0值，意为缺省值。

也就是，LGWR将在Min(1M,1/3 log buffersize)时触发。注意此处的Log Buffer Size是以Log Block来衡量的。此值通常为512bytes

 

GetHidPar.sql

 

select
  x.ksppinm  name,
  y.ksppstvl  value,
  y.ksppstdf  isdefault,
 decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
 decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj
from
  sys.x$ksppi x,
  sys.x$ksppcv y
where
  x.inst_id = userenv('Instance') and
  y.inst_id = userenv('Instance') and
  x.indx = y.indx and
  x.ksppinm like '%_&par%'
order by
  translate(x.ksppinm, ' _', ' ')

sys@NEI> @GetHidPar
Enter value for par: log_io_size
old   4: AND x.ksppinm LIKE'%&par%'
new   4: AND x.ksppinm LIKE'%log_io_size%'
NAME                          VALUE               DESCRIB
------------------------------ --------------------------------------------------------------------------------
_log_io_size                  0                   automatically initiate log write if this many redo blocks in
                                                    buffer

一个常见的经验推荐是将Log Buffer设置为3MB大小，就是因为当Redo LogBuffer为3MB时，以上两个条件可能同时达到，从而可以在某种程度上避免LGWR的过度激活。

 

3. 用户提交
当一个事务提交时，在Redo Stream中将记录一个提交标志。
在这些Redo被写到磁盘上之前，这个事务是不可恢复的。所以，在事务返回成功标志给用户前，必须等待LGWR写完成。进程通知LGWR写，并且以LogFile Sync事件开始休眠，超时时间而1秒。

Oracle的隐含参数_wait_for_sync参数可以设置为False以避免Redo FileSync的等待，但是就将无法保证事务的恢复性。

sys@NEI> @GetHidPar
Enter value for par: wait_for_sync
old   4: AND x.ksppinm LIKE'%&par%'
new   4: AND x.ksppinm LIKE'%wait_for_sync%'
NAME                          VALUE               DESCRIB
------------------------------ --------------------------------------------------------------------------------
_wait_for_sync                TRUE                wait for sync on commit MUST BE ALWAYS TRUE

注意：
在递归调用（RecurSiveCalls）中的提交（比如过程中的提交）不需要同步Redo直到需要返回响应给用户。因此递归调用仅需要同步返回给用户调用之前的最后一次Commit操作的RBA。

存在一个SGA变量用以记录Redo线程需要同步的Log BlockNumber。如果多个提交在唤醒LGWR之前发生，此变量记录最高的Log BlockNumber，在此之前的所有Redo都将被写入磁盘。这有时候称为组提交（GroupCommit）。

4. 在DBWn写之前
如果DBWR将要写出的数据的High RBA超过LGWR的On-DiskRBA，DBWR将通知LGWR去执行写出（否则这部分数据在Recovery时将无法恢复）。在Oracle8i之前，此时DBWR将等待log file sync事件；从Oracle8i开始，DBWR把这些Block放入一个延迟队列，同时通知LGWR执行Redo写出，DBWR可以继续执行无需等待的数据写出。