Enqueues are sophisticated locks for managing access to shared resources
(like tables, rows, jobs, redo threads). An enqueue can be requested in
different levels/mode: null, row share, row exclusive, share, share row
exclusive or exclusive. If a session holds an enqueue in share mode, other
sessions can then also take the enqueue in share mode (for the same resource).
If a session holds an enqueue in exclusive mode, other sessions that wants to
get it - independently in which level - they have to wait.
等待事件：enqueue是串行访问不同资源的本地锁。这种等待事件说明等待一个正被另一个session持有的排它模式或请求模式锁。
enqueue通常描述成这种格式:TYPE-ID1-ID2
"TYPE" is a 2 character text string
"ID1" is a 4 byte hexadecimal number
"ID2" is a 4 byte hexadecimal number
Convert P1 into hexadecimal (or use P1RAW) and extract the lock type and requested mode from this by converting the
first 2 bytes of the hexadecimal number to ASCII and the second 2 bytes to a number:
SELECT chr(to_char(bitand(p1,-16777216))/16777215)||
chr(to_char(bitand(p1, 16711680))/65535) "Lock",
to_char( bitand(p1, 65535) ) "Mode"
FROM v$session_wait
WHERE event = 'enqueue';
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request;
查看等待时间最长的enqueue的等待事件
SELECT eq_type "Lock",
total_req# "Gets",
total_wait# "Waits",
cum_wait_time
FROM V$enqueue_stat
WHERE Total_wait# > 0
order by 4 desc;
记录一个enqueue等待事件的case,原因是由于前一次刷新被异常终止,后台线程没有被释放造成的．
锁类型:JI是物化视图刷新时产生的一个种锁.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL> SELECT chr(to_char(bitand(p1,-16777216))/16777215)||
2 chr(to_char(bitand(p1, 16711680))/65535) "Lock",
3 to_char( bitand(p1, 65535) ) "Mode"
4 FROM v$session_wait
5 WHERE event = 'enqueue';
Lo Mode
-- ----------------------------------------
JI 6
SQL> SELECT * FROM v$lock WHERE request > 0;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- ---------- ----------
CTIME BLOCK
---------- ----------
44D05F70 44D05F80 218 JI 7895 0 0 6
3 0
SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
2 id1, id2, lmode, request, type
3 FROM V$LOCK
4 WHERE (id1, id2, type) IN
5 (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
6 ORDER BY id1, request;
SESS ID1 ID2
------------------------------------------------ ---------- ----------
LMODE REQUEST TY
---------- ---------- --
Holder: 166 7895 0
6 0 JI
Waiter: 218 7895 0
0 6 JI
SQL> SELECT eq_type "Lock",
2 total_req# "Gets",
3 total_wait# "Waits",
4 cum_wait_time
5 FROM V$enqueue_stat
6 WHERE Total_wait# > 0
7 order by 4 desc;
Lo Gets Waits CUM_WAIT_TIME
-- ---------- ---------- -------------
JI 53066 22865 117531891
TM 8337334 8 1105389
CF 927241 387 68069
TX 10480779 1225 59699
TC 320 64 32454
CI 154944 31 6659
SQ 434743 659 766
US 6153 6 156
HW 191455 2 16
CU 136672 4 0
IA 2 1 0
Lo Gets Waits CUM_WAIT_TIME
-- ---------- ---------- -------------
PS 50 1 0
JD 374791 2 0
13 rows selected.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
阅读(1026) | 评论(0) | 转发(0) |
