等待队列分析
当发生ITL等待时,锁的请求模式是共享(4)模式,此时,通过V$ENQUEUE_LOCK就可以观察到TX锁等待:
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
1EEBCDE0 1EEBCDF4 323 TX 589858 142485 0 4 1366 0
但是,除了ITL等待之外,还有其它几种等待也是共享模式,因此我们不能仅仅通过其请求模式来判断是ITL等。我们可以通过session event来鉴定这类等待队列,其对应的事件为“enq: TX - allocate ITL entry”:
2 from v$session s, v$enqueue_lock l, v$session_event e, dba_objects o
3 where e.sid=l.sid
4 and e.sid = s.sid
5 and s.row_wait_obj# = o.object_id(+)
6 and e.event like 'enq: TX%';
SID EVENT ROW_WAIT_OBJ# OBJECT_NAME
---------- ------------------------------ ----------------- ------------------
323 enq: TX - allocate ITL entry 198062 T_TEST5
这里有一点要注意,当请求模式为共享模式时,v$session中ROW_WAIT_*字段信息是并不准确,你可以将其作为参考,但是它可能并不是实际请求的对象。以ROW_WAIT_OBJ#为例,它可能为持锁会话中最后一次加锁的对象(如上例,为T_TEST5,并非真正导致等待的对象TX_LOCK_TAB),大多数情况下其值为-1或0。
和记录锁不同,ITL等待可能发生在表上,也可能发生在索引上。要精确定位导致发生这一等待事件的对象,分析过程就相对复杂一些。
首先,通过被阻塞事务的请求锁的ID1、ID2找到事务的回滚段信息:
2 from v$lock l, v$transaction t, v$session s
3 where l.type = 'TX'
4 and trunc(id1/power(2,16)) = t.xidusn
5 and l.id2 = t.xidsqn
6 and id1 - power(2,16)*trunc(id1/power(2,16)) = t.xidslot
7 and t.addr = s.taddr
8 and l.request = 4;
REQ_SESSION LOCK_SESSION XIDUSN XIDSLOT START_UBAFIL START_UBABLK USED_UBLK START_UBAREC
----------- ------------ --------- ------- ------------ ------------- --------- ------------
323 311 29 7 2 4197 1 1
被阻塞事务使用到UNDO数据块为文件2上4197,UNDO开始记录为1,且只用到一个回滚块。我们将该UNDO块dump出来:
System altered.
找到对应的回滚记录。可以看到,从第1(0x1)条记录开始,slot为7(0x07)的记录有4条:
* Rec #0x1 slt: 0x07 objn: 198074(0x000305ba) objd: 198074 tblspc: 5(0x00000005)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x00801064.0058.01 ctl max scn: 0x0000.b0e03719 prv tx scn: 0x0000.b0e03765
txn start scn: scn: 0x0000.b0e0e1fa logon user: 35
prev brb: 8388927 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0018.012.00000252 uba: 0x00800367.00f2.35
flg: C--- lkc: 0 scn: 0x0000.b0db5fed
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0141072a hdba: 0x01410723
itli: 3 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 28
ncol: 3 nnew: 1 size: 0
col 2: [10] 31 31 31 31 31 31 31 31 31 31
*-----------------------------
* Rec #0x2 slt: 0x07 objn: 198176(0x00030620) objd: 198176 tblspc: 5(0x00000005)
* Layer: 10 (Index) opc: 22 rci 0x01
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
index undo for leaf key operations
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0018.012.00000252 uba: 0x00800367.00f2.37
flg: C--- lkc: 0 scn: 0x0000.b0db5fed
Dump kdilk : itl=3, kdxlkflg=0x1 sdc=0 indexid=0x1415c03 block=0x01415c05
(kdxlre): restore leaf row (clear leaf delete flags)
key :(18): 0a 31 31 31 31 31 31 31 31 31 31 06 01 41 07 2a 00 02
...
*-----------------------------
* Rec #0x4 slt: 0x07 objn: 198062(0x000305ae) objd: 198062 tblspc: 5(0x00000005)
* Layer: 11 (Row) opc: 1 rci 0x0f
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0019.02a.0000009a uba: 0x008000eb.0077.02
flg: C--- lkc: 0 scn: 0x0000.b0de9878
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x014103d7 hdba: 0x014103d3
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 28
ncol: 3 nnew: 1 size: 0
col 0: [ 3] 41 41 41
事务对象分别是T_TEST5(objn: 198062)、TX_LOCK_TAB_IDX(objn: 198176)和TX_LOCK_TAB(objn: 198074),其中,对于索引TX_LOCK_TAB_IDX来说,一个UPDATE操作实际是是一个INSERT操作加DELETE操作,因此存在2条记录。那么,哪个才是导致ITL等待的对象呢?我们可以先找到被阻塞会话当前正在执行的语句(即被阻塞的语句):
2 from v$session s, v$sqlarea q
3 where s.sql_address = q.address(+)
4 and s.sql_hash_value = q.hash_value(+)
5 and s.sid = 323;
SQL_TEXT
--------------------------------------------------------------------------------
update tx_lock_tab set c=lpad('D',10,'D') where a=3638
可以看到,被阻塞事务正在执行对TX_LOCK_TAB的UPDATE操作,而T_TEST5与其没有任何关联(没主外键关系),因而可以被排除。
我们先确认是不是索引数据块引起的。从UNDO记录中找到索引数据块地址(block=0x01415c05),dump出来:
2 dbms_utility.data_block_address_block(TO_NUMBER('01415c05', 'XXXXXXXX')) block_id from dual;
FILE_ID BLOCK_ID
---------- ----------
5 89093
HELLODBA.COM> alter system dump datafile 5 block 89093;
System altered.
Trace文件内容:
SQL代码
seg/obj: 0x30620 csc: 0x00.b0e0e1fa itc: 4 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1415c01 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0037.002.0000005c 0x008011c3.0051.0c ---- 1 fsc 0x0016.00000000
0x03 0x001d.007.00000060 0x00801065.0058.02 ---- 1 fsc 0x0016.00000000
0x04 0x0019.015.000000a5 0x008000ec.007f.02 ---- 1 fsc 0x0016.00000000
Leaf block dump
===============
...
kdxcofbo 762=0x2fa
kdxcofeo 1306=0x51a
kdxcoavs 684
...
可以看到,itc为4,且空闲空间(kdxcoavs)为684字节,不会造成ITL不足等待。此外,其第一条ITL Slot(分裂事务使用)没有被占用,说明不存在对该ITL slot的争用。用同样方法,可以确认另外一个索引块没有造成ITL等待。
然后,再次确认表的数据块。同样还是将其(bdba: 0x0141072a)dump出来:
brn: 1 bdba: 0x1410721 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0037.002.0000005c 0x008011c3.0051.0b ---- 1 fsc 0x0000.00000000
0x02 0x0019.015.000000a5 0x008000ec.007f.01 ---- 1 fsc 0x0000.00000000
0x03 0x001d.007.00000060 0x00801065.0058.01 ---- 1 fsc 0x0000.00000000
data_block_dump,data header at 0xad4587c
===============
...
fsbo=0x2e8
fseo=0x2f5
avsp=0xd
...
可以发现itc为3,没有达到max trans的限制,但是其空闲空间只有0xd=13(avsp),不足以容纳1条ITL slot(24字节)了,因此判定是表TX_LOCK_TAB的数据块上空间不足导致的ITL等待。
有一点要注意,只有当阻塞事务的ITL或者其ITL表之前事务的释放ITL空间时,被阻塞进程才能继续。例如,如果导致阻塞的事务在数据块中ITL序号为0x02,那么如果没有其它事务等待0x01事务的ITL的话,无论是0x01还是0x02的事务被释放,被阻塞的事务都能分配到释放的ITL空间,从而继续;而如果是0x03的事务被释放,被阻塞事务仍然被阻塞。如上例中,从v$transaction找到XID信息得知,导致阻塞的事务的ITL序号是0x01,而此时如果释放第二个事务。
注意:因为ITL空间分配后不会被回收,当这种情况发生时,并不会影响到之前的分析。
递归事务对ITL slot请求分配是在递归事务内部完成,因此因为递归事务的ITL等待出现的时间周期很短暂,我们通过性能视图观察到的这一类等待基本上是由于INITRANS或MAXTRANS引起的。大量ITL等待的出现会影响系统和应用的性能,我们可以通过以下查询观察到在哪个对象上发生ITL等待等待最多,并依此对相应的对象或应用进行调整:
2 from (select owner, object_name, object_type, value
3 from v$segment_statistics
4 where object_name not like 'BIN%'
5 and statistic_name = 'ITL waits'
6 order by value desc)
7 where rownum <= 10;