如何进行Enqueue的整理

如何进行Enqueue的整理，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。

当处理enqueue等待时,熟记这些要点:

1 Enqueues 是应用到数据库对象的locks.与latch(应用于SGA内存)不同.

2 Enqueues 是由应用程序发起,具有事务性.

3 Oracle session 正在等候获取一个特定的enqueue. enqueue的名字和mode被记录在P1参数里.针对不同类型的enqueue竞争应采用不同的方式解决.

4 一直到Oracle 9i,enqueue wait event 代表所有enqueue waits；

从Oracle 10g开始enqueue waits被分类定义(下文详细介绍).

What Is an Enqueue?

一个enqueue是什么由具体环境决定. 如果它被看成是动词,则表示将一个lock request置于一个队列的动作.如果被看成是名词,它表示一个特定的lock,比如TX的enqueue.

Enqueues 是一种非常精密的锁定机制,用来管理访问数据库共享资源,比如(objects, background jobs, and redo threads).Oracle使用enqueues出于两个目的: 第一,当enqueue为不兼容模式(mode)时,enqueues防止并发sessions共享数据库资源. 第二,当enqueue为兼容模式时,enqueues容许并发sessions共享数据库资源.

当session A请求一个数据库对象上的锁资源时,如果被请求的锁模式为不兼容模式,且该数据库对象已经被另一个session 以不兼容模式锁持有,则session A将它的锁请求放置于一个队列里并且按顺序等待被唤起（防止活锁）.这个事件被称为enqueue wait.

Enqueue waits 除了包括buffer locks (discussed in the "Buffer Busy Waits" section), library cache locks, row cache locks, and PCM (Parallel Cache Management) locks,也是对各种local locks的等待.

What Is an Enqueue Resource?

An enqueue resource是由于受到一个enqueue lock的数据库资源.

Oracle通过内部数组结构进行管理.

通过X$KSQRS (kernel service enqueue resource) or V$RESOURCE 视图可以看到具体条目.

SQL> select * from v$resource;

ADDR TY ID1 ID2

-------- -- ---------- ----------

6D304434 XR 4 0

6D304594 CF 0 0

6D3046F4 RT 1 0

6D30474C RS 25 1

6D304904 MR 3 0

6D30495C MR 4 0

6D3049B4 MR 5 0

6D304B24 MR 1 0

6D304B7C MR 2 0

6D304BD4 MR 201 0

6D305634 TA 6 1

ADDR TY ID1 ID2

-------- -- ---------- ----------

6D305B5C PW 1 0

6D3069DC TS 3 1

已选择13行。

SQL>

根据以上查询输出,我们可以看到enqueue resource structure 由lock type和两个参数组成. Oracle用两个字符符号例如(TX, TM, TS, MR, RT, etc)表示lock type. ID1,ID2两个参数由于lock type的不同所以具有不同含义.

具体可以参见<>第六章 Enqueue部分 Table6-3.

在Oracle10g之前,ID1和ID2对于不同类型lock的含义并未公开.

在Oracle10g中可以通过如下查询轻松得到.

col name for a20

col parameter2 for a20

col parameter3 for a20

select name,parameter2,parameter3 from v$event_name

/

稍后在本文enqueue资源争用部分再对ID1与ID2作详细介绍.

可以被lock manager并行锁的enqueue resources的最大数量由ENQUEUE_RESOURCES初始化参数控制.该参数默认值通常来讲已经足够,如果在应用程序中使用并行DML操作,可以考虑增加ENQUEUE_RESOURCES的值.

关于ENQUEUE_RESOURCES [10..65535]

At database startup time, Oracle allocates the number of enqueues specified by the ENQUEUE_RESOURCES parameter. The default value of ENQUEUE_RESOURCES is derived from the SESSIONS parameter and is usually adequate, as long as its value is greater than DML_LOCKS + 20. For three or fewer sessions, the default value is the number of database files + 20. For 4 to 10 sessions, the default value is the number of database files + ((SESSIONS - 3) * 5) + 20. For more than 10 sessions, it is the number of database files + ((SESSIONS - 10) * 2) + 55.

If you explicitly set ENQUEUE_RESOURCES to a value higher than DML_LOCKS + 20, then Oracle uses the value you provide. Oracle will automatically allocate additional enqueues from the shared pool as needed if the number specified by ENQUEUE_RESOURCES is exceeded. You can check resource usage by querying V$RESOURCE_LIMIT.

不能获取an enqueue resource 的进程会报ORA-00052: "maximum number of enqueue resources exceeded" error.

V$RESOURCE_LIMIT视图提供系统资源使用的信息.通过这个视图可以监控很多数据库资源(resources, locks, or processes)的消耗情况.

注意一下这个视图的几个字段:

MAX_UTILIZATION 表示实例启动后该类资源的最大消耗值

以下监控与enqueue有关的资源消耗情况:

col name for a18

col limit_usage for a15

select RESOURCE_NAME name,

CURRENT_UTILIZATION cur_usage,

MAX_UTILIZATION max_usage,

LIMIT_VALUE limit_usage,

INITIAL_ALLOCATION init_allo

from v$resource_limit

where resource_name in ('enqueue_resources','enqueue_locks',

'dml_locks','processes','sessions')

/

NAME CUR_USAGE MAX_USAGE LIMIT_USAGE INIT_ALLO

------------------ ---------- ---------- --------------- -----------

processes 19 24 90 90

sessions 23 30 104 104

enqueue_locks 13 24 1480 1480

enqueue_resources 13 13 UNLIMITED 676

dml_locks 0 7 UNLIMITED 456

What Is an Enqueue Lock?

An enqueue lock 是lock本身.Oracle用一个单独的数组管理.可以通过X$KSQEQ (kernel service enqueue object) 或者 V$ENQUEUE_LOCK 试图察看.隐含参数_ENQUEUE_LOCKS影响这个数组的大小.

Oracle使用不同的结构管理TX and TM 队列.

X$KTCXB (kernel transaction control transaction object-the base view for V$TRANSACTION_ENQUEUE) and X$KTADM (kernel transaction access definition dml lock). TRANSACTIONS and DML_LOCKS 初始化参数会决定管理TX and TM 队列的结构大小.

当然通过查询V$LOCK可以获得所有数据库locks的信息.也是我们诊断enqueue争用的一个重要视图.

Enqueue Architecture

在数据库内部,enqueue architecture 和 sga中cache buffers architecture十分近似. (读过eygle <>这本书的朋友可以参照学习enqueue architecture)

enqueue architecture 的最主要组件由enqueue hash chains latches, enqueue hash table, enqueue hash chains, and enqueue resources组成.

它们之间的数量关系:

enqueue hash chains latch (1:m) a hash bucket(1:1) a enqueue hash chain .

（意思也就是说一个hash chains latch可以管理多个hash bucket，而每一个hash bucket都只包含着一个hash chain）

子enqueue hash chains latches 保护enqueue hash table and hash chains.默认状态下enqueue hash chains latches的个数等于CPU_COUNT,这个数量可以通过隐含参数_ENQUEUE_HASH_CHAIN_LATCHES调整.

根据resource type and identifiers v$resource(TY,ID1,ID2) Enqueue resources 被hash到enqueue hash table 并且被置于相应enqueue hash chains.要使用某个enqueue resource必须获取相应enqueue hash chain. enqueue hash table 的大小来源于SESSIONS初始化参数,也可以通过设置_ENQUEUE_HASH设置（375个）.

If you ever need to increase the ENQUEUE_RESOURCES parameter significantly from its default value, you might want to keep an eye on the sleep rate of the enqueue hash chains latches. This is because the enqueue hash table length will remain the same because it is derived from SESSIONS, not from ENQUEUE_RESOURCES. The combination of a high demand for enqueue resources and a small enqueue hash table will result in a higher hash collision rate and potentially lengthy hash chains. This problem manifests itself as latch contentions for the enqueue hash chains latches. In this case, you need to increase the _ENQUEUE_HASH.

enqueue hash table length = ((SESSIONS - 10) * 2) + 55

通过dump enqueue 结构进一步学习:

alter session set events 'immediate trace name enqueues level 3';

Decoding Enqueue Type and Mode

Oracle 通过对P1列的解码,我们可以得知enqueue type 和 mode.

select sid, event, p1, p1raw,

chr(bitand(P1,-16777216)/16777215)||chr(bitand(P1,16711680)/65535) type,

mod(P1, 16) "MODE"

from v$session_wait

where event ='enqueue'

/

另一种方法.通过v$session_wait(p1raw)列也可以.

The values from the above example are from a 64-bit Oracle Database. You can ignore the leading zeros and focus on the last 4 bytes (that is, the last eight numbers). The high order 2 bytes give the enqueue type. Using 54580006 as an example, the 2 high order bytes are 0x5458Hex. Now, 54Hex is decimal 84 and 58Hex is decimal 88, so the enqueue type can be discovered as follows: (Appendix B has a complete list of enqueue types.)

select chr(84) || chr(88) from dual;CH--TX

Oracle 10g后enqueue wait event name 可以通过v$session_wait(event)直接获得.

Processes request enqueue locks in one of these six modes: Null (N), Row Share (RS), Row Exclusive (RX), Share (S), Share Row Exclusive (SRX), or Exclusive (X)

这部分可参见Oracle相关文档.

Common Causes, Diagnosis, and Actions

由于enqueue type种类繁多,产生an enqueue wait event的原因也不同.我们可以根据多个session正在竞争的enqueue的type和mode找到不同的解决方法.

对于每一种type的enqueue,Oracle内部通过X$KSQST 维护一条相应的记录.在Oracle9i中,通过V$ENQUEUE_STAT视图, 可以具体获得不同type enqueue的请求统计信息(实例启动后的统计值).

-- Oracle 7.1.6 to 8.1.7

select inst_id,

ksqsttyp inst_lock,

ksqstget inst_Gets,

ksqstwat inst_Waits

from x$ksqst

where ksqstwat > 0

order by inst_id,ksqstwat

/

-- Oracle9i Database and above

select *

from v$enqueue_stat

where cum_wait_time > 0

order by inst_id, cum_wait_time

/

下面就最常见的一些enqueue等待事件进行讨论.

Wait for TX Enqueue in Mode 6

对于 TX enqueue in mode 6 的等待是最常见的enqueue wait

(In Oracle Database 10g, the wait event name is enq: TX-row lock contention.) .当一个事物试图update 或 delete 一行或多行已经被另一个事物锁定的记录时这个等待事件发生.通常这是由应用程序引起的.

eg.

在Session A中:

update a set object_id=0 where object_id=11;

与此同时在Session B中执行相同的语句,Session B is hung:

update a set object_id=0 where object_id=11;

Session B一直会等待session A 提交或回滚此事物.

没有其他方法可以释放Session A持有的row exclusive lock(Kill session A会引起事物被pmon回滚并释放相应lock资源)

The following listing shows an example of TX enqueue wait in mode 6 as seen in the V$LOCK view:

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- --- -- ------ ------ ----- ------- ----- ----- A3950688 A395069C 10 TM 188154 0 3 0 3 0

A304E2A0 A304E2B0 10 TX 65585 147836 0 6 3 0

01AD23D4 01AD24A4 20 TX 65585 147836 6 0 10 1A3950A28 A3950A3C 20 TM 188154 0 3 0 10 0

每当你发现TX enqueue等待事件时,首要任务是找出哪个session是锁阻塞者(session A),哪些session是锁等待者(session B),在一个繁忙的系统中,很科能伴有多个锁等待者.

使用以下sql定位即可:

/*

BLOCKER_SID: 锁持有者sid(阻塞sid)

WAITER_SID: 等待者sid

MODE_HELD: 锁持有模式

REQUEST_MODE: 请求模式

*/

select /*+ ordered */ a.sid

blocker_sid,

a.username

blocker_username,

a.serial#,

a.logon_time,

b.type,

b.lmode mode_held,

b.ctime time_held,

c.sid waiter_sid,

c.request request_mode,

c.ctime time_waited

from v$lock b,

v$enqueue_lock c,

v$session a

where a.sid = b.sid

and b.id1 = c.id1(+)

and b.id2 = c.id2(+)

and c.type(+) = 'TX'

and b.type ='TX'

and b.block = 1

order by time_held, time_waited

/

你也可通过以下查询定位,哪些数据库资源正在被征用.

select c.sid waiter_sid,

a.object_name,

a.object_type

from dba_objects a,

v$session b,

v$session_wait c

where ( a.object_id = b.row_wait_obj# or a.data_object_id = b.row_wait_obj#)

and b.sid = c.sid

and chr(bitand(c.P1,-16777216)/16777215) || chr(bitand(c.P1,16711680)/65535) = 'TX'

and c.event like 'enq%'

/

当发生TX Enqueue in Mode 6等待事件时

获取到waiting session 和 blocking session正在执行的sql语句十分重要.我们可以通过这个sql语句定位应用程序可能出现问题(1 很有可能应用程序没有commit 2 没有及时commit 或 rollback DML操作 这就需要结合应用需求而调整了)的大致范围.

Wait for TX Enqueue in Mode 4-ITL Shortage

TX enqueue in mode 4 的等待事件我们分成3部分讨论.

1> ITL (interested transaction list) shortage

2 > Unique key enforcement

3 > Bitmap index entry

Ok 我们先从ITL Shortage说起.

SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid) file_no,

2 dbms_rowid.ROWID_BLOCK_NUMBER(rowid) blk_no

3 from a

4 where rownum<2

5 /

FILE_NO BLK_NO

---------- ----------

4 1550

SQL> alter system dump datafile 4 block 1550;

系统已更改。

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x000a.017.00000128 0x00802ee0.004b.2a C--- 0 scn 0x0000.00076ef1

0x02 0x0005.01c.00000133 0x00803f33.0059.28 --U- 9 fsc 0x0009.00079e2b

0x03 0x0002.02c.0000012a 0x00800605.005b.1b C--- 0 scn 0x0000.00076d65

ITL是数据块内"感兴趣的事物列表",是数据块内事物插槽(transaction slot).由建表时INITRANS 和 MAXTRANS子句决定. "--U-"表示此ITL正在被使用.

在默认情况下,表所包含的每个数据块中有1个ITL,索引有2个ITL,每个ITL占据24个字节,以USN.SLOT#.WRAP#. 形式包含事务ID. 每一个DML事物被处理之前必须占有数据块内ITL空间,当某个数据库块内所有可用的ITL都在使用中，并且PCTFREE中没有空间让ORACLE动态分配一个新的ITL事物插槽时，ITL争用就会发生,在这种情况下,会话将持续等待,直到一个事务被提交或回滚.

The following listing shows an example of the TX enqueue wait in mode 4 that is caused by ITL shortage, as seen in the V$LOCK view.

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- --- -- ------ ------ ----- ------- ----- ----- 8A2B6400 8A2B6414 8 TM 3172 0 3 0 248 0

89EF3A0C 89EF3A1C 8 TX 131147 13 0 4 248 0

01A4177C 01A41848 9 TX 131147 13 6 0 376 18 A2B6388 8A2B639C 9 TM 3172 0 3 0 376 0

从Oracle 9i开始 可以通过以下sql定位ITL征用的数据库对象.

SELECT s.owner,

s.object_name,

s.subobject_name,

s.object_type,

s.tablespace_name,

s.VALUE,

s.statistic_name

FROM v$segment_statistics s

WHERE s.statistic_name = 'ITL waits'

AND s.VALUE > 0

ORDER by VALUE DESC

/

Wait for TX Enqueue in Mode 4-Unique Key Enforcement

Unique or primary key enforcement is yet another reason you might see contention for the TX enqueue in mode 4. (In Oracle Database 10g, the wait event name is enq: TX-row lock contention.)

This only occurs when multiple concurrent sessions insert the same key value into a table that has one or more unique key constraints. The first session to insert the value succeeds, but the rest freeze and wait until the first session commits or rolls back to see if "ORA-00001 unique constraint (%s.%s) violated" should be raised.

The following listing shows an example of a TX enqueue wait in mode 4 as seen in the V$LOCK view that is due to unique key enforcement. What is the difference between this listing and the one caused by ITL shortage? Notice that the waiter (SID=8) has two TX entries in the V$LOCK view. This doesn't mean that it owns two transactions. In fact, the V$TRANSACTION view shows only two transactions-one for SID 8 and another for SID 9. This shows SID 8 is waiting for the TX lock held by SID 9, and it wants a share lock (mode 4) on the object. SID 8 also holds a TX lock for its own transaction. Another thing you should be aware of is the object ID that is recorded in ID1 of the DML transaction (TM) is always the table ID, not the index ID, although a unique key is enforced through an index.

Wait for TX Enqueue in Mode 4-Bitmap Index Entry

A wait for the TX enqueue in mode 4 can also occur when multiple sessions try to update or delete different rows that are covered by the same bitmap entry. Of course, this does not apply if the application does not use bitmap indexes.

Unlike the B-tree index entry, which contains a single ROWID, a bitmap entry can potentially cover a range of ROWIDs. So when a bitmap index entry is locked, all the ROWIDs that are covered by the entry are also locked.

The following listing shows an example of a TX enqueue wait in mode 4 as seen in the V$LOCK view due to bitmap entry. What is the difference between this listing and the preceding one in the unique key enforcement? Can you tell if you are dealing with a bitmap index entry or unique key enforcement issue by looking at the V$LOCK view output? No, you can't. The object ID in the TM lock doesn't help either, as it is the object ID of the table and not the index. That is why it is very important for you to capture the SQL statement of the waiting and blocking sessions.

If the waiting session is attempting an insert, you are dealing with a unique key enforcement issue.

如果waiting session正在试图作insert操作.

If the waiting session is attempting an update or delete, most likely you are dealing with a bitmap entry issue.如果waiting session正在试图作update or delete操作

In order to resolve the contention, you have to hunt down the offending user. However, the offending user is not always the user who holds the lock. That user was there first, for crying out loud. If the user has a legitimate reason to hold the lock, the waiters should back out of their transactions.

Wait for ST Enqueue

每个数据库只有一个ST锁。

修改UET$(用户范围表)和FET$(空闲范围表)的数据库操作需要ST锁，这包括删除、截取、结合等动作。

ST锁争用表明有多个活动会话在字典管理的表空间中执行动态磁盘空间分配或解除分配。不是使用TEMPORARY子句创建的临时表空间和经历大范围分配和解除分配的字典管理的表空间是ST锁争用的主要原因。

减少ST锁争用的方法：

1) 使用本地管理表空间，在ORACLE9i中所有表空间均可本地化管理。

2) 使用CREATE TEMPORARY TABLESPACE TEMPFILE...命令创建所有临时表空间。

关于如何进行Enqueue的整理问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注行业资讯频道了解更多相关知识。