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
提示:虽然修改隐藏参数可以解决热链争用,但一般不建议修改隐藏参数。