Oracle Enqueues Wait Events 一

Oracle Enqueue Names

Enquences 是为了管理 连续访问数据库资源而设计的一种共享内存结构也叫锁。它们可以与会话或事务相关联。Enquences 名显示在DBA_LOCK和DBA_LOCK_INTERNAL数据字典视图的LOCK_TYPE列中。

资源被唯一地标识一个对象，该对象可以被一个实例(本地资源)或多个实例之间(全局资源)的不同会话锁定。每个想试图锁定资源的会话都在该资源上生成一个队列。

下面是Oracle Enqueues对资源申请的锁模式和申请资源

Enqueues are usually represented in the format "TYPE-ID1-ID2" where:
"TYPE" is a 2 character text string"ID1" is a 4 byte hexadecimal number
"ID2" is a 4 byte hexadecimal number Parameters:
P1 = Lock Type & Mode
P2 = Lock ID1
P3 = Lock ID2
Lock Type & Mode
The lock type and requested mode are encoded into P1 thus:
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:
Eg: 54580006
^^^^------ Converted to ASCII gives "TX" (0x54 = "T", 0x58 = "X")
^^^^-- 0006 is 6 in decimal so this is a mode 6 request

Note that on UNIX, the command 'man ascii' will display the Octal, hexadecimal, and decimal ASCII character sets to help with the hex translation.

An alternative way to extract this information is to use SQL thus:
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'
;
Lock ID1
P2 represents ID1 of the enqueue name in decimal.
P2RAW represents ID1 of the enqueue name in hexadecimal.
Lock ID2
P3 represents ID2 of the enqueue name in decimal.
P3RAW represents ID2 of the enqueue name in hexadecimal.