本文以双节点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 @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 @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 @get_resource_name
no rows selected @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 @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 @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_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 @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 @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 @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 @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 @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 @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 @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 @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;
@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 @get_resource_name.sql
no rows selected @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 @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 update scott.t0820_1 set id=id+1;
1 row updated.
SQL> rollback;
Rollback complete.
SQL> @get_buffer_stat.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 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 @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
当前位置: 数据库>oracle
初探Cache Fusion对block的锁管理
来源: 互联网 发布时间：2017-06-24
本文导语: 本文以双节点RAC为例，揭示了在执行select及DML操作过程中，Cache Fusion在幕后是如何对block进行锁管理的。###实例1上查询scott.t0820_1表select * from scott.t0820_1; ID---------- 2select dbms_rowid.rowid_relative_fno(rowid) rfno,dbms_rowid.r...
您可能感兴趣的文章:
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术，将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。
站内导航:
特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除！