12.02.05

enqueue 等待事件探究

Posted in Oracle优化, Brotherxiao's at 12:29 pm by bachelor

Enqueues are another type of locking mechanism used in Oracle.
An enqueue is a more sophisticated mechanism which permits several concurrent
processes to have varying degree of sharing of “known” resources. Any object
which can be concurrently used, can be protected with enqueues. A good example
is of locks on tables. We allow varying levels of sharing on tables e.g.
two processes can lock a table in share mode or in share update mode etc.
One difference is that the enqueue is obtained using an OS specific
locking mechanism. An enqueue allows the user to store a value in the lock,
i.e the mode in which we are requesting it. The OS lock manager keeps track
of the resources locked. If a process cannot be granted the lock because it
is incompatible with the mode requested and the lock is requested with wait,
the OS puts the requesting process on a wait queue which is serviced in FIFO.
Another difference between latches and enqueues is that
in latches there is no ordered queue of waiters like in enqueues. Latch
waiters may either use timers to wakeup and retry or spin (only in
multiprocessors). Since all waiters are concurrently retrying (depending on
the scheduler), anyone might get the latch and conceivably the first one to
try might be the last one to get.
x$ksqst(8i)

1、查看锁类型（以TX为例）:
select chr(bitand(p1,-16777216)/16777215)||
chr(bitand(p1,16711680)/65535) “Lock”
from dual;
2、查看锁对象
select lock_wait from v$session where sid=:sid;
3、查看阻塞进程
select sid from v$lock
where id1 in (select id1 from v$lock where kaddr = ‘:LOCK_WAIT’)
and request = 0

常见的enqueue类型
(1)ST enqueue，用于空间管理和字典管理的表空间(DMT)的区间分配，在DMT中典型的是对于uet$和fet$数据字典表的 争用。对于支持LMT的
版本，应该尽量使用本地管理表空间. 或者考虑手工预分配一定数量的区(Extent)，减少动态扩展
时发生的严重队列竞争。
(2)HW enqueue指和段的高水位标记相关等待；手动分配适当区可以避免这一等待。

(3)TX是最常见的enqueue等待。TX enqueue等待通常是以下三个问题之一产生的结果。
第一个问题是唯一索引中的重复索引，你需要执行提交（commit）/回滚（rollback）操作来释放enqueue。
第二个问题是对同一位图索引段的多次更新。因为单个位图段可能包含多个行地址（rowid），所以当多个用户试图更新同一段时，可能一个
用户会锁定其他用户请求的记录，这时等待出现。直到获得锁定的用户提交或回滚， enqueue释放。
第三个问题，也是最可能发生的问题是多个用户同时更新同一个块。如果没有足够的ITL槽，就会发生块级锁定。通过增大initrans和/或
maxtrans以允许使用多个ITL槽（对于频繁并发进行DML操作的数据表，在建表之初就应该考虑为相应参数设置合理的数值，避免系统运行
以后在线的更改，在8i之前，freelists等参数不能在线更改，设计时的考虑就尤为重要），或者增大表上的pctfree值，就可以很容易的避免
这种情况。
(4)TM enqueue队列锁在进行DML操作前获得，以阻止对正在操作的数据表进行任何DDL操作(在DML操作一个数据表时，其结构不能被更改)。

enqueue类型

BL, Buffer Cache Management
CF, Controlfile Transaction

CI, Cross-instance Call Invocation
CU, Bind Enqueue

DF, Datafile
DL, Direct Loader Index Creation

DM, Database Mount
DR, Distributed Recovery

DX, Distributed TX
FS, File Set

IN, Instance Number
IR, Instance Recovery

IS, Instance State
IV, Library Cache Invalidation

JQ, Job Queue
KK, Redo Log “Kick”

L[A-P], Library Cache Lock
MR, Media Recovery

N[A-Z], Library Cache Pin
PF, Password File

PI, Parallel Slaves
PR, Process Startup

PS, Parallel Slave Synchronization
Q[A-Z], Row Cache

RT, Redo Thread
SC, System Commit Number

SM, SMON
SQ, Sequence Number Enqueue

SR, Synchronized Replication
SS, Sort Segment

ST, Space Management Transaction
SV, Sequence Number Value

TA, Transaction Recovery
TM, DML Enqueue

TS, Temporary Segment (also TableSpace)
TT, Temporary Table

TX, Transaction
UL, User-defined Locks

UN, User Name
US, Undo Segment, Serialization

WL, Being Written Redo Log
XA, Instance Attribute Lock

XI, Instance Registration Lock———

Permalink