SQL> select event#,name,parameter1,parameter2,parameter3 from v$event_name where name like '%latch: redo%';
EVENT# NAME PARAMETER1 PARAMETER2 PARAMETER3
---------- ------------------------------ --------------- --------------- ---------------
132 latch: redo writing address number tries
133 latch: redo copy address number tries
631 latch: redo allocation address number tries
oracle为了保护将重做记录复制到重做缓冲区的一连串过程,使用以下三个锁存器。
(1)redo writing锁存器:为占用重做缓冲区内的空间,向LGWR请求写入工作的进程需要获得redo writing锁存器。因为LGWR的写入工作不能同时执行,所以自然在整个实例上只有一个。redo writing锁存器因为是独立锁存器,所以可以通过v$latch_parent视图观察活动性。
SQL> select name,gets,MISSES,IMMEDIATE_GETS,IMMEDIATE_MISSES,WAIT_TIME from v$latch_parent where name = 'redo writing';
NAME GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES WAIT_TIME
------------------------------ ---------- ---------- -------------- ---------------- ----------
redo writing 2072 0 0 0 0
在获取redo writing锁存器的过程中,如果发生争用,就会等待latch: redo writing事件。
(2)redo copy锁存器:想要将PGA内的Change Vector复制到重做缓冲区的进程,全程都要拥有redo copy锁存器。通过v$latch_children视图可以了解redo copy锁存器的活动性。
SQL> select name,gets,MISSES,IMMEDIATE_GETS,IMMEDIATE_MISSES,WAIT_TIME from v$latch_children where name = 'redo copy';
NAME GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES WAIT_TIME
------------------------------ ---------- ---------- -------------- ---------------- ----------
redo copy 5 0 0 0 0
redo copy 5 0 0 0 0
redo copy 5 0 0 0 0
redo copy 5 0 2486 6 0
如果进程在获取redo copy锁存器失败,将为了获取redo copy锁存器连续尝试,在最后一次获得redo copy锁存器过程中将使用Willing-to-wait模式。若获得redo copy锁存器的过程中发生争用,则等待latch: redo copy事件。
(3)redo allocation锁存器:为了将Change Vector复制到重做缓冲区,在获取重做缓冲区空间过程中需要拥有redo allocation锁存器。通过v$latch_children视图可以了解redo allocation锁存器的活动性。
SQL> select name,gets,MISSES,IMMEDIATE_GETS,IMMEDIATE_MISSES,WAIT_TIME from v$latch_children where name = 'redo allocation';
NAME GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES WAIT_TIME
------------------------------ ---------- ---------- -------------- ---------------- ----------
redo allocation 2 0 0 0 0
redo allocation 2 0 0 0 0
redo allocation 2 0 0 0 0
redo allocation 2 0 0 0 0
redo allocation 2 0 0 0 0
redo allocation 2 0 0 0 0
redo allocation 2 0 0 0 0
redo allocation 2 0 0 0 0
redo allocation 2 0 0 0 0
redo allocation 2 0 0 0 0
redo allocation 2 0 0 0 0
NAME GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES WAIT_TIME
------------------------------ ---------- ---------- -------------- ---------------- ----------
redo allocation 2 0 0 0 0
redo allocation 2 0 0 0 0
redo allocation 2 0 0 0 0
redo allocation 2 0 0 0 0
redo allocation 2 0 0 0 0
redo allocation 2 0 0 0 0
redo allocation 2 0 0 0 0
redo allocation 2 0 0 0 0
redo allocation 2 0 0 0 0
redo allocation 2 0 0 0 0
redo allocation 4 0 0 0 0
NAME GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES WAIT_TIME
------------------------------ ---------- ---------- -------------- ---------------- ----------
redo allocation 8 0 0 0 0
redo allocation 360 0 0 0 0
redo allocation 4 0 0 0 0
redo allocation 14 0 0 0 0
redo allocation 52 0 0 0 0
redo allocation 426 0 0 0 0
redo allocation 576 3 2510 0 194
已选择29行。
若获取redo allocation锁存器的过程中发生争用,则等待latch: redo allocation事件。
一般情况下不常发生相关的锁存器争用。重做缓冲区的大小可能成为引起重做锁存器争用的原因。如果整个系统上创建了许多不必要的重做数据,就通过应用nologging功能减少重做数据,这也可以成为减少锁存器争用的方法。但nologging工作基本上不能恢复,请记住这一点。