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
fromv$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
在数据库内部,enqueue architecture 和 sga中cache buffers architecture十分近似. (读过eygle <>这本书的朋友可以参照学习enqueue architecture)
enqueue architecture 的最主要组件由enqueue hash chains latches,enqueue hash table,enqueue hash chains, andenqueue 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...命令创建所有临时表空间。
