介绍:
enqueue - TX 是DML的锁,从事务初始化开始获得。直到事务结束提交或者回滚结束。
enqueue - TX 是DML的锁,从事务初始化开始获得。直到事务结束提交或者回滚结束。
TYPE - TX, mode - 4 (Share)
1> ITL (interested transaction list) shortage
2 > Unique key enforcement
3 > Bitmap index entry
2 > Unique key enforcement
3 > Bitmap index entry
ITL是指block的事务槽的个数。interested transactin list,表示有多少个transaction可以使用block。这有段的参数
inittrans和maxtrans来控制的。没有ITL占用24个字节。常见的情况是block的空间都被占用完了。没有空间留个ITL。在这
种情况下,事务的会话开始请求share 4的enqueue。等待其他的会话释放ITL。
inittrans和maxtrans来控制的。没有ITL占用24个字节。常见的情况是block的空间都被占用完了。没有空间留个ITL。在这
种情况下,事务的会话开始请求share 4的enqueue。等待其他的会话释放ITL。
下面是block中的ITL的数据。有俩个会话在这个block上有事务操作。
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.002.00008aca 0x0080050f.018c.16 --U- 1 fsc 0x0000.019d4d56
0x02 0x0002.01f.00007f06 0x0080048f.0191.58 --U- 3 fsc 0x0000.019d4d53
测试案例:
0x01 0x0003.002.00008aca 0x0080050f.018c.16 --U- 1 fsc 0x0000.019d4d56
0x02 0x0002.01f.00007f06 0x0080048f.0191.58 --U- 3 fsc 0x0000.019d4d53
测试案例:
下面是测试的过程:
创建一个表,要求有4条数据,并占满整个block的空间。
drop table t1;
drop table t1;
create table t1 (c1 varchar2(3000));
insert into t1 values ('1');
insert into t1 values ('2');
insert into t1 values ('3');
insert into t1 values ('4');
commit;
insert into t1 values ('2');
insert into t1 values ('3');
insert into t1 values ('4');
commit;
update t1 set c1 = c1 || rpad('A',2040) where rownum <= 3;
commit;
update t1 set c1 = c1 || rpad('A',1911) where c1 = '4';
commit;
commit;
update t1 set c1 = c1 || rpad('A',1911) where c1 = '4';
commit;
这4条记录所占的空间是:8058字节
select (2047*3+1917) from dual;
select (2047*3+1917) from dual;
然后看看4条记录的block的使用情况。可以看到tosp是4个字节,也就是说只有4个字节是空闲的。
data_block_dump,data header at 0x2fa1064
===============
tsiz: 0x1f98
hsiz: 0x1a
pbl: 0x02fa1064
bdba: 0x024009f4
76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1e
avsp=0x4
tosp=0x4
0xe:pti[0] nrow=4 offs=0
0x12:pri[0] offs=0x1799
0x14:pri[1] offs=0xf9a
0x16:pri[2] offs=0x79b
0x18:pri[3] offs=0x1e
block_row_dump:
tab 0, row 0, @0x1799
tl: 2047 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [2041]
31 41 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
......
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
tab 0, row 1, @0xf9a
tl: 2047 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [2041]
32 41 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
......
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
tab 0, row 2, @0x79b
tl: 2047 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [2041]
33 41 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
......
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
tab 0, row 3, @0x1e
tl: 1917 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [1911]
34 41 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
......
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20
end_of_block_dump
End dump data blocks tsn: 9 file#: 9 minblk 2548 maxblk 2548
===============
tsiz: 0x1f98
hsiz: 0x1a
pbl: 0x02fa1064
bdba: 0x024009f4
76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1e
avsp=0x4
tosp=0x4
0xe:pti[0] nrow=4 offs=0
0x12:pri[0] offs=0x1799
0x14:pri[1] offs=0xf9a
0x16:pri[2] offs=0x79b
0x18:pri[3] offs=0x1e
block_row_dump:
tab 0, row 0, @0x1799
tl: 2047 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [2041]
31 41 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
......
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
tab 0, row 1, @0xf9a
tl: 2047 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [2041]
32 41 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
......
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
tab 0, row 2, @0x79b
tl: 2047 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [2041]
33 41 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
......
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
tab 0, row 3, @0x1e
tl: 1917 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [1911]
34 41 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
......
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20
end_of_block_dump
End dump data blocks tsn: 9 file#: 9 minblk 2548 maxblk 2548
然后打开3个会话:
session 1:
update t1 set c1 = c1 where ROWID = 'AAAHiJAAJAAAAn0AAA';
update t1 set c1 = c1 where ROWID = 'AAAHiJAAJAAAAn0AAA';
session 2:
update t1 set c1 = c1 where ROWID = 'AAAHiJAAJAAAAn0AAB';
update t1 set c1 = c1 where ROWID = 'AAAHiJAAJAAAAn0AAB';
session 3:
update t1 set c1 = c1 where ROWID = 'AAAHiJAAJAAAAn0AAC';
update t1 set c1 = c1 where ROWID = 'AAAHiJAAJAAAAn0AAC';
当运行第3个会话时,会话被hang住了。
查看会话的等待时间:
set linesize 200
col event format a40
select sid,event,p1,p1raw,p2,p2raw,p3,p3raw from v$session_wait where sid in (16);
set linesize 100
set linesize 200
col event format a40
select sid,event,p1,p1raw,p2,p2raw,p3,p3raw from v$session_wait where sid in (16);
set linesize 100
sys@ORCL>select sid,event,p1,p1raw,p2,p2raw,p3,p3raw from v$session_wait where sid in (16);
SID EVENT P1 P1RAW P2 P2RAW P3 P3RAW
---------- ---------------------------------------- ---------- -------- ---------- -------- ---------- --------
16 enqueue 1415053316 54580004 524288 00080000 32862 0000805E
---------- ---------------------------------------- ---------- -------- ---------- -------- ---------- --------
16 enqueue 1415053316 54580004 524288 00080000 32862 0000805E
查看什么请求的enqueue。
sys@ORCL>select chr(to_char(bitand(p1,-16777216))/16777215)||
2 chr(to_char(bitand(p1, 16711680))/65535) "Lock",
3 to_char( bitand(p1, 65535) ) "Mode"
4 from v$session_wait
5 where event = 'enqueue';
sys@ORCL>select chr(to_char(bitand(p1,-16777216))/16777215)||
2 chr(to_char(bitand(p1, 16711680))/65535) "Lock",
3 to_char( bitand(p1, 65535) ) "Mode"
4 from v$session_wait
5 where event = 'enqueue';
Lock Mode
---- ----------------------------------------
TX 4
---- ----------------------------------------
TX 4
查看会话在等待那个object。发现是-1。
select SID, SERIAL#, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# from v$session where sid = 16;
select SID, SERIAL#, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# from v$session where sid = 16;
SID SERIAL# ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
---------- ---------- ------------- -------------- --------------- -------------
16 3 -1 0 0 0
---------- ---------- ------------- -------------- --------------- -------------
16 3 -1 0 0 0
查看有什么会话在持有这个enqueue
SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
ORDER BY id1, request;
SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
ORDER BY id1, request;
SESS ID1 ID2 LMODE REQUEST TY
------------------------------------------------ ---------- ---------- ---------- ---------- --
Holder: 11 524288 32862 6 0 TX
Waiter: 16 524288 32862 0 4 TX
------------------------------------------------ ---------- ---------- ---------- ---------- --
Holder: 11 524288 32862 6 0 TX
Waiter: 16 524288 32862 0 4 TX
select * from dba_locks where blocking_others = 'Blocking';
查看dba_waiters;
set linesize 200
select waiting_session, holding_session, lock_type, lock_id1, lock_id2, mode_held, mode_requested from dba_waiters;
set linesize 100
set linesize 200
select waiting_session, holding_session, lock_type, lock_id1, lock_id2, mode_held, mode_requested from dba_waiters;
set linesize 100
WAITING_SESSION HOLDING_SESSION LOCK_TYPE LOCK_ID1 LOCK_ID2 MODE_HELD MODE_R
EQUESTED
--------------- --------------- -------------------------- ---------- ---------- ---------------------------------------
- ----------------------------------------
16 11 Transaction 524288 32862 Exclusive Share
EQUESTED
--------------- --------------- -------------------------- ---------- ---------- ---------------------------------------
- ----------------------------------------
16 11 Transaction 524288 32862 Exclusive Share
查看dba_blockers,
select * from dba_blockers
select * from dba_blockers
HOLDING_SESSION
---------------
11
---------------
11
获取undo的信息:
select trunc(524288/power(2,16)) rbs, bitand(524288,to_number('ffff','xxxx'))+0 slot from dual;
select trunc(524288/power(2,16)) rbs, bitand(524288,to_number('ffff','xxxx'))+0 slot from dual;
RBS SLOT
---------- ----------
8 0
---------- ----------
8 0
select usn,name from v$rollname where usn=8;
USN NAME
---------- ------------------------------
8 _SYSSMU8$
USN NAME
---------- ------------------------------
8 _SYSSMU8$
dump出undo segment头。
alter session set tracefile_identifier='MYSESSION';
alter system dump undo header '_SYSSMU8$';
alter session set tracefile_identifier='MYSESSION';
alter system dump undo header '_SYSSMU8$';
index state cflags wrap# uel scn dba parent-xid nub stmt_num
------------------------------------------------------------------------------------------------
0x00 10 0x80 0x805e 0x0002 0x0000.019dffb8 0x00800424 0x0000.000.00000000 0x00000001 0x00000000
0x01 9 0x00 0x805d 0x001f 0x0000.019dfc4e 0x00800423 0x0000.000.00000000 0x00000001 0x00000000
0x02 9 0x00 0x805d 0x000b 0x0000.019dfa8f 0x00800423 0x0000.000.00000000 0x00000001 0x00000000
------------------------------------------------------------------------------------------------
0x00 10 0x80 0x805e 0x0002 0x0000.019dffb8 0x00800424 0x0000.000.00000000 0x00000001 0x00000000
0x01 9 0x00 0x805d 0x001f 0x0000.019dfc4e 0x00800423 0x0000.000.00000000 0x00000001 0x00000000
0x02 9 0x00 0x805d 0x000b 0x0000.019dfa8f 0x00800423 0x0000.000.00000000 0x00000001 0x00000000
SELECT dbms_utility.data_block_address_block(8389668) "BLOCK",
dbms_utility.data_block_address_file(8389668) "FILE"
FROM dual;
BLOCK FILE
---------- ----------
1060 2
dbms_utility.data_block_address_file(8389668) "FILE"
FROM dual;
BLOCK FILE
---------- ----------
1060 2
dump出undo块的信息。
alter system dump datafile 2 block 1060;
alter system dump datafile 2 block 1060;
*-----------------------------
* Rec #0xe slt: 0x00 objn: 30857(0x00007889) objd: 30857 tblspc: 9(0x00000009)
* 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: 0x00800424.0167.0c ctl max scn: 0x0000.019df9d9 prv tx scn: 0x0000.019dfa4d
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0002.01f.00007f06 uba: 0x0080048f.0191.58
flg: C--- lkc: 0 scn: 0x0000.019d4d53
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x024009f4 hdba: 0x024009f3
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 1 nnew: 1 size: 0
col 0: [2041]
31 41 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
* Rec #0xe slt: 0x00 objn: 30857(0x00007889) objd: 30857 tblspc: 9(0x00000009)
* 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: 0x00800424.0167.0c ctl max scn: 0x0000.019df9d9 prv tx scn: 0x0000.019dfa4d
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0002.01f.00007f06 uba: 0x0080048f.0191.58
flg: C--- lkc: 0 scn: 0x0000.019d4d53
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x024009f4 hdba: 0x024009f3
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 1 nnew: 1 size: 0
col 0: [2041]
31 41 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
相关的data block的地址0x024009f4。
alter system dump datafile 9 block 2548;
alter system dump datafile 9 block 2548;
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.005.00007f39 0x008004ab.0191.34 ---- 1 fsc 0x0000.00000000
0x02 0x0008.000.0000805e 0x00800424.0167.0e ---- 1 fsc 0x0000.00000000
data_block_dump,data header at 0x5c21064
===============
tsiz: 0x1f98
hsiz: 0x1a
pbl: 0x05c21064
bdba: 0x024009f4
76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1e
avsp=0x4
tosp=0x4
0x01 0x0002.005.00007f39 0x008004ab.0191.34 ---- 1 fsc 0x0000.00000000
0x02 0x0008.000.0000805e 0x00800424.0167.0e ---- 1 fsc 0x0000.00000000
data_block_dump,data header at 0x5c21064
===============
tsiz: 0x1f98
hsiz: 0x1a
pbl: 0x05c21064
bdba: 0x024009f4
76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1e
avsp=0x4
tosp=0x4
可以看到itl是2,剩余空间是4。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25105315/viewspace-734899/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25105315/viewspace-734899/