本文共 5387 字，大约阅读时间需要 17 分钟。
[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> @ver1PORT_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 ProductionSCOTT@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 testSCOTT@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 00000000BCAC7C58SCOTT@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 T50 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 ------------------ AAABbuAACAAAWP3AAASCOTT@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.转载地址：http://uhdox.baihongyu.com/