Cache Buffers Chain Latch的竞争

Oracle内核技术揭密_吕海波  学习笔记

Cache Buffers Chain(CBC) Latch出现竞争时，主要是如下两种情况：
1，多个进程不兼容的申请同一CBC Latch，访问此CBC Latch保护的不同链表的不同Buffer Header(BH)，这叫热链竞争。
2，多个进程不兼容的申请同一CBC Latch，访问此CBC Latch保护的同一链表的同一BH，这叫热块竞争。

隐藏参数_db_block_hash_buckets和_db_block_hash_latches分别控制HASH Bucket的数量和CBC Latch的数量。
当出现热链竞争时，可以修改隐藏参数，BH和HASH Bucket的对应关系就会被重新计算，原本在同一链表中的BH可能就不在同一链表中了。

1，查看表t71中id=1的那一行数据所在的数据块,再通过文件号、块号得到它在x$bh表的Latch位置；第一次查询如果没有latch值，是因为数据块没有catch到缓存中，select一下数据就可以了。

SQL> select dbms_rowid.rowid_relative_fno(rowid) file_id,dbms_rowid.rowid_block_number(rowid) block_id from t71 where id=1;

   FILE_ID   BLOCK_ID
---------- ----------
         4     327499


SQL> select HLADDR from x$bh where file#=4 and  dbablk=327499;

HLADDR
----------------
00000000D17C9B68

2，查询到此Latch共保护了10个Buffer，下面将以表SYS.WRI$_ADV_SQLT_PLANS为例模拟热链竞争。

SQL> select file#,dbablk,owner,object_name,object_type,object_id from x$bh a,dba_objects b where hladdr='00000000D17C9B68' and a.obj=b.data_object_id;

     FILE#     DBABLK OWNER        OBJECT_NAME          OBJECT_TYP  OBJECT_ID
---------- ---------- ------------ -------------------- ---------- ----------
         1      23644 SYS          IDL_UB2$             TABLE             303
         1      12698 SYS          C_TOID_VERSION#      CLUSTER           627
         1      12698 SYS          TYPE$                TABLE             629
         1      12698 SYS          COLLECTION$          TABLE             638
         1      12698 SYS          ATTRIBUTE$           TABLE             642
         1      12698 SYS          METHOD$              TABLE             645
         1      12698 SYS          PARAMETER$           TABLE             647
         1      12698 SYS          RESULT$              TABLE             651
         2     110484 SYS          WRI$_ADV_SQLT_PLANS  TABLE            7776
         4     327499 SYSTEM       T71                  TABLE           94361

3，查看表SYS.WRI$_ADV_SQLT_PLANS在块110484中第一行数据的rowid，并验证该ROWID对应数据块位置的正确性。ROWID_CREATE的使用方法

SQL> select dbms_rowid.rowid_create(1,7776,2,110484,1) from dual;

DBMS_ROWID.ROWID_C
------------------
AAAB5gAACAAAa+UAAB

SQL> select  count(*) from SYS.WRI$_ADV_SQLT_PLANS where rowid='AAAB5gAACAAAa+UAAB';

  COUNT(*)
----------
         1
		 
SQL> select dbms_rowid.rowid_relative_fno(rowid) file_id,dbms_rowid.rowid_block_number(rowid) block_id from SYS.WRI$_ADV_SQLT_PLANS where rowid='AAAB5gAACAAAa+UAAB';

   FILE_ID   BLOCK_ID
---------- ----------
         2     110484

4，分别在两个会话中执行程序，实现两个程序分别在各自的循环中频繁访问同一Latch保护的不同Buffer。

declare
r number;
begin
  for i in 1..10000000 loop
    select count(*) into r  from  SYS.WRI$_ADV_SQLT_PLANS where ROWID='AAAB5gAACAAAa+UAAB';
  end loop;
end;
/


declare
n varchar2(20);
begin
  for i in 1..10000000 loop
    select name into n  from  t71 where id=1;
  end loop;
end;
/

5，新开一个会话，查看上面37和41两个会话出现的等待事件，P1RAW是latch地址，P2RAW是latch编号(v$latch_child的latch#列)

SQL> select sid,event,p1raw,p2raw  from v$session where wait_class <> 'Idle' order by event;

       SID EVENT                        P1RAW            P2RAW
---------- ---------------------------- ---------------- ----------------
        31 SQL*Net message to client    0000000062657100 0000000000000001
        41 latch: shared pool           000000006010ED50 00000000000001C5
        37 latch: shared pool           000000006010ED50 00000000000001C5

6，上面两个匿名存储过程都是select语句，请求的CBC latch应该是共享锁，所以等待事件中没有出现热链争用，面是出现了latch: shared pool事件；新开两个会话259和268，将其中一个存储过程改为update就可以看到热链争用了，它们争用的latch也确实是00000000D17C9B68。

declare
r number;
begin
  for i in 1..10000000 loop
    select count(*) into r  from  SYS.WRI$_ADV_SQLT_PLANS where ROWID='AAAB5gAACAAAa+UAAB';
  end loop;
end;
/


declare
n varchar2(20);
begin
  for i in 1..10000000 loop
    n := 'kaka'||i ;
    update  t71  set name= n where id=1;
	commit;
  end loop;
end;
/


SQL> select sid,event,p1raw,p2raw  from v$session where wait_class <> 'Idle' order by event;

       SID EVENT                        P1RAW            P2RAW
---------- ---------------------------- ---------------- ----------------
        44 SQL*Net message to client    0000000062657100 0000000000000001
       268 latch: cache buffers chains  00000000D17C9B68 00000000000000E4
       259 latch: cache buffers chains  00000000D17C9B68 00000000000000E4
       243 log file parallel write      0000000000000001 0000000000000003

7，修改latch的数据，重启DB，可以看到测试的两个表已经不在同一个CBC latch下了，重启后要查看数据资料，使其进入缓冲区。

SQL> SELECT ksppinm, ksppstvl, ksppdesc FROM  x$ksppi x, x$ksppcv y WHERE  x.indx = y.indx AND  ksppinm = '_db_block_hash_latches';

KSPPINM                   KSPPSTVL   KSPPDESC
------------------------- ---------- -----------------------------------------
_db_block_hash_latches    8192       Number of database block hash latches


SQL> alter  system set "_db_block_hash_latches" = 32768 scope=spfile;

SQL> shutdown immediate;

SQL> startup;

SQL> SELECT ksppinm, ksppstvl, ksppdesc FROM  x$ksppi x, x$ksppcv y WHERE  x.indx = y.indx AND  ksppinm = '_db_block_hash_latches';

KSPPINM                   KSPPSTVL   KSPPDESC
------------------------- ---------- -----------------------------------------
_db_block_hash_latches    32768      Number of database block hash latches

SQL>  select *  from t71;  

SQL> select *  from SYS.WRI$_ADV_SQLT_PLANS;  

SQL>  select HLADDR from x$bh where file#=4 and  dbablk=327499;

HLADDR
----------------
00000000D18CEFE8

SQL> select HLADDR from x$bh where file#=2 and  dbablk=110484;

HLADDR
----------------
00000000D18CEE68

SQL>  select file#,dbablk,owner,object_name from x$bh a,dba_objects b where hladdr='00000000D18CEFE8' and a.obj=b.data_object_id;

     FILE#     DBABLK OWNER           OBJECT_NAME
---------- ---------- --------------- ---------------
         1      23644 SYS             IDL_UB2$
         4     327499 SYSTEM          T71

8，再次执行前两个存储过程，虽然仍有等待事件，但二者之间没有热链争用

SQL> select sid,event,p1raw,p2raw  from v$session where wait_class <> 'Idle' order by event;

       SID EVENT                        P1RAW            P2RAW
---------- ---------------------------- ---------------- ----------------
        12 SQL*Net message to client    0000000062657100 0000000000000001
        23 latch: In memory undo latch  00000000CE1FC560 0000000000000162
       259 latch: cache buffers chains  00000000D18CEFE8 00000000000000E4

提示：虽然修改隐藏参数可以解决热链争用，但一般不建议修改隐藏参数。