这个也是我原来的学习比较，应该算是latch 姊妹篇吧；希望对大家有用；也希望大家能够多多交流，在调优时的心得；
如果e文，有错误，请见谅；
直到oracle 9i enqueue 等待事件的参数为：name，mode，id1，id2 ；从10g ，第一个参数没有变化，但是第二个和第三个参数提供了关于enqueue的说明信息；在处理enqueue事件的时候要考虑一下几点：
• Enqueues are locks that apply to database objects.
• Enqueues are transactional, initiated by the application.
• The Oracle session is waiting to acquire a specific enqueue. The enqueue name and mode is recorded in the P1 parameter. The appropriate action to take depends on the type of enqueue being competed for.
• Up to Oracle9i Database, the enqueue wait event represents all enqueue waits; starting in Oracle Database 10g, all enqueues are broken out and have independent wait events.
------------------------------------------------------------------------------------
什么是enqueue resource
指收到enqueue 锁，影响的数据库资源；oracle在管理enqueue 资源时，使用内部的 array 内部结构；这些信息可以从 X$ksqrs 或者 v$resource 查出：
Select * from v$resource;
如上图所示，enqueue 资源结构，是有锁类型和 两个数字 鉴别符组成的；如下表所示：
Enqueue resources 能够被并发锁定的最大值，是由 lock manager 的 enqueue_resources 决定的；默认情况下，不足够的；当使用并发DML操作时
就有可能需要增加 此值；进程可能由于获取不到 enqueue 锁而报 ora-00052 的错误；通过视图V$RESOURCE_LIMIT 可以获得有用的信息：比如high
Watermark （max_utilization) from limit_value; 知道 oracle 8i enqueue_reources 参数同时也设定 了X$KSQRS array 的大小；从9i 开始，使用了不同的机制来建立 X$KSQRS array 的大小：
select *
from v$resource_limit
where resource_name in (’enqueue_resources’,’enqueue_locks’,
’dml_locks’,’processes’,’sessions’);
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------ ------------------- --------------- ---------- ----------
processes 438 643 2000 2000
sessions 443 664 2205 2205
enqueue_locks 402 465 27101 27101
enqueue_resources 449 584 800 UNLIMITED
dml_locks 68 778 600 UNLIMITED
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
什么是enqueue lock：
An enqueue lock is the lock itself；oracle 使用一个独立的 array 而非 enqueue resources array 来管理 enqueue 锁；此结构可以通过 X$KSQEQ 或者V$ENQUEUE_LOCk 来获得相关信息；此结构的大小是受参数_ENQUEUE_LOCKS 来决定的；
select *
from x$ksqeq
where bitand(kssobflg,1)!=0
and (ksqlkmod!=0 or ksqlkreq!=0);
-- or simply query from V$ENQUEUE_LOCK
select * from v$enqueue_lock;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- --- -- ------- ---- ----- ------- ------- -----
243F8368 243F8378 2 MR 51 0 4 0 349520 0
243F831C 243F832C 2 MR 6 0 4 0 349520 0
243F82D0 243F82E0 2 MR 5 0 4 0 349520 0
243F8284 243F8294 2 MR 4 0 4 0 349520 0
243F8238 243F8248 2 MR 3 0 4 0 349520 0
243F81EC 243F81FC 2 MR 2 0 4 0 349520 0
243F81A0 243F81B0 2 MR 1 0 4 0 349520 0
243F80BC 243F80CC 3 RT 1 0 6 0 349521 0
243F7F8C 243F7F9C 4 XR 4 0 1 0 349524 0
243F8108 243F8118 5 TS 2 1 3 0 349519 0
243F7FD8 243F7FE8 9 TX 393241 875 0 6 2651 0
如果在 v$enqueue_locks 中没有看到tx或者 tm锁，这是正常的；因为他们并不在 x$ksqeq 结构中，除非发生 enqueue 等待；oracle使用不同的机制来管理 TX 和 TM enqueues：the X$KTCXB (kernel transaction control transaction object—the base view for V$TRANSACTION_ENQUEUE) and X$KTADM (kernel transaction access definition dml lock)；可以使用下列查询：其结构的大小是受 transactions 和 dml_locks 来决定的；v$lock 则显示了所有的锁类型：
select *
from x$ktcxb
where KTCXBLKP in (select kaddr from v$lock where type = ’TX’);
select *
from x$ktadm
where KSQLKADR in (select kaddr from v$lock where type = ’TM’);
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ENQUEUE 结构：
Enqueue 机构与 cache buffer是的机构非常类似；主要的组成为 enqueue hash chains latches，enqueue hash table ，enqueue hash chain 和 enqueue 资源；其关系如下所示：
enqueue hash chains latch ß (1:m) à hash bucket ß (1:1) à enqueue hash chain
Child enqueue hash chains latch 会保护 enqueue hash table 和 hash chains；在默认情况下 child enqueue hash chains latches 的数量是和 cpu_count相等的；此值夜可以通过 _enqueue_hash_chain_latches 参数更改；
Enqueue 资源在基于其类型 identifier 的基础上以散列的形式与 enqueue hash table 相联系，并且被放置在合适的 hash chain 上；默认的 enqueue hash table 的长度是源自于 session 参数的，并且可以通过参数 _ENQUEUE_HASH 来修订；如果需要增加 enqueue_resources 的值，就需要注意enqueue hash chains latch的sleep 比率；因为 enqueue hash table length 不变因为它是 源于sessions ，而不是enqueue_resources的；
对 enqueue resources 和 小 enqueue hash table 的高要求，将会造成 高hash 冲突，和潜在的 长的 hash chains；其表现形式就是 的 enqueue hash chains latches 竞争；在此情况下就可能需要增加 _ENQUEUE_HASH 值：
enqueue hash table length = ((SESSIONS – 10) * 2) + 55
如果想学习更详细的 enqueue hash table ，reources，和queue 的内容可以使用下列命令dump enqueue结构：
Alter session set events 'immediate trace name enqueues level3 ';
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Decoding enqueue and mode：
Enqueue wait event 中的p1 列对 enqueue 的类型和模式进行了编码；进行破译时，将进得到两个字符的 enqueue 类型，和一个数字的mode 号；使用下列查询可以进行enqueue的 破译：
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’;
SID EVENT P1 P1RAW TY MODE
---- --------------- ---------- ---------------- -- ----------
405 enqueue 1213661190 0000000048570006 HW 6
132 enqueue 1397817350 0000000053510006 SQ 6
43 enqueue 1413677062 0000000054430006 TC 6
44 enqueue 1415053316 0000000054580004 TX 4
40 enqueue 1415053318 0000000054580006 TX 6
同样的，enqueue 类型也能够在 enqueue 等待事件的P1RAW 列中到；如上面的查询只需要关注最后4byte的数字；即8位数字；以 54580006 来进行比较，2 high order bytes 是 0x5458Hex，54Hex 是数字 84,58Hex是88 ，那么就可以找出其类型：
Select chr(84) ||char(88) from dual;
在10g中进行解码已经非常容易，因为10g中 enqueue 已经是 enqueue wait event 名称的一部分；
进程在以下六种模式中会请求 enqueue：Null (N), Row Share (RS), Row Exclusive (RX), Share (S), Share Row Exclusive (SRX), or Exclusive (X). 这六种模式由0到6 中的一个来表示；lock mode 是由 p1raw 的低2位表示的，例如：54580006 中的0006Hex，所以其lock mode 是6 ；同样使用P1 列也能进行解码：mod(P1,16) ,to_char(bitand(P1,65535).
Oracle 根据lock mode 来决定，一个资源是否能够被多个并发进程共享；
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
成因，分析，与处理：
由于enqueue 是多种多样的，一个enqueue 等待事件的发生可能够不同的原因；通常的成因和 处理方式要根据会话所竞争的enqueue的类型和mode来决定；对于每种 类型的enqueue oracle 提供实例级别的信息： X$KSQST；
-- Oracle9i Database and above
select *
from v$enqueue_stat
where cum_wait_time > 0
order by inst_id, cum_wait_time;
INST_ID EQ TOTAL_REQ# TOTAL_WAIT# SUCC_REQ# FAILED_REQ# CUM_WAIT_TIME
---------- -- ---------- ----------- ---------- ----------- -------------
1 SQ 66551 437 66551 0 498
1 CU 64353 133 64353 0 1616
1 HW 453067 18683 453067 0 11811
1 CF 119748 76 119605 143 37842
1 TX 22687836 9480 22687758 71 672435
1 TC 3620 724 3620 0 679237
1 TM 89822967 91 89817200 5 4056333
-- Oracle 7.1.6 to 8.1.7
select inst_id,
ksqsttyp "Lock",
ksqstget "Gets",
ksqstwat "Waits"
from x$ksqst
where ksqstwat > 0
order by inst_id, ksqstwat;
你应当认识到如何与遭遇 enqueue 的用户打交道；他们没有收到任何的一场信息，并且他们的session 被完全冻僵；这就是为什么他们会找到你的原因；不要期望他们告诉你他们正在等待enqueue，；你应当感谢他们没有 失态；
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Wait for TX Enqueue in Mode 6
此类型的enqueue（P1= 1415053318, P1RAW= 54580006) 是最常见的,通常是指 行级锁的竞争；当事务试图去更新或删除被其他事务锁定的行时就会发生此事件；
通常这是应用的问题；等待的session 会在 锁定的session 进行commit 或者 回退后执行其自身的事务；没有别的方法来释放这样的锁；直接杀死session会引起事务的回退；
当遇到 TX enqueue 时，第一步是找到谁是持有者；如果有多个等待者正在等待一个同样的资源，可以进行如下查询；如果blocking session是一个特别的进程，用户可能会 taking a break ；如果是着这种情况，就请用户去 commit 或者 roll back；如果是批量的或者oltp 进程，就需要检查 session 是否是 alive的状态；可能他是一个alive的oracle 进程，但是其进程已经死了，或者hung；这种情况就需要kill session；杀死生产环境的进程时一定要与应用进行确认：
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;
你能够发现正在竞争的相关资源；resource ID 在试图 V$lock.id1 中是可用的，The resource ID is available in the V$LOCK.ID1 column of the DML lock (TM) for that transaction. It is also available in the V$SESSION.ROW_WAIT_OBJ# of the waiting session. The following query retrieves the resource of the TX enqueue wait:
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 = ’enqueue’;
===============================================================================================================================
Wait for TX Enqueue in Mode 4—ITL Shortage
此种锁主要有以下原因造成:
• ITL (interested transaction list) shortage
• Unique key enforcement
• Bitmap index entry
ITL 是数据块中的一个事务空位；itl slot 是由 initrans 定义有 maxtrans 限制的；默认情况下一个表会有1个itl，索引有21个itl；每个itl 站 24 bytes，并且以USN.SLOT#.WRAP#. 的格式包含有一个 事务ID；在数据进行修改之前每个dml需要在block 中获取itl 空间；当一个block中的所有的 可用ITL 被使用，并且表 中的 PCTFREE 没有足够的空间来动态的分配 ITL 时，就会引发ITL 竞争；这时session 就会等待，知道其他session
提交或者回退后，重新获取ITL slot；ITL 就想一个建筑的车位，所有要开车进入建筑的人都需要一个停车位；如果没有的话就只能进行等待；
Note Starting in Oracle9i Database, each data block has a minimum of two ITL slots by default. Even if you specify one, you still get two. The DBA_TABLES view will show just one, but the block dump will show two.
TX enqueue 等待事件中的 mode 4 的情况并不是总在 ITL 短缺的情况下出现，所以遇到此情况时要先进行判断；v$session 包含了关于 enqueue 对象的信息；尤其是列：ROW_WAIT_FILE# and ROW_WAIT_BLOCK#；使用这些值，可以dump block 并且查看活动的itl的 数；并确认是否是itl的问题；如果是，就需要重新建立一个有更高 initrans 值的 对象；同时注意设置 pctfree ；
alter system dump datafile block ;
-- The ITL portion of a block dump
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.051.0001fcf2 0x07ca2145.4e11.18 --U- 0 scn 0x070e.03df2f08
0x02 0x0005.049.00022d46 0x090618b7.5967.1c C--- 0 scn 0x070e.03df2f6a
0x03 0x0012.008.0001244b 0x0580a510.26ac.0c --U- 0 scn 0x070e.03df2f7b
0x04 0x0014.00d.00012593 0x090d4f93.28d3.1e C--- 0 scn 0x070e.03e08919
从oracle 9i 开始，oracle 开始跟踪 itl 等待的数目，并且将相关信息发布到 v$segment_statistics；可以使用下列语句：
select owner,
object_name,
subobject_name,
object_type,
tablespace_name,
value,
statistic_name
from v$segment_statistics
where statistic_name = ’ITL waits’
and value > 0
order by value;
===============================================================================================================================
Wait for TX Enqueue in Mode 4—Unique Key Enforcement
在10g 中此等待事件又称为：enq：tx-row lock contention；这种情况只发生在 当有多个并发的session 向同一个表中插入相同的key 值；第一个session会插入值，其它session处于等待中中，直到 第一个session commit 或者 rollback；此时 ora-00001 会增多；
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.
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- --- -- ------ ----- ----- ------- ----- -----
01AF6120 01AF61EC 8 TX 131099 14 6 0 4051 0
8A2B6388 8A2B639C 8 TM 3176 0 3 0 4051 0
89EF37CC 89EF37DC 8 TX 131094 14 0 4 4051 0
01AF6120 01AF61EC 9 TX 131094 14 6 0 4461 1
8A2B6400 8A2B6414 9 TM 3176 0 3 0 4461 0
==============================================================================================================================
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；
与b-tree 索引不同的是，它可能包含一组 rowid ，而前者只包含一个rowid；所以当 位图索引项锁定后，会锁定一组的 rowid；
如下表所示：
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. If the waiting session is attempting an update or delete, most likely you are dealing with a bitmap entry issue.
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- --- -- ------ ----- ----- ------- ----- -----
01A52DB4 01A52E80 7 TX 131120 14 6 0 31 1
8A2B6310 8A2B6324 7 TM 3181 0 3 0 31 0
01A52DB4 01A52E80 9 TX 131107 14 6 0 9 0
8A2B6388 8A2B639C 9 TM 3181 0 3 0 9 0
89EF3A4C 89EF3A5C 9 TX 131120 14 0 4 9 0
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 锁；此是lock的出现说明，多个session正在执行 数据字典管理的表空间的磁盘空间的自动分配或者回收；
临时表空间没有在创建时没有使用 temporary 语法，以及数据字段管理的 表空间 在高程度的 分配或者回收 空间时，就会触发此事件；以下的配置会降低st锁，尤其是前两条：
• Use locally managed tablespaces. In Oracle9i Database Release 2, all tablespaces including SYSTEM can be locally managed. There is no reason not to use locally managed tablespaces. (If the SYSTEM tablespace is locally managed, you cannot have any dictionary managed tablespaces that are read/write.)
• Recreate all temporary tablespaces using the CREATE TEMPORARY TABLESPACE TEMPFILE… command.
• For dictionary managed tablespaces, increase the next extent sizes of segments that experienced high dynamic allocations. Also, preallocate extents for the segments that are frequently extended.
===============================================================================================================================
未加索引的外键是 出现TM mode 3的主要原因；但这是只在 9i中适用的；
Depending on the operation, when foreign key columns are not indexed, Oracle either takes up a DML share lock (S – mode 4) or share row exclusive lock (SRX – mode 5) on the child table whenever the parent key or row is modified. (The share row exclusive lock is taken on the child table when the parent row is deleted and the foreign key constraint is created with the ON DELETE CASCADE option. Without this option, Oracle takes the share lock.) The share lock or share row exclusive lock on the child table prohibits other processes from getting a row exclusive lock (RX—mode 3) on the table. The waiting session will wait until the blocking session commits or rolls back its transaction.
Here is a philosophical question for you: Are you going to start building new indexes for all the foreign key columns in your databases? DBAs are divided on this. Our take is that you should hold your horses and don’t get carried away building new indexes just yet. If you do, you will introduce many new indexes to the database, some that are unnecessary. For example, you don’t need to create new indexes on foreign key columns when the parent tables they reference are static. You only need to create indexes on foreign key columns of the child table that is being identified by the enqueue wait event. The object ID for the child table is recorded in the P2 column, which corresponds to the ID1 column of the V$LOCK view. Query the DBA_OBJECTS view using the object ID and you will see the name of the child table. Yes, you will be operating in reactive mode, but it beats creating unnecessary indexes in the database, which not only wastes storage and increases maintenance, but may open up another can of worms for SQL tuning.
Following is an Oracle8i Database V$LOCK view output of a TM enqueue wait in mode 3 that is caused by an unindexed foreign key column. Notice the blocking session holds two TM locks: one for the parent table (ID1=3185) and the other for the child table (ID=3187). The share row exclusive lock (mode 5) on the child table prevents the row exclusive lock (mode 3) request from the waiting session (SID=9).
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- --- -- ------ ----- ----- ------- ----- -----
01A52DB4 01A52E80 7 TX 131155 14 6 0 603 0
8A2B6388 8A2B639C 7 TM 3187 0 5 0 603 1
8A2B6310 8A2B6324 7 TM 3185 0 3 0 603 0
8A2B6400 8A2B6414 9 TM 3187 0 0 3 758 0
The same steps that are used to produce the preceding TM enqueue contention are repeated in an Oracle9i Database; following is the output. In this case, the ID of the child table is 128955 and the ID of the parent table is 128953. Notice that there is no blocking lock. Unindexed foreign keys are no longer an issue starting in Oracle9i Database.
KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- --- -- ------- ------ ----- ------- ----- -----
C00000003B1F7230 295 TM 128953 0 2 0 21 0
C00000003B1F73B0 295 TM 128955 0 3 0 21 0
C000000036F5F2F0 295 TX 4391002 145885 6 0 21 0
C00000003B1F6CF0 465 TM 128953 0 3 0 27 0
C00000003B29B118 465 TX 1310720 143517 6 0 27 0
C00000003B1F6DB0 465 TM 128955 0 3 0 27 0
Finally, TM enqueue contention in mode 3 can also occur when a table is explicitly locked in the share mode or higher and there are concurrent DML activities against the table. This is common with applications that use old third-party vendor codes. You can query the V$SQLAREA view for the LOCK TABLE statement. Following is an example of what this contention will look like in the V$LOCK view:
KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---- -- ------ ---- ----- ------- ------- -----
C00000003B1F6B70 295 TM 128956 0 0 3 12 0
C00000003B1F75F0 465 TM 128956 0 4 0 18 1
阅读(2369) | 评论(0) | 转发(0) |
