关于latch: cache buffers chains等待事件 oracle 官方的解释：

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).
cache buffers chains
闩锁是用于保护在buffer cache中的 buffer列表的，当从buffer cach中添加或移除一个 buffere时这些闩锁用于搜索buffer(其实只要访问闩锁管理的buffer就会用相关闩锁),这个闩锁上的竞争通常意味着一个块的的及大竞争，称为热点块。

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 viewV$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.
如何识别buffer chain的严重访问来引的块的竞争，可以查看V$LATCH_CHILDREN视图的cache buffers chains 闩锁的统信息，如果有特殊的一个cache buffers chains 子闩锁有更多的 GETS, MISSES, 和SLEEPS与它子闩锁相比，这个是竞争的子闩锁

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:
闩锁有很多多地址，用addr字段标识，用addr字段中的值连接X$BH表中被此闩锁保护的标识块，如例，获得严重竞争的地址（V$LATCH_CHILDREN.ADDR），这个查询文件和块号：

SELECT OBJ data_object_id, FILE#, DBABLK,CLASS, STATE, TCH
  FROM X$BH
 WHERE HLADDR = 'address of latch'
  ORDER BY TCH;

latch: cache buffers chains 等待事件的3 个参数
p1 代表的是内存中latch锁的地址
p2 代表闩锁号
p3 没意义

下面模拟场景
打开三个session

u1 用户 session1 (我已经有一张emp 表了）执行如下语句

点击(此处)折叠或打开

declare
name1 VARCHAR2(10);
begin
for i in 1..1000000 loop
dbms_lock.sleep(0.001);
select ENAME into name1 from emp where id=843;
end loop;
end;
/

u2 用户session2 执行如下语句

点击(此处)折叠或打开

declare
name2 VARCHAR2(10);
begin
for radius in 1..1000000 loop
dbms_lock.sleep(0.001);
select ENAME into name2 from emp where id=889;
end loop;
end;
/

sys 用户session中查看等待事件

点击(此处)折叠或打开

QL> select sid,event,p1,p2,p3,p1raw,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where wait_class# <> 6;
SID EVENT P1 P2 P3 P1RAW WAIT_TIME SECONDS_IN_WAIT
---------- ---------------------------------------------------------------- ---------- ---------- ---------- -------- ---------- ---------------
143 latch: cache buffers chains 806370812 122 0 30103DFC -1 0
146 latch: cache buffers chains 806737148 122 0 3015D4FC -1 0
159 SQL*Net message to client 1650815232 1 0 62657100 -1 0
SQL> /
SID EVENT P1 P2 P3 P1RAW WAIT_TIME SECONDS_IN_WAIT
---------- ---------------------------------------------------------------- ---------- ---------- ---------- -------- ---------- ---------------
143 latch: cache buffers chains 806737148 122 0 3015D4FC -1 0
146 latch: cache buffers chains 806737148 122 0 3015D4FC -1 0
159 SQL*Net message to client 1650815232 1 0 62657100 -1 0
SQL> /
SID EVENT P1 P2 P3 P1RAW WAIT_TIME SECONDS_IN_WAIT
---------- ---------------------------------------------------------------- ---------- ---------- ---------- -------- ---------- ---------------
143 latch: cache buffers chains 806370812 122 0 30103DFC -1 3
146 latch: cache buffers chains 806737148 122 0 3015D4FC -1 3
159 SQL*Net message to client 1650815232 1 0 62657100 -1 0
SQL> /
SID EVENT P1 P2 P3 P1RAW WAIT_TIME SECONDS_IN_WAIT
---------- ---------------------------------------------------------------- ---------- ---------- ---------- -------- ---------- ---------------
143 latch: cache buffers chains 806737148 122 0 3015D4FC 0 0
146 latch: cache buffers chains 806737148 122 0 3015D4FC 0 0
159 SQL*Net message to client 1650815232 1 0 62657100 -1 0
SQL> /
SID EVENT P1 P2 P3 P1RAW WAIT_TIME SECONDS_IN_WAIT
---------- ---------------------------------------------------------------- ---------- ---------- ---------- -------- ---------- ---------------
143 latch: cache buffers chains 806737148 122 0 3015D4FC -1 3
146 latch: cache buffers chains 806370812 122 0 30103DFC -1 3
159 SQL*Net message to client 1650815232 1 0 62657100 -1 0
SQL> /
SID EVENT P1 P2 P3 P1RAW WAIT_TIME SECONDS_IN_WAIT
---------- ---------------------------------------------------------------- ---------- ---------- ---------- -------- ---------- ---------------
143 latch: cache buffers chains 806737148 122 0 3015D4FC -1 0
146 latch: cache buffers chains 806737148 122 1 3015D4FC -1 0
159 SQL*Net message to client 1650815232 1 0 62657100 -1 0
SQL> /
SID EVENT P1 P2 P3 P1RAW WAIT_TIME SECONDS_IN_WAIT
---------- ---------------------------------------------------------------- ---------- ---------- ---------- -------- ---------- ---------------
143 latch: cache buffers chains 806737148 122 0 3015D4FC -1 0
146 latch: cache buffers chains 806737148 122 0 3015D4FC 10 0
159 SQL*Net message to client 1650815232 1 0 62657100 -1 0
SQL> /
SID EVENT P1 P2 P3 P1RAW WAIT_TIME SECONDS_IN_WAIT
---------- ---------------------------------------------------------------- ---------- ---------- ---------- -------- ---------- ---------------
143 latch: cache buffers chains 806737148 122 0 3015D4FC 3 0
146 latch: cache buffers chains 806737148 122 0 3015D4FC -1 3
159 SQL*Net message to client 1650815232 1 0 62657100 -1 0
SQL> /
SID EVENT P1 P2 P3 P1RAW WAIT_TIME SECONDS_IN_WAIT
---------- ---------------------------------------------------------------- ---------- ---------- ---------- -------- ---------- ---------------
143 latch: cache buffers chains 806370812 122 0 30103DFC -1 0
146 latch: cache buffers chains 806737148 122 1 3015D4FC 10 0
159 SQL*Net message to client 1650815232 1 0 62657100 -1 0
SQL> /
SID EVENT P1 P2 P3 P1RAW WAIT_TIME SECONDS_IN_WAIT
---------- ---------------------------------------------------------------- ---------- ---------- ---------- -------- ---------- ---------------
143 latch: cache buffers chains 806737148 122 0 3015D4FC 10 0
146 latch: cache buffers chains 806737148 122 0 3015D4FC -1 0
159 SQL*Net message to client 1650815232 1 0 62657100 -1 0
SQL> /
SID EVENT P1 P2 P3 P1RAW WAIT_TIME SECONDS_IN_WAIT
---------- ---------------------------------------------------------------- ---------- ---------- ---------- -------- ---------- ---------------
143 latch: cache buffers chains 806737148 122 0 3015D4FC -1 0
146 latch: cache buffers chains 806737148 122 0 3015D4FC -1 0
159 SQL*Net message to client 1650815232 1 0 62657100 -1 0
SQL> /
SID EVENT P1 P2 P3 P1RAW WAIT_TIME SECONDS_IN_WAIT
---------- ---------------------------------------------------------------- ---------- ---------- ---------- -------- ---------- ---------------
143 latch: cache buffers chains 806737148 122 0 3015D4FC -1 0
146 latch: cache buffers chains 806737148 122 0 3015D4FC -1 0
159 SQL*Net message to client 1650815232 1 0 62657100 -1 0
SQL> /
SID EVENT P1 P2 P3 P1RAW WAIT_TIME SECONDS_IN_WAIT
---------- ---------------------------------------------------------------- ---------- ---------- ---------- -------- ---------- ---------------
143 latch: cache buffers chains 806737148 122 0 3015D4FC -1 0
146 latch: cache buffers chains 806737148 122 0 3015D4FC -1 0
159 SQL*Net message to client 1650815232 1 0 62657100 -1 0
SQL> /
SID EVENT P1 P2 P3 P1RAW WAIT_TIME SECONDS_IN_WAIT
---------- ---------------------------------------------------------------- ---------- ---------- ---------- -------- ---------- ---------------
143 latch: cache buffers chains 806737148 122 0 3015D4FC -1 0
146 latch: cache buffers chains 806737148 122 0 3015D4FC -1 0
159 SQL*Net message to client 1650815232 1 0 62657100 -1 0
SQL> /
SID EVENT P1 P2 P3 P1RAW WAIT_TIME SECONDS_IN_WAIT
---------- ---------------------------------------------------------------- ---------- ---------- ---------- -------- ---------- ---------------
159 SQL*Net message to client 1650815232 1 0 62657100 -1 0

通过p1raw的值（闩锁地址的16进制）查询热点块（tch高的）

点击(此处)折叠或打开

SQL> SELECT OBJ data_object_id, FILE#, DBABLK,CLASS, STATE, TCH from x$bh where HLADDR=\'30103DFC\';
DATA_OBJECT_ID FILE# DBABLK CLASS STATE TCH
-------------- ---------- ---------- ---------- ---------- ----------
335 1 2664 1 1 1
3688 3 800 1 1 1
73 1 11806 1 1 1
29 1 7468 1 1 2
49871 3 22956 1 1 3
51703 4 101 1 1 27
6 rows selected.
SQL> SELECT OBJ data_object_id, FILE#, DBABLK,CLASS, STATE, TCH from x$bh where HLADDR=\'3015D4FC\';
DATA_OBJECT_ID FILE# DBABLK CLASS STATE TCH
-------------- ---------- ---------- ---------- ---------- ----------
18 1 7477 1 1 4
49873 3 22965 14 1 0
51704 4 110 1 1 27

通过OBJ的值与DBA_OBJECTS的DATA_OBJECT_ID值连接查询出对像

点击(此处)折叠或打开

SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,DATA_OBJECT_ID from DBA_OBJECTS WHERE DATA_OBJECT_ID = 51789;
OWNER OBJECT_NAM OBJECT_TYPE DATA_OBJECT_ID
------------------------------ ---------- ------------------- --------------
U1 EMP TABLE 51703
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,DATA_OBJECT_ID from DBA_OBJECTS WHERE DATA_OBJECT_ID = 51704;
OWNER OBJECT_NAM OBJECT_TYPE DATA_OBJECT_ID
------------------------------ ---------- ------------------- --------------
U1 EMP_DI INDEX 51704

通过上面的查询知道了热点块发生在 emp_di 索引的110块和emp表的101块，对于索引我们可以使用反向索引，对表我们呆以将101块的数据查询出来，然后插入到多个块中

在u1 用户 session1

创建反向索引

将表emp表101块的数据查询出来，并抛入到多个块中

点击(此处)折叠或打开

SQL> insert into empbk select * from emp where dbms_rowid.rowid_block_number(rowid)=101;
65 rows created.
SQL> delete emp where dbms_rowid.rowid_block_number(rowid)=101;
65 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from emp;
COUNT(*)
----------
831
SQL> insert /*+ append */ into emp select * from empbk where rownum <12;
11 rows created.
SQL> delete empbk where rownum <12;
11 rows deleted.
SQL> commit;
Commit complete.
SQL> insert /*+ append */ into emp select * from empbk where rownum <12;
11 rows created.
SQL> delete empbk where rownum <12;
11 rows deleted.
SQL> commit;
Commit complete.
SQL> insert /*+ append */ into emp select * from empbk where rownum <12;
11 rows created.
SQL> delete empbk where rownum <12;
11 rows deleted.
SQL> commit;
Commit complete.
SQL> SQL> insert /*+ append */ into emp select * from empbk where rownum <12;
11 rows created.
SQL> delete empbk where rownum <12;
11 rows deleted.
SQL> commit;
Commit complete.
SQL> insert /*+ append */ into emp select * from empbk where rownum <12;
11 rows created.
SQL> delete empbk where rownum <12;
11 rows deleted.
SQL> commit;
Commit complete.
SQL> insert /*+ append */ into emp select * from empbk where rownum <12;
10 rows created.
SQL> delete empbk where rownum <12;
10 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from empbk;
COUNT(*)
----------
0
SQL> select count(*) from emp;
COUNT(*)
----------
896

然后在u1的 session1 和 session2中执行的刚才的pl/sql语句

在sys 用户session中查看等待事件

点击(此处)折叠或打开

SQL> select sid,event,p1,p2,p3,p1raw,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where wait_class# <> 6;
SQL> /
SID EVENT P1 P2 P3 P1RAW WAIT_TIME SECONDS_IN_WAIT
---------- ---------------------------------------------------------------- ---------- ---------- ---------- -------- ---------- ---------------
159 SQL*Net message to client 1650815232 1 0 62657100 -1 0
SQL> /
SID EVENT P1 P2 P3 P1RAW WAIT_TIME SECONDS_IN_WAIT
---------- ---------------------------------------------------------------- ---------- ---------- ---------- -------- ---------- ---------------
159 SQL*Net message to client 1650815232 1 0 62657100 -1 0
SQL> /
SID EVENT P1 P2 P3 P1RAW WAIT_TIME SECONDS_IN_WAIT
---------- ---------------------------------------------------------------- ---------- ---------- ---------- -------- ---------- ---------------
159 SQL*Net message to client 1650815232 1 0 62657100 -1 0
SQL> /
SID EVENT P1 P2 P3 P1RAW WAIT_TIME SECONDS_IN_WAIT
---------- ---------------------------------------------------------------- ---------- ---------- ---------- -------- ---------- ---------------
159 SQL*Net message to client 1650815232 1 0 62657100 -1 0
SQL> /
SID EVENT P1 P2 P3 P1RAW WAIT_TIME SECONDS_IN_WAIT
---------- ---------------------------------------------------------------- ---------- ---------- ---------- -------- ---------- ---------------
159 SQL*Net message to client 1650815232 1 0 62657100 -1 0
SQL> /
SID EVENT P1 P2 P3 P1RAW WAIT_TIME SECONDS_IN_WAIT
---------- ---------------------------------------------------------------- ---------- ---------- ---------- -------- ---------- ---------------
159 SQL*Net message to client 1650815232 1 0 62657100 -1 0
SQL> /
SID EVENT P1 P2 P3 P1RAW WAIT_TIME SECONDS_IN_WAIT
---------- ---------------------------------------------------------------- ---------- ---------- ---------- -------- ---------- ---------------
159 SQL*Net message to client 1650815232 1 0 62657100 -1 0

注：1、此方法查到的不一这是准确的热点块，因为一个latch是保护多个块的，且块上的接触计数（tch）是一段时间内累计计数，tch算法和lru算法结合使用，当块回到lru链的热端时 tch会重新设置（有人说是清零，有人说是除2的值），所以一个latch保护的多个块上的tch计数不同时间段的累计值。所以最好多次查看tch 对比一个时间段内的增长量，来确定热点块。
2、为什么查询要需要latch锁？，也许大家会有这样的疑问。这个根 oracle的算法相关，为了在内存中尽快找到所需要的块， oracle 采用一种hash算法，根据块的的属性（绝对文件号、块号等）利用hash函数生成一个数字，并使用一链将这些块连接起来，再将这个链分成段，这段叫做桶，多个桶一个 latch锁保护，这个latch锁就是cache buffers chains，这样为了找到块，必须搜索段来查看段内的块，要搜索段就必须获得 latch锁，这就产生了竞争（这里进的不是很明白，准备以后专门写篇博文来说明）。

ITPub博客

等待事件（latch: cache buffers chains）之热点块的定位

最新文章