初探Cache Fusion對block的鎖管理
本文以雙節點RAC為例，揭示了在執行select及DML操作過程中，Cache Fusion在幕後是如何對block進行鎖管理的。
###例項1上查詢scott.t0820_1表
select * from scott.t0820_1;
ID
----------
2
select dbms_rowid.rowid_relative_fno(rowid) rfno,dbms_rowid.rowid_block_number(rowid) blkno from scott.t0820_1;
RFNO BLKNO
---------- ----------
6 255
select to_char(6,'xxx'),to_char(255,'xxx') from dual;
TO_C TO_C
---- ----
6 ff
select object_id,data_object_id from dba_objects where object_name='T0820_1';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
15976 15976
###用到的指令碼
//// get_buffer_stat.sql ////
col object_name for a10
select (select object_name from dba_objects where object_id = b.obj) as object_name,decode (state,0, 'Free', 1, 'XCUR', 2, 'SCUR', 3, 'CR', 4,'BEING READ',5, 'MREC', 6, 'IREC', 7, 'WRITE_CLONE', 8, 'PI') state,mode_held, le_addr, cr_scn_bas, cr_scn_wrp from x$bh b where obj = 15976 and dbablk = 255 and class = 1;
//// get_resource_name.sql，獲得block在GRD記憶體的資源名////
col hexname for a35
col resource_name for a15
set linesize 170
select b.kjblname hexname, b.kjblname2 resource_name,b.kjblgrant, b.kjblrole, b.kjblowner,b.kjblmaster,b.KJBLPKEY,b.kjblsid,b.kjblrequest from x$le a, x$kjbl b where a.le_kjbl=b.kjbllockp and a.le_addr = ( select le_addr from x$bh where dbablk = 255 and obj = 15976 and class = 1 and state <> 3);
//// get_resource_stat.sql 獲得block資源的授權訪問資訊，注意：因為v$dlm_ress只在block的主節點上才能查到，而v$ges_enqueue在兩節點上都有，所以查詢結果表示block以inst_id所指節點為主節點，inst_id=mast+1 ////
col resource_name for a34
col state for a8
col mast for 9999
col grnt for 9999
col cnvt for 9999
set linesize 160
select a.inst_id,a.resource_name, b.state, a.master_node mast, a.on_convert_q cnvt,a.on_grant_q grnt, b.request_level, b.grant_level, b.owner_node from gv$dlm_ress a, gv$ges_enqueue b where upper(a.resource_name) = upper(b.resource_name1) and a.resource_name like '%[0xff][0x6]%' and a.inst_id=b.inst_id;
//// get_master_node.sql 獲得block資源的主節點 ////
col resource_name for a34
col state for a8
col mast for 9999
col grnt for 9999
col cnvt for 9999
set linesize 160
select inst_id,resource_name,ON_GRANT_Q,ON_CONVERT_Q,master_node from gv$ges_resource where resource_name like '[0xff][0x6],[BL]%';
//// get_ges_enqueue.sql 查詢gv$ges_enqueue檢視////
set linesize 180
select inst_id,owner_node,resource_name1,resource_name2,GRANT_LEVEL,REQUEST_LEVEL from gv$ges_enqueue where upper(RESOURCE_NAME1) like '[0XFF][0X6],[BL]%' order by inst_id,owner_node;
---例項1上執行get系列指令碼
###因為之前例項1訪問過這個block所以以下指令碼有輸出
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- -------- ---------- ---------------- ---------- ----------
T0820_1 SCUR 0 000000008BFAEE38 0 0
###block的主節點在例項2上，因為MASTER_NODE=1
SQL> @get_master_node.sql
INST_ID RESOURCE_NAME ON_GRANT_Q ON_CONVERT_Q MASTER_NODE
---------- ---------------------------------- ---------- ------------ -----------
2 [0xff][0x6],[BL][ext 0x0,0x0] 1 0 1
###獲得block的資源名稱，只有訪問過這個block的節點才能查到
SQL> @get_resource_name
HEXNAME RESOURCE_NAME KJBLGRANT KJBLROLE KJBLOWNER KJBLMASTER KJBLPKEY KJBLSID KJBLREQUE
----------------------------------- --------------- --------- ---------- ---------- ---------- ---------- ---------- ---------
[0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSERPR 0 0 1 15976 0 KJUSERNL <---KJBLPKEY等於object_id
###inst_id=1或者MAST=0都表示block的主節點在例項2上，owner_node=0、state=GRANTED及grant_level=KJUSERPR表示目前例項1讀取了這個block，經例項2的允許例項1在block上加了KJUSERPR鎖
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
2 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 1 0 1 KJUSERNL KJUSERPR 0
*************
* 人工將主節點從例項2調整為例項1
*************
---在例項1上執行
###嘗試將block的主節點從例項2人工調整為例項1
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug lkdebug -m pkey 15976
Statement processed.
###MASTER_NODE=0表明block 6/255的主節點為例項1
SQL> @get_master_node.sql
INST_ID RESOURCE_NAME ON_GRANT_Q ON_CONVERT_Q MASTER_NODE
---------- ---------------------------------- ---------- ------------ -----------
1 [0xff][0x6],[BL][ext 0x0,0x0] 1 0 0
###以下兩個輸出除了MASTER變成0，其他均和上一輪的輸出保持一致
SQL> @get_resource_name
HEXNAME RESOURCE_NAME KJBLGRANT KJBLROLE KJBLOWNER KJBLMASTER KJBLPKEY KJBLSID KJBLREQUE
----------------------------------- --------------- --------- ---------- ---------- ---------- ---------- ---------- ---------
[0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSERPR 0 0 0 15976 0 KJUSERNL
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSERPR 0 <---inst_id說明主節點是例項1，owner_node=0表示例項1曾經訪問過block 6/255、並持有KJUSERPR鎖
---例項2上執行get系列指令碼
###例項2尚未訪問過該block所以無輸出
SQL> @get_buffer_stat.sql
no rows selected
###來看看例項2上此時的輸出
SQL> @get_master_node.sql
INST_ID RESOURCE_NAME ON_GRANT_Q ON_CONVERT_Q MASTER_NODE
---------- ---------------------------------- ---------- ------------ -----------
1 [0xff][0x6],[BL][ext 0x0,0x0] 1 0 0 <---ON_GRANT_Q=1表示block已經被授予某個節點的訪問許可權
SQL> @get_resource_name
no rows selected <---因為例項2尚未訪問過該block所以尚無輸出
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSERPR 0
---例項2上執行block 6/255的查詢、然後執行get系列指令碼
select * from scott.t0820_1;
ID
----------
2
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- -------- ---------- ---------------- ---------- ----------
T0820_1 SCUR 0 000000008AFF8E28 0 0
SQL> @get_master_node.sql
INST_ID RESOURCE_NAME ON_GRANT_Q ON_CONVERT_Q MASTER_NODE
---------- ---------------------------------- ---------- ------------ -----------
1 [0xff][0x6],[BL][ext 0x0,0x0] 1 0 0
SQL> @get_resource_name
HEXNAME RESOURCE_NAME KJBLGRANT KJBLROLE KJBLOWNER KJBLMASTER KJBLPKEY KJBLSID KJBLREQUE
----------------------------------- --------------- --------- ---------- ---------- ---------- ---------- ---------- ---------
[0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSERPR 0 1 0 15976 0 KJUSERNL <---KJBLOWNER=1
###inst_id=1表示block主節點是例項1，輸出的兩行分別表示例項1、例項2均以讀的方式訪問block
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSERPR 0
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSERPR 1
###作為主節點的例項1記錄了所有訪問過該block的節點資訊(inst_id=1的兩條記錄，owner_node=0記錄例項1的訪問，owner_node=1記錄了例項2的訪問)，主節點所掌握的資訊在RAC裡稱為master metadata；而例項2只記錄了自己對於block的訪問(inst_id=2的那條記錄)，非主節點掌握的資訊在RAC裡被稱為shadow metadata
SQL> @get_ges_enqueue.sql
INST_ID OWNER_NODE RESOURCE_NAME1 RESOURCE_NAME2 GRANT_LEV REQUEST_L
---------- ---------- ------------------------------ ------------------------------ --------- ---------
1 0 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSERPR KJUSERNL
1 1 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSERPR KJUSERNL
2 1 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSERPR KJUSERNL
*************
* 人工將主節點從例項1調整為例項2，先後在例項2、例項1上發起update操作
*************
---例項2上透過執行oradebug把block 6/255的主節點再次重置為例項2，觀察master metadata是否都轉移到了例項2上
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug lkdebug -m pkey 15976
Statement processed.
###果然例項2對該block的記錄變為了兩條(inst_id=2有兩條)，表明主節點對應的例項上儲存RAC環境裡所有節點對某個block訪問時持有的鎖資訊
SQL> @get_ges_enqueue.sql
INST_ID OWNER_NODE RESOURCE_NAME1 RESOURCE_NAME2 GRANT_LEV REQUEST_L
---------- ---------- ------------------------------ ------------------------------ --------- ---------
1 0 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSERPR KJUSERNL
2 0 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSERPR KJUSERNL
2 1 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSERPR KJUSERNL
---例項2上發起對block的更改，執行get系列指令碼
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- ----------- ---------- ---------------- ---------- ----------
T0820_1 SCUR 0 0000000089F90298 0 0
update scott.t0820_1 set id=id+1;
commit;
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- -------- ---------- ---------------- ---------- ----------
T0820_1 XCUR 0 0000000089F90298 0 0
T0820_1 CR 0 00 1947380 0
SQL> @get_resource_name.sql
HEXNAME RESOURCE_NAME KJBLGRANT KJBLROLE KJBLOWNER KJBLMASTER KJBLPKEY KJBLSID KJBLREQUE
----------------------------------- --------------- --------- ---------- ---------- ---------- ---------- ---------- ---------
[0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX 0 1 1 15976 0 KJUSERNL
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
2 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 1 0 1 KJUSERNL KJUSEREX 1
SQL> @get_ges_enqueue.sql
INST_ID OWNER_NODE RESOURCE_NAME1 RESOURCE_NAME2 GRANT_LEV REQUEST_L
---------- ---------- ------------------------------ ------------------------------ --------- ---------
2 1 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL <---作為主節點的例項2 master metadata資訊裡包含了自己(OWNER_NODE=1)修改block時加的X鎖
---例項1上執行get系列指令碼
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- ----------- ---------- ---------------- ---------- ----------
T0820_1 CR 0 00 1947379 0
SQL> @get_resource_name.sql
no rows selected <---get_resource_name.sql指令碼實質上是關聯x$le、x$kjbl兩張表得出例項對block施加的鎖資訊，沒有輸出是因為例項1的cache裡只有CR型別的buffer，只能給自己使用，不能分享其它節點，所以無需持有任何鎖
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
2 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 1 0 1 KJUSERNL KJUSEREX 1 <---只剩下了例項2 update block時留下的X鎖，例項1先期加在block上的Share鎖已解除
SQL> @get_ges_enqueue.sql
INST_ID OWNER_NODE RESOURCE_NAME1 RESOURCE_NAME2 GRANT_LEV REQUEST_L
---------- ---------- ------------------------------ ------------------------------ --------- ---------
2 1 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL <---作為主節點的例項2 master metadata資訊裡包含了自己(OWNER_NODE=1)修改block時加的X鎖，沒有inst_id=1的記錄說明例項1沒有關於該block的shadow metadata
---例項1上發起對block的更改，執行get系列指令碼
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- -------- ---------- ---------------- ---------- ----------
T0820_1 CR 0 00 1947379 0
update scott.t0820_1 set id=id+1;
commit;
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- -------- ---------- ---------------- ---------- ----------
T0820_1 XCUR 0 0000000089F95818 0 0
T0820_1 CR 0 00 0
T0820_1 CR 0 00 1947379 0
SQL> @get_resource_name.sql
HEXNAME RESOURCE_NAME KJBLGRANT KJBLROLE KJBLOWNER KJBLMASTER KJBLPKEY KJBLSID KJBLREQUE
----------------------------------- --------------- --------- ---------- ---------- ---------- ---------- ---------- ---------
[0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX 0 0 1 15976 0 KJUSERNL <---KJBLOWNER=0表示例項1訪問了block
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
2 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 1 0 1 KJUSERNL KJUSEREX 0 <---OWNER_NODE=0說明之前例項2修改block時加上的X鎖已被釋放，改為記錄本次例項1修改block時的鎖資訊
SQL> @get_ges_enqueue.sql
INST_ID OWNER_NODE RESOURCE_NAME1 RESOURCE_NAME2 GRANT_LEV REQUEST_L
---------- ---------- ------------------------------ ------------------------------ --------- ---------
1 0 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL <---因為最近一次修改block是從例項1發起，所以例項1的GRD裡記錄了OWNER_NODE=0(代表例項1)修改時持有的鎖資訊；例項2之所以會記錄例項1鎖修改時的鎖資訊是因為例項2是master node，必須記錄所有訪問過該block的節點資訊
2 0 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL
---例項2上執行get系列指令碼
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- -------- ---------- ---------------- ---------- ----------
T0820_1 CR 0 00 1950280 0
T0820_1 CR 0 00 1947380 0
SQL> @get_resource_name.sql <---例項1發起更改後，例項2 cache裡只有CR型別的buffer，只能給自己使用，不能分享其它節點，所以無需持有任何鎖(實質是x$bh.le_addr=NULL所以與x$le.le_addr關聯無結果)
no rows selected
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
2 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 1 0 1 KJUSERNL KJUSEREX 0
SQL> @get_ges_enqueue.sql
INST_ID OWNER_NODE RESOURCE_NAME1 RESOURCE_NAME2 GRANT_LEV REQUEST_L
---------- ---------- ------------------------------ ------------------------------ --------- ---------
1 0 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL
2 0 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL
如果此時我們將block的master node再一次指回例項1，那麼get_ges_enqueue.sql僅會顯示inst_id=1的記錄，因為例項2不再是主節點，所以沒必要儲存其它節點的block鎖資訊
*************
* 人工將主節點從例項2調整回例項1，觀察v$ges_enqueue檢視的內容
*************
---例項1執行
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug lkdebug -m pkey 15976
Statement processed.
SQL> @get_ges_enqueue.sql
INST_ID OWNER_NODE RESOURCE_NAME1 RESOURCE_NAME2 GRANT_LEV REQUEST_L
---------- ---------- ------------------------------ ------------------------------ --------- ---------
1 0 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL <---僅有節點1(owner_node=0)的block鎖資訊儲存在節點1(inst_id=1)上
*************
* 重啟資料庫後，執行update但不提交的情況下，block鎖資源查詢
*************
###重啟資料庫
srvctl stop database -d susedb1 -o immediate
srvctl start database -d susedb1
---例項1、例項2分別查詢scott.t0820_1表
select * from scott.t0820_1;
---例項1查詢block 6/255資源上的鎖持有情況，此時例項1是主節點，例項1、例項2都對block持有共享讀模式的鎖
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSERPR 0
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSERPR 1
---例項1執行update後不提交，緊接著執行get系列指令碼
update scott.t0820_1 set id=id+1;
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- -------- ---------- ---------------- ---------- ----------
T0820_1 XCUR 0 0000000089F90C18 0 0
T0820_1 CR 0 00 2015904 0
SQL> @get_resource_name.sql
HEXNAME RESOURCE_NAME KJBLGRANT KJBLROLE KJBLOWNER KJBLMASTER KJBLPKEY KJBLSID KJBLREQUE
----------------------------------- --------------- --------- ---------- ---------- ---------- ---------- ---------- ---------
[0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX 0 0 0 15976 0 KJUSERNL
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSEREX 0
SQL> @get_ges_enqueue.sql
INST_ID OWNER_NODE RESOURCE_NAME1 RESOURCE_NAME2 GRANT_LEV REQUEST_L
---------- ---------- ------------------------------ ------------------------------ --------- ---------
1 0 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL
---例項2執行get系列指令碼檢視資源狀態資訊
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- ----------- ---------- ---------------- ---------- ----------
T0820_1 CR 0 00 2015903 0
SQL> @get_resource_name.sql
no rows selected <---原有的KJUSERPR鎖解除
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSEREX 0
SQL> @get_ges_enqueue.sql
INST_ID OWNER_NODE RESOURCE_NAME1 RESOURCE_NAME2 GRANT_LEV REQUEST_L
---------- ---------- ------------------------------ ------------------------------ --------- ---------
1 0 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL
---例項2 update同一條記錄，
update scott.t0820_1 set id=id+1;
<----處於等待
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- ----------- ---------- ---------------- ---------- ----------
T0820_1 XCUR 0 0000000089F96198 0 0
T0820_1 CR 1 00 2016429 0
T0820_1 CR 0 00 2015903 0
SQL> @get_resource_name.sql
HEXNAME RESOURCE_NAME KJBLGRANT KJBLROLE KJBLOWNER KJBLMASTER KJBLPKEY KJBLSID KJBLREQUE
----------------------------------- --------------- --------- ---------- ---------- ---------- ---------- ---------- ---------
[0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX 64 1 0 15976 0 KJUSERNL
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSERNL 0 <---如果例項1的buffer cache裡有pi型別的block在，才會有這條記錄
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSEREX 1 <---雖然例項2的會話因為鎖而處於等待，但GRANT_LEVEL還是顯示為KJUSEREX，從GC檢視看不出行級鎖的存在
SQL> @get_ges_enqueue.sql
INST_ID OWNER_NODE RESOURCE_NAME1 RESOURCE_NAME2 GRANT_LEV REQUEST_L
---------- ---------- ------------------------------ ------------------------------ --------- ---------
1 0 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSERNL KJUSERNL
1 1 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL
2 1 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL <---雖然例項2的會話因為鎖而處於等待，但GRANT_LEVEL還是顯示為KJUSEREX
---例項1執行get系列指令碼
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- -------- ---------- ---------------- ---------- ----------
T0820_1 CR 0 00 2016429 0
T0820_1 CR 0 00 2016430 0
T0820_1 CR 0 00 2015904 0
SQL> @get_resource_name.sql
no rows selected <---因為例項1的buffer裡都是CR型別的block，所以這裡必然返回0條記錄
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSEREX 1 <---例項1的buffer cache裡有pi型別的block因為checkpoint發生變成CR了，所以相比上次在例項2的執行，結果裡少了OWNER_NODE=1的記錄
SQL> @get_ges_enqueue.sql
INST_ID OWNER_NODE RESOURCE_NAME1 RESOURCE_NAME2 GRANT_LEV REQUEST_L
---------- ---------- ------------------------------ ------------------------------ --------- ---------
1 1 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL <---相比上次在例項2執行的輸出少了owner_node=0、GRANT_LEVEL/REQUEST_LEVEL=KJUSERNL的記錄，因為例項1上的pi block變成了CR
2 1 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL
*************
* 例項1回滾後，block資源使用情況觀察
*************
---例項1 執行rollback回滾剛才的更改
SQL> update scott.t0820_1 set id=id+1;
1 row updated.
SQL> rollback;
Rollback complete.
SQL> @get_buffer_stat.sql <---回滾後可看到PI出現
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- ----------- ---------- ---------------- ---------- ----------
T0820_1 PI 0 0000000089F90C18 0 0
T0820_1 CR 0 00 2016429 0
T0820_1 CR 0 00 2016430 0
T0820_1 CR 0 00 2015904 0
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSERNL 0 <---PI block保留有KJUSERNL鎖，這點與CR block不同，後者沒有任何鎖
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSEREX 1
SQL> alter system checkpoint;
System altered.
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- -------- ---------- ---------------- ---------- ----------
T0820_1 CR 0 00 2020380 0 <---checkpoint發生後PI變成了CR
T0820_1 CR 0 00 2016429 0
T0820_1 CR 0 00 2016430 0
T0820_1 CR 0 00 2015904 0
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSEREX 1 <---REQUEST_LEVEL/GRANT_LEVEL均為KJUSERNL的記錄消失
總結:
1、當多個節點在自己的buffer cache裡擁有同一block的scur buffer時，他們對block持有的KJUSERPR型別的鎖資訊可以並存於GRD
2、當某節點修改了block，該block xcur型別的buffer出現在執行修改操作節點的buffer cache，其餘節點只會有PI或CR型別的buffer，對於每個PI類的buffer GRD裡各保留一行REQUEST_LEVEL/GRANT_LEVEL均為KJUSERNL的記錄；對於CR類的buffer GRD裡不保留任何鎖資訊
###例項1上查詢scott.t0820_1表
select * from scott.t0820_1;
ID
----------
2
select dbms_rowid.rowid_relative_fno(rowid) rfno,dbms_rowid.rowid_block_number(rowid) blkno from scott.t0820_1;
RFNO BLKNO
---------- ----------
6 255
select to_char(6,'xxx'),to_char(255,'xxx') from dual;
TO_C TO_C
---- ----
6 ff
select object_id,data_object_id from dba_objects where object_name='T0820_1';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
15976 15976
###用到的指令碼
//// get_buffer_stat.sql ////
col object_name for a10
select (select object_name from dba_objects where object_id = b.obj) as object_name,decode (state,0, 'Free', 1, 'XCUR', 2, 'SCUR', 3, 'CR', 4,'BEING READ',5, 'MREC', 6, 'IREC', 7, 'WRITE_CLONE', 8, 'PI') state,mode_held, le_addr, cr_scn_bas, cr_scn_wrp from x$bh b where obj = 15976 and dbablk = 255 and class = 1;
//// get_resource_name.sql，獲得block在GRD記憶體的資源名////
col hexname for a35
col resource_name for a15
set linesize 170
select b.kjblname hexname, b.kjblname2 resource_name,b.kjblgrant, b.kjblrole, b.kjblowner,b.kjblmaster,b.KJBLPKEY,b.kjblsid,b.kjblrequest from x$le a, x$kjbl b where a.le_kjbl=b.kjbllockp and a.le_addr = ( select le_addr from x$bh where dbablk = 255 and obj = 15976 and class = 1 and state <> 3);
//// get_resource_stat.sql 獲得block資源的授權訪問資訊，注意：因為v$dlm_ress只在block的主節點上才能查到，而v$ges_enqueue在兩節點上都有，所以查詢結果表示block以inst_id所指節點為主節點，inst_id=mast+1 ////
col resource_name for a34
col state for a8
col mast for 9999
col grnt for 9999
col cnvt for 9999
set linesize 160
select a.inst_id,a.resource_name, b.state, a.master_node mast, a.on_convert_q cnvt,a.on_grant_q grnt, b.request_level, b.grant_level, b.owner_node from gv$dlm_ress a, gv$ges_enqueue b where upper(a.resource_name) = upper(b.resource_name1) and a.resource_name like '%[0xff][0x6]%' and a.inst_id=b.inst_id;
//// get_master_node.sql 獲得block資源的主節點 ////
col resource_name for a34
col state for a8
col mast for 9999
col grnt for 9999
col cnvt for 9999
set linesize 160
select inst_id,resource_name,ON_GRANT_Q,ON_CONVERT_Q,master_node from gv$ges_resource where resource_name like '[0xff][0x6],[BL]%';
//// get_ges_enqueue.sql 查詢gv$ges_enqueue檢視////
set linesize 180
select inst_id,owner_node,resource_name1,resource_name2,GRANT_LEVEL,REQUEST_LEVEL from gv$ges_enqueue where upper(RESOURCE_NAME1) like '[0XFF][0X6],[BL]%' order by inst_id,owner_node;
---例項1上執行get系列指令碼
###因為之前例項1訪問過這個block所以以下指令碼有輸出
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- -------- ---------- ---------------- ---------- ----------
T0820_1 SCUR 0 000000008BFAEE38 0 0
###block的主節點在例項2上，因為MASTER_NODE=1
SQL> @get_master_node.sql
INST_ID RESOURCE_NAME ON_GRANT_Q ON_CONVERT_Q MASTER_NODE
---------- ---------------------------------- ---------- ------------ -----------
2 [0xff][0x6],[BL][ext 0x0,0x0] 1 0 1
###獲得block的資源名稱，只有訪問過這個block的節點才能查到
SQL> @get_resource_name
HEXNAME RESOURCE_NAME KJBLGRANT KJBLROLE KJBLOWNER KJBLMASTER KJBLPKEY KJBLSID KJBLREQUE
----------------------------------- --------------- --------- ---------- ---------- ---------- ---------- ---------- ---------
[0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSERPR 0 0 1 15976 0 KJUSERNL <---KJBLPKEY等於object_id
###inst_id=1或者MAST=0都表示block的主節點在例項2上，owner_node=0、state=GRANTED及grant_level=KJUSERPR表示目前例項1讀取了這個block，經例項2的允許例項1在block上加了KJUSERPR鎖
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
2 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 1 0 1 KJUSERNL KJUSERPR 0
*************
* 人工將主節點從例項2調整為例項1
*************
---在例項1上執行
###嘗試將block的主節點從例項2人工調整為例項1
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug lkdebug -m pkey 15976
Statement processed.
###MASTER_NODE=0表明block 6/255的主節點為例項1
SQL> @get_master_node.sql
INST_ID RESOURCE_NAME ON_GRANT_Q ON_CONVERT_Q MASTER_NODE
---------- ---------------------------------- ---------- ------------ -----------
1 [0xff][0x6],[BL][ext 0x0,0x0] 1 0 0
###以下兩個輸出除了MASTER變成0，其他均和上一輪的輸出保持一致
SQL> @get_resource_name
HEXNAME RESOURCE_NAME KJBLGRANT KJBLROLE KJBLOWNER KJBLMASTER KJBLPKEY KJBLSID KJBLREQUE
----------------------------------- --------------- --------- ---------- ---------- ---------- ---------- ---------- ---------
[0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSERPR 0 0 0 15976 0 KJUSERNL
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSERPR 0 <---inst_id說明主節點是例項1，owner_node=0表示例項1曾經訪問過block 6/255、並持有KJUSERPR鎖
---例項2上執行get系列指令碼
###例項2尚未訪問過該block所以無輸出
SQL> @get_buffer_stat.sql
no rows selected
###來看看例項2上此時的輸出
SQL> @get_master_node.sql
INST_ID RESOURCE_NAME ON_GRANT_Q ON_CONVERT_Q MASTER_NODE
---------- ---------------------------------- ---------- ------------ -----------
1 [0xff][0x6],[BL][ext 0x0,0x0] 1 0 0 <---ON_GRANT_Q=1表示block已經被授予某個節點的訪問許可權
SQL> @get_resource_name
no rows selected <---因為例項2尚未訪問過該block所以尚無輸出
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSERPR 0
---例項2上執行block 6/255的查詢、然後執行get系列指令碼
select * from scott.t0820_1;
ID
----------
2
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- -------- ---------- ---------------- ---------- ----------
T0820_1 SCUR 0 000000008AFF8E28 0 0
SQL> @get_master_node.sql
INST_ID RESOURCE_NAME ON_GRANT_Q ON_CONVERT_Q MASTER_NODE
---------- ---------------------------------- ---------- ------------ -----------
1 [0xff][0x6],[BL][ext 0x0,0x0] 1 0 0
SQL> @get_resource_name
HEXNAME RESOURCE_NAME KJBLGRANT KJBLROLE KJBLOWNER KJBLMASTER KJBLPKEY KJBLSID KJBLREQUE
----------------------------------- --------------- --------- ---------- ---------- ---------- ---------- ---------- ---------
[0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSERPR 0 1 0 15976 0 KJUSERNL <---KJBLOWNER=1
###inst_id=1表示block主節點是例項1，輸出的兩行分別表示例項1、例項2均以讀的方式訪問block
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSERPR 0
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSERPR 1
###作為主節點的例項1記錄了所有訪問過該block的節點資訊(inst_id=1的兩條記錄，owner_node=0記錄例項1的訪問，owner_node=1記錄了例項2的訪問)，主節點所掌握的資訊在RAC裡稱為master metadata；而例項2只記錄了自己對於block的訪問(inst_id=2的那條記錄)，非主節點掌握的資訊在RAC裡被稱為shadow metadata
SQL> @get_ges_enqueue.sql
INST_ID OWNER_NODE RESOURCE_NAME1 RESOURCE_NAME2 GRANT_LEV REQUEST_L
---------- ---------- ------------------------------ ------------------------------ --------- ---------
1 0 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSERPR KJUSERNL
1 1 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSERPR KJUSERNL
2 1 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSERPR KJUSERNL
*************
* 人工將主節點從例項1調整為例項2，先後在例項2、例項1上發起update操作
*************
---例項2上透過執行oradebug把block 6/255的主節點再次重置為例項2，觀察master metadata是否都轉移到了例項2上
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug lkdebug -m pkey 15976
Statement processed.
###果然例項2對該block的記錄變為了兩條(inst_id=2有兩條)，表明主節點對應的例項上儲存RAC環境裡所有節點對某個block訪問時持有的鎖資訊
SQL> @get_ges_enqueue.sql
INST_ID OWNER_NODE RESOURCE_NAME1 RESOURCE_NAME2 GRANT_LEV REQUEST_L
---------- ---------- ------------------------------ ------------------------------ --------- ---------
1 0 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSERPR KJUSERNL
2 0 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSERPR KJUSERNL
2 1 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSERPR KJUSERNL
---例項2上發起對block的更改，執行get系列指令碼
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- ----------- ---------- ---------------- ---------- ----------
T0820_1 SCUR 0 0000000089F90298 0 0
update scott.t0820_1 set id=id+1;
commit;
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- -------- ---------- ---------------- ---------- ----------
T0820_1 XCUR 0 0000000089F90298 0 0
T0820_1 CR 0 00 1947380 0
SQL> @get_resource_name.sql
HEXNAME RESOURCE_NAME KJBLGRANT KJBLROLE KJBLOWNER KJBLMASTER KJBLPKEY KJBLSID KJBLREQUE
----------------------------------- --------------- --------- ---------- ---------- ---------- ---------- ---------- ---------
[0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX 0 1 1 15976 0 KJUSERNL
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
2 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 1 0 1 KJUSERNL KJUSEREX 1
SQL> @get_ges_enqueue.sql
INST_ID OWNER_NODE RESOURCE_NAME1 RESOURCE_NAME2 GRANT_LEV REQUEST_L
---------- ---------- ------------------------------ ------------------------------ --------- ---------
2 1 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL <---作為主節點的例項2 master metadata資訊裡包含了自己(OWNER_NODE=1)修改block時加的X鎖
---例項1上執行get系列指令碼
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- ----------- ---------- ---------------- ---------- ----------
T0820_1 CR 0 00 1947379 0
SQL> @get_resource_name.sql
no rows selected <---get_resource_name.sql指令碼實質上是關聯x$le、x$kjbl兩張表得出例項對block施加的鎖資訊，沒有輸出是因為例項1的cache裡只有CR型別的buffer，只能給自己使用，不能分享其它節點，所以無需持有任何鎖
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
2 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 1 0 1 KJUSERNL KJUSEREX 1 <---只剩下了例項2 update block時留下的X鎖，例項1先期加在block上的Share鎖已解除
SQL> @get_ges_enqueue.sql
INST_ID OWNER_NODE RESOURCE_NAME1 RESOURCE_NAME2 GRANT_LEV REQUEST_L
---------- ---------- ------------------------------ ------------------------------ --------- ---------
2 1 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL <---作為主節點的例項2 master metadata資訊裡包含了自己(OWNER_NODE=1)修改block時加的X鎖，沒有inst_id=1的記錄說明例項1沒有關於該block的shadow metadata
---例項1上發起對block的更改，執行get系列指令碼
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- -------- ---------- ---------------- ---------- ----------
T0820_1 CR 0 00 1947379 0
update scott.t0820_1 set id=id+1;
commit;
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- -------- ---------- ---------------- ---------- ----------
T0820_1 XCUR 0 0000000089F95818 0 0
T0820_1 CR 0 00 0
T0820_1 CR 0 00 1947379 0
SQL> @get_resource_name.sql
HEXNAME RESOURCE_NAME KJBLGRANT KJBLROLE KJBLOWNER KJBLMASTER KJBLPKEY KJBLSID KJBLREQUE
----------------------------------- --------------- --------- ---------- ---------- ---------- ---------- ---------- ---------
[0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX 0 0 1 15976 0 KJUSERNL <---KJBLOWNER=0表示例項1訪問了block
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
2 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 1 0 1 KJUSERNL KJUSEREX 0 <---OWNER_NODE=0說明之前例項2修改block時加上的X鎖已被釋放，改為記錄本次例項1修改block時的鎖資訊
SQL> @get_ges_enqueue.sql
INST_ID OWNER_NODE RESOURCE_NAME1 RESOURCE_NAME2 GRANT_LEV REQUEST_L
---------- ---------- ------------------------------ ------------------------------ --------- ---------
1 0 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL <---因為最近一次修改block是從例項1發起，所以例項1的GRD裡記錄了OWNER_NODE=0(代表例項1)修改時持有的鎖資訊；例項2之所以會記錄例項1鎖修改時的鎖資訊是因為例項2是master node，必須記錄所有訪問過該block的節點資訊
2 0 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL
---例項2上執行get系列指令碼
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- -------- ---------- ---------------- ---------- ----------
T0820_1 CR 0 00 1950280 0
T0820_1 CR 0 00 1947380 0
SQL> @get_resource_name.sql <---例項1發起更改後，例項2 cache裡只有CR型別的buffer，只能給自己使用，不能分享其它節點，所以無需持有任何鎖(實質是x$bh.le_addr=NULL所以與x$le.le_addr關聯無結果)
no rows selected
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
2 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 1 0 1 KJUSERNL KJUSEREX 0
SQL> @get_ges_enqueue.sql
INST_ID OWNER_NODE RESOURCE_NAME1 RESOURCE_NAME2 GRANT_LEV REQUEST_L
---------- ---------- ------------------------------ ------------------------------ --------- ---------
1 0 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL
2 0 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL
如果此時我們將block的master node再一次指回例項1，那麼get_ges_enqueue.sql僅會顯示inst_id=1的記錄，因為例項2不再是主節點，所以沒必要儲存其它節點的block鎖資訊
*************
* 人工將主節點從例項2調整回例項1，觀察v$ges_enqueue檢視的內容
*************
---例項1執行
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug lkdebug -m pkey 15976
Statement processed.
SQL> @get_ges_enqueue.sql
INST_ID OWNER_NODE RESOURCE_NAME1 RESOURCE_NAME2 GRANT_LEV REQUEST_L
---------- ---------- ------------------------------ ------------------------------ --------- ---------
1 0 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL <---僅有節點1(owner_node=0)的block鎖資訊儲存在節點1(inst_id=1)上
*************
* 重啟資料庫後，執行update但不提交的情況下，block鎖資源查詢
*************
###重啟資料庫
srvctl stop database -d susedb1 -o immediate
srvctl start database -d susedb1
---例項1、例項2分別查詢scott.t0820_1表
select * from scott.t0820_1;
---例項1查詢block 6/255資源上的鎖持有情況，此時例項1是主節點，例項1、例項2都對block持有共享讀模式的鎖
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSERPR 0
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSERPR 1
---例項1執行update後不提交，緊接著執行get系列指令碼
update scott.t0820_1 set id=id+1;
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- -------- ---------- ---------------- ---------- ----------
T0820_1 XCUR 0 0000000089F90C18 0 0
T0820_1 CR 0 00 2015904 0
SQL> @get_resource_name.sql
HEXNAME RESOURCE_NAME KJBLGRANT KJBLROLE KJBLOWNER KJBLMASTER KJBLPKEY KJBLSID KJBLREQUE
----------------------------------- --------------- --------- ---------- ---------- ---------- ---------- ---------- ---------
[0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX 0 0 0 15976 0 KJUSERNL
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSEREX 0
SQL> @get_ges_enqueue.sql
INST_ID OWNER_NODE RESOURCE_NAME1 RESOURCE_NAME2 GRANT_LEV REQUEST_L
---------- ---------- ------------------------------ ------------------------------ --------- ---------
1 0 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL
---例項2執行get系列指令碼檢視資源狀態資訊
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- ----------- ---------- ---------------- ---------- ----------
T0820_1 CR 0 00 2015903 0
SQL> @get_resource_name.sql
no rows selected <---原有的KJUSERPR鎖解除
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSEREX 0
SQL> @get_ges_enqueue.sql
INST_ID OWNER_NODE RESOURCE_NAME1 RESOURCE_NAME2 GRANT_LEV REQUEST_L
---------- ---------- ------------------------------ ------------------------------ --------- ---------
1 0 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL
---例項2 update同一條記錄，
update scott.t0820_1 set id=id+1;
<----處於等待
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- ----------- ---------- ---------------- ---------- ----------
T0820_1 XCUR 0 0000000089F96198 0 0
T0820_1 CR 1 00 2016429 0
T0820_1 CR 0 00 2015903 0
SQL> @get_resource_name.sql
HEXNAME RESOURCE_NAME KJBLGRANT KJBLROLE KJBLOWNER KJBLMASTER KJBLPKEY KJBLSID KJBLREQUE
----------------------------------- --------------- --------- ---------- ---------- ---------- ---------- ---------- ---------
[0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX 64 1 0 15976 0 KJUSERNL
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSERNL 0 <---如果例項1的buffer cache裡有pi型別的block在，才會有這條記錄
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSEREX 1 <---雖然例項2的會話因為鎖而處於等待，但GRANT_LEVEL還是顯示為KJUSEREX，從GC檢視看不出行級鎖的存在
SQL> @get_ges_enqueue.sql
INST_ID OWNER_NODE RESOURCE_NAME1 RESOURCE_NAME2 GRANT_LEV REQUEST_L
---------- ---------- ------------------------------ ------------------------------ --------- ---------
1 0 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSERNL KJUSERNL
1 1 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL
2 1 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL <---雖然例項2的會話因為鎖而處於等待，但GRANT_LEVEL還是顯示為KJUSEREX
---例項1執行get系列指令碼
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- -------- ---------- ---------------- ---------- ----------
T0820_1 CR 0 00 2016429 0
T0820_1 CR 0 00 2016430 0
T0820_1 CR 0 00 2015904 0
SQL> @get_resource_name.sql
no rows selected <---因為例項1的buffer裡都是CR型別的block，所以這裡必然返回0條記錄
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSEREX 1 <---例項1的buffer cache裡有pi型別的block因為checkpoint發生變成CR了，所以相比上次在例項2的執行，結果裡少了OWNER_NODE=1的記錄
SQL> @get_ges_enqueue.sql
INST_ID OWNER_NODE RESOURCE_NAME1 RESOURCE_NAME2 GRANT_LEV REQUEST_L
---------- ---------- ------------------------------ ------------------------------ --------- ---------
1 1 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL <---相比上次在例項2執行的輸出少了owner_node=0、GRANT_LEVEL/REQUEST_LEVEL=KJUSERNL的記錄，因為例項1上的pi block變成了CR
2 1 [0xff][0x6],[BL][ext 0x0,0x0] 255,6,BL KJUSEREX KJUSERNL
*************
* 例項1回滾後，block資源使用情況觀察
*************
---例項1 執行rollback回滾剛才的更改
SQL> update scott.t0820_1 set id=id+1;
1 row updated.
SQL> rollback;
Rollback complete.
SQL> @get_buffer_stat.sql <---回滾後可看到PI出現
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- ----------- ---------- ---------------- ---------- ----------
T0820_1 PI 0 0000000089F90C18 0 0
T0820_1 CR 0 00 2016429 0
T0820_1 CR 0 00 2016430 0
T0820_1 CR 0 00 2015904 0
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSERNL 0 <---PI block保留有KJUSERNL鎖，這點與CR block不同，後者沒有任何鎖
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSEREX 1
SQL> alter system checkpoint;
System altered.
SQL> @get_buffer_stat.sql
OBJECT_NAM STATE MODE_HELD LE_ADDR CR_SCN_BAS CR_SCN_WRP
---------- -------- ---------- ---------------- ---------- ----------
T0820_1 CR 0 00 2020380 0 <---checkpoint發生後PI變成了CR
T0820_1 CR 0 00 2016429 0
T0820_1 CR 0 00 2016430 0
T0820_1 CR 0 00 2015904 0
SQL> @get_resource_stat.sql
INST_ID RESOURCE_NAME STATE MAST CNVT GRNT REQUEST_L GRANT_LEV OWNER_NODE
---------- ---------------------------------- -------- ----- ----- ----- --------- --------- ----------
1 [0xff][0x6],[BL][ext 0x0,0x0] GRANTED 0 0 1 KJUSERNL KJUSEREX 1 <---REQUEST_LEVEL/GRANT_LEVEL均為KJUSERNL的記錄消失
總結:
1、當多個節點在自己的buffer cache裡擁有同一block的scur buffer時，他們對block持有的KJUSERPR型別的鎖資訊可以並存於GRD
2、當某節點修改了block，該block xcur型別的buffer出現在執行修改操作節點的buffer cache，其餘節點只會有PI或CR型別的buffer，對於每個PI類的buffer GRD裡各保留一行REQUEST_LEVEL/GRANT_LEVEL均為KJUSERNL的記錄；對於CR類的buffer GRD裡不保留任何鎖資訊
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/53956/viewspace-2124814/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【RAC原理】Cache Fusion
- RAC的cache fusion對資料塊訪問效率的影響
- 快取融合（Cache Fusion）介紹快取
- Oracle RAC Cache Fusion 系列九：Oracle RAC 分散式資源管理（二）Oracle分散式
- Oracle RAC Cache Fusion 系列八：Oracle RAC 分散式資源管理（一）Oracle分散式
- Oracle RAC Cache Fusion 系列十七：Oracle RAC DRMOracle
- Oracle RAC Cache Fusion 系列一：基礎概念Oracle
- Oracle RAC 和OPS 區別 - Cache FusionOracle
- VMware Fusion 埠對映
- Oracle RAC Cache Fusion 系列十三：PCM資源訪問Oracle
- Oracle RAC Cache Fusion系列十八：Oracle RAC Statisticsand Wait EventsOracleAI
- Oracle RAC Cache Fusion 系列十四：Oracle RAC CR Server Part OneOracleServer
- Oracle RAC Cache Fusion 系列十：Oracle RAC Enqueues And Lock Part 1OracleENQ
- DB_BLOCK_SIZE and DB_CACHE_SIZE in OracleBloCOracle
- Block學習⑤--block對物件變數的捕獲BloC物件變數
- web應用服務端cache策略初探Web服務端
- iOS - 對 block 實現的探究iOSBloC
- block沒那麼難（三）：block和物件的記憶體管理BloC物件記憶體
- mysql 的myisam和innodb對應的cache管理方式的一點差異MySql
- block沒那麼難（二）：block和變數的記憶體管理BloC變數記憶體
- ORACLE鎖的管理Oracle
- 【BLOCK】Oracle 塊管理常用SQLBloCOracleSQL
- E: 無法獲得鎖 /var/cache/apt/archives/lock - open (11: 資源暫時不可用) E: 無法對目錄 /var/cache/apt/archives/ 加鎖APTHive
- block 對外部引用變數的處理BloC變數
- 解鎖快取新姿勢——更靈活的 Cache快取
- SAP 客供料管理初探
- How To Identify a Hot Block Within The Database Buffer Cache. [ID 163424.1]IDEBloCDatabase
- ORACLE鎖的管理(zt)Oracle
- oracle鎖的管理（轉）Oracle
- 網路回撥：Block和Delegate的對比BloC
- 專案管理過程中的知識管理初探（轉）專案管理
- 透過Buffer cache瞭解data block在DML操作下的狀態演變BloC
- 初探dhtmlxScheduler日程管理框架HTML框架
- 軟體測試管理初探
- Vmware fusion 命令列管理虛擬機器便捷工具命令列虛擬機
- 對 Python Socket 程式設計的初探Python程式設計
- 對TM鎖和TX鎖的簡單理解
- RAC 鎖管理與鎖問題的定位(二）