latch free、buffer latch！

latch free

SQL> select event#,name,parameter1,parameter2,parameter3 from v$event_name where name = 'latch free';

    EVENT# NAME                           PARAMETER1      PARAMETER2 PARAMETER3
---------- ------------------------------ --------------- ---------- ----------
       293 latch free                     address         number     tries

latch free等待事件在10g中并不常见，而是以具体的Latch等待事件出现的。这个等待事件包含三个参数。

address：会话等待的Latch地址。

number：Latch号，通过这个号，可以从v$latchname视图中找到这个latch的相关信息。

SQL> select * from v$latchname where latch# = 'number';

tries：会话尝试获取latch的次数。

在10g中，一些常用的latch事件已经被独立出来：

SQL> select event#,name,parameter1,parameter2,parameter3 from v$event_name where name like '%latch%';

    EVENT# NAME                                               PARAMETER1      PARAMETER2 PARAMETER3
---------- -------------------------------------------------- --------------- ---------- ----------
        59 latch: cache buffers chains                        address         number     tries
       107 latch: redo writing                                address         number     tries
       108 latch: redo copy                                   address         number     tries
       194 latch: Undo Hint Latch                             address         number     tries
       196 latch: In memory undo latch                        address         number     tries
       197 latch: MQL Tracking Latch                          address         number     tries
       207 latch: row cache objects                           address         number     tries
       213 latch: shared pool                                 address         number     tries
       214 latch: library cache                               address         number     tries
       215 latch: library cache lock                          address         number     tries
       216 latch: library cache pin                           address         number     tries

    EVENT# NAME                                               PARAMETER1      PARAMETER2 PARAMETER3
---------- -------------------------------------------------- --------------- ---------- ----------
       291 latch activity                                     address         number     process#
       292 wait list latch activity                           address         number     process#
       293 latch free                                         address         number     tries
       294 wait list latch free                               address         number     tries
       302 latch: session allocation                          address         number     tries
       317 latch: messages                                    address         number     tries
       320 latch: enqueue hash chains                         address         number     tries
       348 latch: ges resource hash list                      address         number     tries
       359 ges2 proc latch in rm latch get 1
       360 ges2 proc latch in rm latch get 2
       367 gcs remastering wait for write latch

    EVENT# NAME                                               PARAMETER1      PARAMETER2 PARAMETER3
---------- -------------------------------------------------- --------------- ---------- ----------
       368 gcs remastering wait for read latch
       401 latch: gcs resource hash                           address         number     tries
       432 latch: cache buffers lru chain                     address         number     tries
       434 latch: checkpoint queue latch                      address         number     tries
       435 latch: cache buffer handles                        address         number     tries
       438 buffer latch                                       latch addr      chain#
       445 latch: object queue header operation               address         number     tries
       446 latch: object queue header heap                    address         number     tries
       490 latch: redo allocation                             address         number     tries
       501 latch: KCL gc element parent latch                 address         number     tries
       571 latch: undo global data                            address         number     tries

    EVENT# NAME                                               PARAMETER1      PARAMETER2 PARAMETER3
---------- -------------------------------------------------- --------------- ---------- ----------
       595 latch: Change Notification Hash table latch        address         number     tries
       640 waiting to get CAS latch
       641 waiting to get RM CAS latch
       644 latch: virtual circuit queues                      address         number     tries
       660 PX qref latch                                      function        sleeptime  qref
       673 latch: parallel query alloc buffer                 address         number     tries

已选择39行。

buffer latch

SQL> select event#,name,parameter1,parameter2,parameter3 from v$event_name where name = 'buffer latch';

    EVENT# NAME                           PARAMETER1      PARAMETER2      PARAMETER3
---------- ------------------------------ --------------- --------------- ---------------
       438 buffer latch                   latch addr      chain#

内存中数据块的存放位置是记录在一个Hash列表（cache buffer chains）当中的。当一个会话需要访问某个数据块时，它首先要搜索这个Hash列表，从列表中获得数据块的地址，然后通过这个地址去访问需要的数据块，这个Hash在Oracle中使用Latch来保护它的完整性。当一个会话需要访问这个列表时，需要先获取一个Latch，只有这样，才能保证这个列表在这个会话的浏览中不会发生改变。

产生buffer latch的等待事件主要原因是：

（1）buffer chains太长，导致会话搜索这个列表话费的时间太长，使其他会话处于等待状态。

（2）同样的数据块被频繁的访问，就是我们通常说的热块问题。

如果buffer chains太长，我们可以使用多个buffer pool的方式来创建更多的buffer chains，或者使用参数DB_BLOCK_LRU_LATCHES来增加Latch的数量，以便于更多的会话可以获得Latch，这两种办法也可以同时使用。

这个等待事件包含两个参数：

latch addr：会话申请的Latch在SGA中的虚拟地址，通过以下的sql语句可以根据这个地址找到它对应的Latch名称：

SQL> select * from v$latch a,v$latchname b where a.addr = 'latch addr' and a.latch# = b.latch#;

chain#：buffer chains hash列表中的索引值，当这个参数的值等于s 0xfffffff时，说明当前的会话正在等待一个LRU Latch。