latch:cachebufferschains等待事件导致的latch争用的原理原因与检查

latch:cache buffers chains 原理

当一个数据块读入到sga中时，该块的块头(buffer header)会放置在一个hash bucket的链表(hash chain)中。该内存结构由一系列cache buffers chains子latch保护（又名hash latch或者cbc latch）。对Buffer cache中的块，要select或者update、insert,delete等，都得先获得cache buffers chains子latch，以保证对chain的排他访问。若在过程中发生争用，就会等待latch:cache buffers chains事件。

产生原因： 1. 低效率的SQL语句（主要体现在逻辑读过高） 在某些环境中，应用程序打开执行相同的低效率SQL语句的多个并发会话，这些SQL语句都设法得到相同的数据集，每次执行都带有高 BUFFER_GETS(逻辑读取)的SQL语句是主要的原因。相反，较小的逻辑读意味着较少的latch get操作，从而减少锁存器争用并改善性能。注意v$sql中BUFFER_GETS/EXECUTIONS大的语句。 2.Hot block 当多个会话重复访问一个或多个由同一个子cache buffers chains锁存器保护的块时，热块就会产生。当多个会话争用cache buffers chains子锁存器时，就会出现这个等待事件。有时就算调优了SQL，但多个会话同时执行此SQL，那怕只是扫描特定少数块，也是也会出现HOT BLOCK的。

检查看下当前active的会话中产生的比较高buffer get的SQL： select * from (select sql_text,hash_value,buffer_gets/executions from v$sql where executions<>0 and hash_value in (select sql_hash_value from gv$session where statu
s='ACTIVE' )order by buffer_gets/executions desc ) where rownum<16;

通过v$latch查看自实例启动以来cache buffers chains锁存器争用是否厉害： select round((misses/gets)*100)||'%',round(100*(immediate_misses/(immediate_gets+immediate_misses)))||' %' from v$latch where name='cache buffers chains'; 对于willing-to-wait,比较重要的是misses/gets，假如大于1%就应该发生争用了，大于10%，就有争用严重的情况了。对于no-wait模式，immediate_misses/(immediate_gets+immediate_misses)也一样。

通过查询子锁存器视图，看看是否有Hot Block，并且获取有Hot Block的子锁存器addr select * from (select addr,child#,gets,misses,sleeps from v$latch_children where name='cache buffers chains' order by sleeps desc ) where rownum<=20; 如果存在倾斜明显，即某些子锁存器的GETS与SLEEPS比其他子锁存器高很多，则可以推测相应锁存器管辖的chain上有Hot Block。

另外一种判断Hot block方法，是从当前等待latch:cache buffers chains事件的会话出发。通过v$session_wait视图，获得P1RAW即子锁存器的地址。通过重复观察v$session_wait视图，发现某个子锁存器地址较多地出现，那么该子锁存器管辖的chain可能有热块。 select p1,p1raw from v$session_wait where event='latch: cache buffers chains';

所以v$session的p1raw与x$bh的laddr，以及v$latch_children的addr是同样的东西，都是子锁存器的地址。大概思路是，通过子锁存器的热度来找到所管辖的对象，以及对象的热度。

通过子锁存器地址，即v$latch_children的addr字段，来获取这些子锁存器所管理的对象的文件号块号与热度。 注意到x$bh字典表中的tch字段表示的就是block的touch count,一般来说这个值越高那么这个块就越热，我们称这样的块就叫做热点块。 select hladdr,obj,(select object_name from dba_objects where (data_object_id is null and object_id=x.obj) or data_object_id=x.obj and rownum=1) as object_name,dbarfil,dbablk,tch from x$bh x where hladdr in ('00000000DA253C08','00000000DA380310') order by tch desc;
根据FILE#,dbablk来找出对应对象。 select * from dba_extents where file_id=10 and 36643122 between block_id and block_id + blocks - 1;

直接通过v$bh视图直接查找数据库热点块，从而找到热点的对象。 select * from (select hladdr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc) where rownum<16; 然后通过dba_extents来查找相应对象。 select * from dba_extents where file_id=10 and 36643122 between block_id and block_id + blocks - 1; 或者直接通过下面语句来查找数据库热点对象： SELECT * FROM (SELECT O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE, SUM(TCH) TOUCHTIME FROM X$BH B, DBA_OBJECTS O WHERE B.OBJ = O.DATA_OBJECT_ID AND B.TS# > 0 GROUP BY O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE ORDER BY SUM(TCH) DESC) WHERE ROWNUM <= 10;

查看引起latch: cache buffers chains的sql select * from (select count(*),sql_id,nvl(o.object_name,ash.current_obj#) objn,substr(o.object_type,0,10) otype, CURRENT_FILE# fn,CURRENT_BLOCK# blockn from v$active_session_history ash,all_objects o where event like 'latch: cache buffers chains' and o.object_id (+)= ash.CURRENT_OBJ# group by sql_id, current_obj#, current_file#, current_block#, o.object_name,o.object_type order by count(*) desc )where rownum <=10; 根据上面得到的sql_id信息查看sql全文 SQL>select sql_fulltext from v$sqlarea where sql_id='&sqlid';

解决方法 1.优化SQL，如优化nested loop join，如果有可能使用hash join代替nested loop join。 2.可以利用对热块索引进行hash分区，或者使用hash簇的方式减缓热块现象。 3.调整表的pctfree值，将数据尽可能的分布到多个块中，但相同的查询要扫更多块，有负面作用。 4.并行查询是直接读数据文件，不经过SGA，即direct path read,所以就不存在锁存器争用的情况了。但其一般是为了大量数据读取而使用的，不作为一般的解决方案。 5.等问题自己消失。有时当出现latch争用时，故障时刻确实没有较好的方式解决，找到病因才是关键。

附录：查看cache buffers chains有多少个子锁存器 Select count(*) from v$latch_children where name = 'cache buffers chains';
找出前10的热点块对象： select /*+rule*/ owner,object_name from dba_objects where data_object_id in (select obj from (select obj from x$bh order by tch desc) where rownum < 11) or object_id in (select obj from (select obj from x$bh order by tch desc) where rownum < 11)
Oracle11g联机文档中摘录： The cache buffers chains latches are used to protect a buffer list in the buffer cache. These latches are used when searching for, adding, or removing a buffer from the buffer cache. Contention on this latch usually means that there is a block that is greatly contended for (known as a hot block). To identify the heavily accessed buffer chain, and hence the contended for block, look at latch statistics for the cache buffers chains latches using the view V$LATCH_CHILDREN. If there is a specific cache buffers chains child latch that has many more GETS, MISSES, and SLEEPS when compared with the other child latches, then this is the contended for child latch. This latch has a memory address, identified by the ADDR column. Use the value in the ADDR column joined with the X$BH table to identify the blocks protected by this latch. For example, given the address (V$LATCH_CHILDREN.ADDR) of a heavily contended latch, this queries the file and block numbers: SELECT OBJ data_object_id, FILE#, DBABLK,CLASS, STATE, TCH FROM X$BH WHERE HLADDR = 'address of latch' ORDER BY TCH; X$BH.TCH is a touch count for the buffer. A high value for X$BH.TCH indicates a hot block. Many blocks are protected by each latch. One of these buffers will probably be the hot block. Any block with a high TCH value is a potential hot block. Perform this query several times, and identify the block that consistently appears in the output. After you have identified the hot block, query DBA_EXTENTS using the file number and block number, to identify the segment. After you have identified the hot block, you can identify the segment it belongs to with the following query: SELECT OBJECT_NAME, SUBOBJECT_NAME FROM DBA_OBJECTS WHERE DATA_OBJECT_ID = &obj; In the query, &obj is the value of the OBJ column in the previous query on X$BH.
Latch: cache buffers chains Description: Blocks in the buffer cache are placed on linked lists (cache buffer chains) which hang off a hash table. The hash chain that a block is placed on is based on the DBA and CLASS of the block. Each hash chain is protected by a single child latch. Processes need to get the relevant latch to allow them the scan a hash chain for a buffer so that the linked list does not change underneath them. Contention: Contention for these latches can be caused by: - Very long buffer chains. There is a known problem that can result in long buffer chains - - very very heavy access to a single block. This would require the application to be reviewed. - To identify the heavily accessed buffer chain look at the latch stats for this latch under <View:V$Latch_Children> and match this to <View:X$BH>.