触发light work rule的一种情况

来源：互联网发布：mysql 删除多列编辑：程序博客网时间：2024/04/29 16:22

The lightwork rule is invoked when CR construction involves too much work and no
current block or PI block is available in the cache for block cleanouts. Any additional disk

I/O to serve the CR block will kick the lightwork rule.

表my_test所在block 1/23146 该资源（BL）的master node为instance B。

从以下语句可以查到该资源的master node:

select * from gv$dlm_ress where resource_name like '%5a6a%BL%';

场景1 ：instance A 上修改表 update my_test set name=name where id=1;

alter systsem flush buffer_cache;

//查看v$cr_block_server里的light_works

Instance B上select * from my_test; //对其进行10046 trace

trace 文件如下：

PARSING IN CURSOR #2 len=21 dep=0 uid=0 oct=3 lid=0 tim=1410754418661475 hv=42
15432227 ad='7efae218'
select * from my_test
END OF STMT
PARSE #2:c=0,e=7419,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1410754418661472
EXEC #2:c=0,e=6469,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1410754418673271
WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1
p3=0 obj#=0 tim=1410754418673305
WAIT #2: nam='db file sequential read' ela= 3705 file#=1 block#=23145 blocks=1 //由于是资源master node，所以没有授权的等待
obj#=9917 tim=1410754418677187
WAIT #2: nam='db file sequential read' ela= 536 file#=1 block#=23146 blocks=1 obj#=9917 tim=1410754418677870 //由于是资源master node，所以没有授权的等待
WAIT #2: nam='gc cr block 2-way' ela= 338 p1=2 p2=105 p3=29 obj#=0 tim=1410754418678404 //undo segment header
WAIT #2: nam='gc cr disk read' ela= 172 p1=2 p2=175 p3=30 obj#=0 tim=1410754418678767 //由于该block1/23146 有修改，需要instance A 上的undo(master node是 instance A)做cr block。由于该undo block不在instance A的buffer中，所以该事件是授权instance B直接去disk 读取。此时instance A会触发 light works

WAIT #2: nam='db file sequential read' ela= 1927 file#=2 block#=175 blocks=1 obj#=0 tim=1410754418680720//从disk 读取ubdo block
FETCH #2:c=1000,e=7456,p=3,cr=5,cu=0,mis=0,r=1,dep=0,og=1,tim=1410754418680805
WAIT #2: nam='SQL*Net message from client' ela= 29558 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1410754418710408
WAIT #2: nam='SQL*Net message to client' ela= 0 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1410754418710467
FETCH #2:c=0,e=28,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=1410754418710486
*** 2015-10-12 09:15:31.348
WAIT #2: nam='SQL*Net message from client' ela= 6429515 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1410754425145184
STAT #2 id=1 cnt=2 pid=0 pos=1 obj=9917 op='TABLE ACCESS FULL MY_TEST (cr=6 pr=3 pw=0 time=7447 us)'

场景1 ：instance B上修改表 update my_test set name=name where id=1;

alter systsem flush buffer_cache;

//查看v$cr_block_server里的light_works

Instance A 上select * from my_test; //对其进行10046 trace

PARSING IN CURSOR #1 len=21 dep=0 uid=0 oct=3 lid=0 tim=1410755503651449 hv=4215432227 ad='7eedf5b8'
select * from my_test
END OF STMT
PARSE #1:c=999,e=1533,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1410755503651446
EXEC #1:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1410755503651545
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=9917 tim=1410755503651572
WAIT #1: nam='gc cr grant 2-way' ela= 195 p1=1 p2=23145 p3=4 obj#=9917 tim=1410755503652020 // 由于资源master node在instance B且在任何instance buffer cache

//里找不到该block，所以授权直接从disk 读取

WAIT #1: nam='db file sequential read' ela= 354 file#=1 block#=23145 blocks=1 obj#=9917 tim=1410755503652410
WAIT #1: nam='gc cr grant 2-way' ela= 158 p1=1 p2=23146 p3=1 obj#=9917 tim=1410755503652723 // 由于资源master node在instance B且在任何instance buffer cache

//里找不到该block，所以授权直接从disk 读取

WAIT #1: nam='db file sequential read' ela= 187 file#=1 block#=23146 blocks=1 obj#=9917 tim=1410755503652934
WAIT #1: nam='gc cr block 2-way' ela= 293 p1=4 p2=137 p3=53 obj#=0 tim=1410755503653383 //请求undo segment header，并接收到；此处从disk读取到my_test的block需要undo 去构建cr block

“gc current grant 2-way” – Indicates that no current block was received because it was not cached in any instance. Instead a global grant was given, enabling the requesting instance to read the block from disk or modify it.

WAIT #1: nam='gc current grant 2-way' ela= 145 p1=1 p2=23146 p3=33619969 obj#=9917 tim=1410755503653609//该事件得到授权修改current block

WAIT #1: nam='gc cr disk read' ela= 175 p1=4 p2=10900 p3=54 obj#=0 tim=1410755503653940 //得到授权读取undo block ，此时触发了instanceB的light works
WAIT #1: nam='db file sequential read' ela= 8112 file#=4 block#=10900 blocks=1 obj#=0 tim=1410755503662077 //读取undo block
FETCH #1:c=1000,e=10503,p=3,cr=5,cu=0,mis=0,r=1,dep=0,og=1,tim=1410755503662183
WAIT #1: nam='SQL*Net message from client' ela= 278 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1410755503662497
WAIT #1: nam='SQL*Net message to client' ela= 0 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1410755503662533
FETCH #1:c=0,e=25,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=1410755503662549
*** 2015-10-12 09:34:11.202
WAIT #1: nam='SQL*Net message from client' ela= 15089808 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1410755518752397
STAT #1 id=1 cnt=2 pid=0 pos=1 obj=9917 op='TABLE ACCESS FULL MY_TEST (cr=6 pr=3 pw=0 time=10483 us)'
=====================
PARSING IN CURSOR #1 len=55 dep=0 uid=0 oct=42 lid=0 tim=1410755518752653 hv=2217940283 ad='0'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #1:c=0,e=37,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1410755518752651
EXEC #1:c=0,e=47,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1410755518752741

除了以上场景，block cleanout也会触发light work。可参考链接：

http://blog.csdn.net/loryliu/article/details/49070979

场景1 ：instance A 上修改表 update my_test set name=name where id=1;

alter systsem flush buffer_cache;

//查看v$cr_block_server里的light_works

Instance B上select * from my_test; //对其进行10046 trace

0 0