分享程序员开发的那些事...
更新时间：2022-09-13 10:40:54
[20150123]热链竞争.txt
--最近一段时间都在看vage的,提到CBC latch保护多个链表,而且还要保护BH的读和修改操作.而一个链表中,可能
--会有多个BH,这样算下来,一个CBC latch除了操作多个链表以外,还要保护数目更多的BH.因此,当CBC Latch出现竞争时,可能是如下两种
--情况:
1.多个进程频繁地以不兼容的模式申请获得某一CBC LATCH,访问此CBC latch保护的不同链表和不同BH.
2.多个进程频繁地以不兼容的模式申请获得莫伊CBC LATCH,访问此CBC Latch保护的同一链表的同一BH.
在这两种情况中,第一种情况被称为热链竞争,第二种情况被称为热块竞争. P62.
--自己重复测试,出现热链的情况:
1.建立测试环境:
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> create table t as select rownum id,'test' data from dual connect by levelTable created.
SCOTT@test> select rowid,t.* from t where id=600;
ROWID ID DATA
------------------ ---------- ----
AABIyeAAEAAAAcMABL 600 test
SCOTT@test> @lookup_rowid AABIyeAAEAAAAcMABL
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
298142 4 1804 75 4,1804 alter system dump datafile 4 block 1804
;
2.查询对应的cbc latch地址:
SCOTT@test> select LRU_flag,hladdr from x$bh where dbarfil=4 and dbablk=1804 and OBJ=298142;
LRU_FLAG HLADDR
---------- ----------------
0 00000000BCAC7C58
SCOTT@test> select file#,dbablk,owner,object_id,data_object_id,object_name from x$bh a,dba_objects b where hladdr=hextoraw('00000000BCAC7C58') and a.obj=b.data_object_id;
FILE# DBABLK OWNER OBJECT_ID DATA_OBJECT_ID OBJECT_NAME
---------- ---------- ------ ---------- -------------- --------------------
1 70369 SYS 20 2 ICOL$
1 9171 SYS 20 2 ICOL$
1 70369 SYS 19 2 IND$
1 9171 SYS 19 2 IND$
1 70369 SYS 21 2 COL$
1 9171 SYS 21 2 COL$
1 70369 SYS 5 2 CLU$
1 9171 SYS 5 2 CLU$
1 48446 SYS 36 36 I_OBJ1
1 70369 SYS 2 2 C_OBJ#
1 9171 SYS 2 2 C_OBJ#
1 70369 SYS 4 2 TAB$
1 9171 SYS 4 2 TAB$
1 70369 SYS 80 2 LOB$
1 9171 SYS 80 2 LOB$
1 70369 SYS 83 2 COLTYPE$
1 9171 SYS 83 2 COLTYPE$
1 70369 SYS 86 2 SUBCOLTYPE$
1 9171 SYS 86 2 SUBCOLTYPE$
1 70369 SYS 88 2 NTAB$
1 9171 SYS 88 2 NTAB$
1 70369 SYS 92 2 REFCON$
1 9171 SYS 92 2 REFCON$
1 70369 SYS 95 2 OPQTYPE$
1 9171 SYS 95 2 OPQTYPE$
1 70369 SYS 114 2 ICOLDEP$
1 9171 SYS 114 2 ICOLDEP$
1 70369 SYS 173 2 VIEWTRCOL$
1 9171 SYS 173 2 VIEWTRCOL$
1 17847 SYS 232 232 I_ARGUMENT1
1 70369 SYS 251 2 LIBRARY$
1 9171 SYS 251 2 LIBRARY$
1 70369 SYS 252 2 ASSEMBLY$
1 9171 SYS 252 2 ASSEMBLY$
2 100036 SYS 267 267 SMON_SCN_TO_TIME_AUX
2 95465 SYS 267 267 SMON_SCN_TO_TIME_AUX
2 100036 SYS 269 267 SMON_SCN_TIME
2 95465 SYS 269 267 SMON_SCN_TIME
1 92292 SYS 420 420 C_OBJ#_INTCOL#
1 79045 SYS 420 420 C_OBJ#_INTCOL#
1 87721 SYS 420 420 C_OBJ#_INTCOL#
1 92292 SYS 422 420 HISTGRM$
1 79045 SYS 422 420 HISTGRM$
1 87721 SYS 422 420 HISTGRM$
1 70369 SYS 497 2 ATTRCOL$
1 9171 SYS 497 2 ATTRCOL$
1 70369 SYS 502 2 TYPE_MISC$
1 9171 SYS 502 2 TYPE_MISC$
2 91127 SYS 5870 5870 SCHEDULER$_EVENT_LOG
4 1804 SCOTT 298142 298142 T
50 rows selected.
--可以确定这些对象对应的块受到这个CBC latch的保护.
3.获得SYS.SCHEDULER$_EVENT_LOG第0行rowid.
SCOTT@test> select dbms_rowid.rowid_create(1,5870,2,91127,0) from dual ;
DBMS_ROWID.ROWID_C
------------------
AAABbuAACAAAWP3AAA
SCOTT@test> select LOG_DATE from sys.SCHEDULER$_EVENT_LOG where rowid='AAABbuAACAAAWP3AAA';
LOG_DATE
---------------------------------------------------------------------------
2012-05-12 22:09:39.833944
$ cat f1.sql
declare
m_id number;
m_data varchar2(200);
begin
for i in 1 .. 1e9 loop
select data into m_data from t where rowid='AABIyeAAEAAAAcMABL';
end loop;
end ;
/
$ cat g1.sql
declare
m_id number;
m_data varchar2(200);
begin
for i in 1 .. 1e9 loop
select to_char(LOG_DATE,'yyyymmdd') into m_data from sys.SCHEDULER$_EVENT_LOG where rowid='AAABbuAACAAAWP3AAA';
end loop;
end ;
/
00000000BCAC7C58
sqlplus scott/btbtms @f1.sql &
sqlplus scott/btbtms @g1.sql &
--也许我的机器太快了,2个会话根本看不到latch: cache buffers chains.
$ cat f.sh
#! /bin/bash
sqlplus -s scott/btbtms @ $1 &
sqlplus -s scott/btbtms @ $1 &
sqlplus -s scott/btbtms @ $1 &
sqlplus -s scott/btbtms @ $1 &
source f.sh f1.sql
source f.sh g1.sql
--启动4+4个会话,如果出现5个以上latch: cache buffers chains等待事件基本可以确定,P1RAW的信息都是00000000BCAC7C58.
SCOTT@test> select p1raw,p2raw,sid,serial#,seq#,event,state,wait_time_micro,seconds_in_wait from v$session where wait_class'Idle' order by event ;
P1RAW P2RAW SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------- ---------- ---------- ---------------------------------------- ------------------- --------------- ---------------
0000000062657100 0000000000000001 18 123 2449 SQL*Net message to client WAITED SHORT TIME 3 0
0000000080018467 0000000000000002 17 397 76 cursor: pin S WAITED KNOWN TIME 100077 0
00000000C12F4A4F 000000CF00000004 20 195 317 cursor: pin S WAITED KNOWN TIME 99767 0
00000000BCAC7C58 000000000000009B 207 407 331 latch: cache buffers chains WAITED KNOWN TIME 69568 0
00000000BCAC7C58 000000000000009B 400 43 293 latch: cache buffers chains WAITED SHORT TIME 785 0
00000000BCAC7C58 000000000000009B 204 341 74 latch: cache buffers chains WAITED KNOWN TIME 69055 0
00000000BCAC7C58 000000000000009B 596 23 344 latch: cache buffers chains WAITED SHORT TIME 4253 0
00000000BCAC7C58 000000000000009B 13 59 88 latch: cache buffers chains WAITED SHORT TIME 811 0
00000000BCAC7C58 000000000000009B 202 41 66 latch: cache buffers chains WAITED KNOWN TIME 69574 0
9 rows selected.