这篇文章和之前的《MINSCN与Cache Fusion Read Consistent》 是姊妹篇,他们源于同一个问题帖子。
我们来重现提问者所看到的这样一个场景:
SQL> select * from V$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> select count(*) from gv$instance;
COUNT(*)
----------
2
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
www.oracledatabase12g.com
在11gR2 2节点RAC环境中将一个数据块的status修改为XG,假设这个Xcurrent block当前在INSTANCE 2被hold住,这时我们在INSTANCE 1反复查询这个数据块,并观察结果:
SQL> select * from test;
ID
----------
1
2
SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from test;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
89233 1
89233 1
SQL> alter system flush buffer_cache;
System altered.
INSTANCE 1 Session A:
SQL> update test set id=id+1 where id=1;
1 row updated.
INSTANCE 1 Session B:
SQL> select state,cr_scn_bas from x$bh where file#=1 and dbablk=89233 and state!=0;
STATE CR_SCN_BAS
---------- ----------
1 0
3 1755287
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump gc_elements 255;
Statement processed.
SQL> oradebug tracefile_name;
/s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_ora_19111.trc
GLOBAL CACHE ELEMENT DUMP (address: 0xa4ff3080):
id1: 0x15c91 id2: 0x1 pkey: OBJ#76896 block: (1/89233)
lock: X rls: 0x0 acq: 0x0 latch: 3
flags: 0x20 fair: 0 recovery: 0 fpin: 'kdswh11: kdst_fetch'
bscn: 0x0.146e20 bctx: (nil) write: 0 scan: 0x0
lcp: (nil) lnk: [NULL] lch: [0xa9f6a6f8,0xa9f6a6f8]
seq: 32 hist: 58 145:0 118 66 144:0 192 352 197 48 121 113 424 180 58
LIST OF BUFFERS LINKED TO THIS GLOBAL CACHE ELEMENT:
flg: 0x02000001 lflg: 0x1 state: XCURRENT tsn: 0 tsh: 2
addr: 0xa9f6a5c8 obj: 76896 cls: DATA bscn: 0x0.1ac898
BH (0xa9f6a5c8) file#: 1 rdba: 0x00415c91 (1/89233) class: 1 ba: 0xa9e56000
set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 3 pwc: 0,15
dbwrid: 0 obj: 76896 objn: 76896 tsn: 0 afn: 1 hint: f
hash: [0x91f4e970,0xbae9d5b8] lru: [0x91f58848,0xa9f6a828]
lru-flags: debug_dump
obj-flags: object_ckpt_list
ckptq: [0x9df6d1d8,0xa9f6a740] fileq: [0xa2ece670,0xbdf4ed68] objq: [0xb4964e00,0xb4964e00] objaq: [0xb4964de0,0xb4964de0]
st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 2 le: 0xa4ff3080
flags: buffer_dirty redo_since_read
LRBA: [0x19.5671.0] LSCN: [0x0.1ac898] HSCN: [0x0.1ac898] HSUB: [1]
buffer tsn: 0 rdba: 0x00415c91 (1/89233)
scn: 0x0000.001ac898 seq: 0x01 flg: 0x00 tail: 0xc8980601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
可以看到此时block: (1/89233)的GLOBAL CACHE ELEMENT DUMP中LOCK状态仍是X 而非XG , 这是因为这个Current Block仅在一个Instance中被modify修改过,没有在全局范围内被更新过。
紧接着在Instance 2 修改该块:
Instance 2 Session C:
SQL> update test set id=id+1 where id=2;
1 row updated.
Instance 2 Session D:
SQL> select state,cr_scn_bas from x$bh where file#=1 and dbablk=89233 and state!=0;
STATE CR_SCN_BAS
---------- ----------
1 0
3 1756658
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump gc_elements 255;
Statement processed.
SQL> oradebug tracefile_name;
/s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_ora_13038.trc
GLOBAL CACHE ELEMENT DUMP (address: 0x89fb25a0):
id1: 0x15c91 id2: 0x1 pkey: OBJ#76896 block: (1/89233)
lock: XG rls: 0x0 acq: 0x0 latch: 3
flags: 0x20 fair: 0 recovery: 0 fpin: 'kduwh01: kdusru'
bscn: 0x0.1acdf3 bctx: (nil) write: 0 scan: 0x0
lcp: (nil) lnk: [NULL] lch: [0x96f4cf80,0x96f4cf80]
seq: 61 hist: 324 21 143:0 19 16 352 329 144:6 14 7 352 197
LIST OF BUFFERS LINKED TO THIS GLOBAL CACHE ELEMENT:
flg: 0x0a000001 state: XCURRENT tsn: 0 tsh: 1
addr: 0x96f4ce50 obj: 76896 cls: DATA bscn: 0x0.1acdf6
BH (0x96f4ce50) file#: 1 rdba: 0x00415c91 (1/89233) class: 1 ba: 0x96bd4000
set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
dbwrid: 0 obj: 76896 objn: 76896 tsn: 0 afn: 1 hint: f
hash: [0x96ee1fe8,0xbae9d5b8] lru: [0x96f4d0b0,0x96f4cdc0]
obj-flags: object_ckpt_list
ckptq: [0xbdf519b8,0x96f4d5a8] fileq: [0xbdf519d8,0xbdf519d8] objq: [0xb4a47b90,0xb4a47b90] objaq: [0x96f4d0e8,0xb4a47b70]
st: XCURRENT md: NULL fpin: 'kduwh01: kdusru' tch: 1 le: 0x89fb25a0
flags: buffer_dirty redo_since_read remote_transfered
LRBA: [0x11.9e18.0] LSCN: [0x0.1acdf6] HSCN: [0x0.1acdf6] HSUB: [1]
buffer tsn: 0 rdba: 0x00415c91 (1/89233)
scn: 0x0000.001acdf6 seq: 0x01 flg: 0x00 tail: 0xcdf60601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
GCS CLIENT 0x89fb2618,6 resp[(nil),0x15c91.1] pkey 76896.0
grant 2 cvt 0 mdrole 0x42 st 0x100 lst 0x20 GRANTQ rl G0
master 1 owner 2 sid 0 remote[(nil),0] hist 0x94121c601163423c
history 0x3c.0x4.0xd.0xb.0x1.0xc.0x7.0x9.0x14.0x1.
cflag 0x0 sender 1 flags 0x0 replay# 0 abast (nil).x0.1 dbmap (nil)
disk: 0x0000.00000000 write request: 0x0000.00000000
pi scn: 0x0000.00000000 sq[(nil),(nil)]
msgseq 0x1 updseq 0x0 reqids[6,0,0] infop (nil) lockseq x2b8
pkey 76896.0
hv 93 [stat 0x0, 1->1, wm 32768, RMno 0, reminc 18, dom 0]
kjga st 0x4, step 0.0.0, cinc 20, rmno 6, flags 0x0
lb 0, hb 0, myb 15250, drmb 15250, apifrz 0
在Instance 2中被修改过后block: (1/89233)的 GLOBAL CACHE ELEMENT Lock Convert成lock: XG
除了通过GC_ELEMENTS DUMP来分析XCUR Cache Fusion外,也可以直接查询X$ VIEW,主要是 X$LE X$KJBR X$KJBL, 这三个X$ VIEW的更多信息可以很方便地从我的博客中找到:
INSTANCE 2 Session D:
SELECT *
FROM x$le
WHERE le_addr IN (SELECT le_addr
FROM x$bh
WHERE obj IN (SELECT data_object_id
FROM dba_objects
WHERE owner = 'SYS'
AND object_name = 'TEST')
AND class = 1
AND state != 3);
ADDR INDX INST_ID LE_ADDR LE_ID1 LE_ID2
---------------- ---------- ---------- ---------------- ---------- ----------
LE_RLS LE_ACQ LE_FLAGS LE_MODE LE_WRITE LE_LOCAL LE_RECOVERY
---------- ---------- ---------- ---------- ---------- ---------- -----------
LE_BLKS LE_TIME LE_KJBL
---------- ---------- ----------------
00007F94CA14CF60 7003 2 0000000089FB25A0 89233 1
0 0 32 2 0 1 0
1 0 0000000089FB2618
PCM Resource NAME由[ID1][ID2],[BL]等组成, ID1和ID2 通过blockno和 fileno计算获得, 这里我们直接参考以上GC_elements dump中的 id1: 0x15c91 id2: 0x1 pkey: OBJ#76896 block: (1/89233)信息 ,则