Administration
About Transaction Slots, initrans and v$transaction
User Rating: / 1
- Details
-
Parent Category: Articles
-
Created on Wednesday, 07 March 2012 17:07
-
Last Updated on Monday, 12 March 2012 13:54
-
Published on Wednesday, 07 March 2012 17:07
-
Written by Guy Lambregts
-
Hits: 7680
About Transaction Slots, initrans and v$transaction.
I recently followed a discussion between DBA ' s about pctfree and transaction slots.
When we create a table or index and we do not configure the attribute initrans then the segment is created with 2 transaction slots.
Optionally - but not often done - at segment creation time we can configure a non default number of initial transaction slots per block with the initrans attribute.
If we dump the segment blocks we notice the non default number of initial transaction slots. In case of concurrent DML and if needed Oracle will dynamically allocate additional transaction slots in the blocks, these days the DBA should not worry too much about that it happens behind the scenes and the associated ITL wait is rarely a performance bottleneck.
In the below workshop we will dump blocks, query v$transaction, v$lock joined with v$session Some of you may find it too internal.
SQL> alter session set deferred_segment_creation = false;
Session altered.
SQL> create table T (T_PK number, T_TEXT varchar2(100)) tablespace TS_TUNING initrans 6;
Table created.
SQL> connect / as sysdba
Connected.
SQL> select * from dba_extents where segment_name='T' and owner='TUNING';
SQL> column segment_name format a10
SQL> column partition_name noprint
SQL> column owner format a10
SQL> set linesize 300
SQL> select * from dba_extents where segment_name='T' and owner='TUNING';
OWNER SEGMENT_NA SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
---------- ---------- ------------------ ------------------------------ ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
TUNING T TABLE TS_TUNING 0 6 128 65536 8 6
SQL> alter system dump datafile 6 block min 128 block max 135;
System altered.
In the trace file we notice the 6 transaction slots per block
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x04 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x05 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x06 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
But as said above configuration of non default transaction slots is not often done. 2 transactions slots are created at segment creation time and the Oracle kernel will do the rest.
Additional slots will be created in the block assuming there is space left in the block. But what when more transaction slots are required and there is no more space left in the data block ?
We will examine 3 cases
CASE 1 : 3 Concurrent inserts with 2 ITL slots per block and pctfree 0 and no more free space left
CASE 2 : 3 Concurrent updates with 2 ITL slots per block and pctfree 0 but still some free space left in the blocks
CASE 3 : 3 Concurrent updates with 2 ITL slots per block and pctfree 0 and no more free space left in the blocks
CASE 1 : 3 Concurrent inserts with 2 ITL slots per block and no more free space left.
We will notice that the thirth record will be inserted in another block
SQL> create user ITL identified by ITL default tablespace users;
User created.
SQL> grant create table,create session to ITL;
Grant succeeded.
SQL> alter user ITL quota unlimited on users;
User altered.
SQL> connect ITL/ITL
Connected.
SQL> create table T_ITL (ITL_PK number) pctfree 0 initrans 2;
Table created.
SQL> connect / as sysdba
Connected.
SQL> create user ITL1 identified by ITL1;
User created.
SQL> grant create session to ITL1;
Grant succeeded.
SQL> grant insert on ITL.T_ITL to ITL1;
Grant succeeded.
SQL> create user ITL2 identified by ITL2;
User created.
SQL> grant create session to ITL2;
Grant succeeded.
SQL> grant insert on ITL.T_ITL to ITL2;
Grant succeeded.
SQL> create user ITL3 identified by ITL3;
User created.
SQL> grant create session to ITL3;
Grant succeeded.
SQL> grant insert on ITL.T_ITL to ITL3;
Grant succeeded.
[oracle@pcguy ~]$ sqlplus ITL1/ITL1
SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 22 -- 2012
SQL_ITL1 > insert into ITL.T_ITL values (1);
1 row created.
-- no commit
[oracle@pcguy ~]$ sqlplus ITL2/ITL2
SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 22 -- 2012
SQL_ITL2 > insert into ITL.T_ITL values (2);
1 row created.
-- no commit
[oracle@pcguy ~]$ sqlplus ITL3/ITL3
SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 22 -- 2012
SQL_ITL3 > insert into ITL.T_ITL values (3);
1 row created.
-- no commit
SQL_DBA > select xidusn,xidslot,xidsqn,xid from v$transaction;
XIDUSN XIDSLOT XIDSQN XID
---------------- ---------------- ---------------- ----------------
2 27 6189 02001B002D180000
5 9 6437 0500090025190000
8 19 6120 08001300E8170000
SQL> column PART1 format a10
SQL> column PART2 format a10
SQL> column PART3 format a10
SQL_DBA > select utl_raw.reverse(substr(xid,1,4)) "PART1",utl_raw.reverse(substr(xid,5,4)) "PART2",utl_raw.reverse(substr(xid,9,8)) "PART3" from v$transaction;
PART1 PART2 PART3
---------- ---------- ----------
0002 001B 0000182D
0005 0009 00001925
0008 0013 000017E8
SQL_DBA >select segment_name,header_file,header_block, blocks from dba_segments where owner='ITL';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
BLOCKS
--------------------------------------------------------------------------------- ---------------- ---------------- ----------------
T_ITL 4 30770 8
SQL_DBA > alter system dump datafile 4 block min 30770 block max 30777;
System altered.
Block header dump: 0x01007834
Object id on Block? Y
seg/obj: 0x137ed csc: 0x00.d420d1 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1007830 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.01b.0000182d 0x00c00b71.020c.02 ---- 1 fsc 0x0000.00000000
0x02 0x0005.009.00001925 0x00c12227.02af.11 ---- 1 fsc 0x0000.00000000
bdba: 0x01007834
data_block_dump,data header at 0x7fec0c1b9a64
Block header dump: 0x01007836
Object id on Block? Y
seg/obj: 0x137ed csc: 0x00.d420d1 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1007830 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.013.000017e8 0x00c2e9d8.01e6.18 ---- 1 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x01007836
data_block_dump,data header at 0x7fec0c1b9a64
SQL_DBA > select sid,type,id1,id2,lmode,request,ctime,block from v$lock where v$lock.addr in (select addr from v$transaction );
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- -- ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
10 TX 131099 6189 6 0 12703 0
418 TX 524307 6120 6 0 12665 0
420 TX 327689 6437 6 0 12635 0
SQL_DBA > select username,taddr,v$lock.sid,trunc(id1/power(2,16)) rbs,bitand(id1,to_number('ffff','xxxx'))+0 slot,id2 seq,lmode,request
from v$lock, v$session where v$lock.type = 'TX' and v$lock.sid = v$session.sid; 2
USERNAME TADDR SID RBS SLOT SEQ LMODE REQUEST
------------------------------ ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
ITL1 0000000081D1D190 10 2 27 6189 6 0
ITL2 0000000081EB23E0 418 8 19 6120 6 0
ITL3 0000000081E4BDB0 420 5 9 6437 6 0
SQL_DBA > select username,taddr,sql_address,sql_id,prev_sql_id,sql_address,sql_hash_value,v$lock.sid,trunc(id1/power(2,16)) rbs,bitand(id1,to_number('ffff','xxxx'))+0 slot,id2 seq,lmode,request from v$lock, v$session where v$lock.type = 'TX' and v$lock.sid = v$session.sid;
USERNAME TADDR SQL_ADDRESS SQL_ID PREV_SQL_ID SQL_ADDRESS SQL_HASH_VALUE SID RBS SLOT
------------------------------ ---------------- ---------------- ------------- ------------- ---------------- ---------------- ---------------- ---------------- ----------------
SEQ LMODE REQUEST
---------------- ---------------- ----------------
ITL1 0000000081D1D190 00000000852B9448 brcyn81qbpju2 brcyn81qbpju2 00000000852B9448 1824180034 10 2 27
6189 6 0
ITL2 0000000081EB23E0 00 g6dzgpfw37uxr 00 0 418 8 19
6120 6 0
ITL3 0000000081E4BDB0 00 fnkz6w9nrntzm 00 0 420 5 9
6437 6 0
SQL_DBA > select sql_text from v$sql where sql_id in ('brcyn81qbpju2','g6dzgpfw37uxr','fnkz6w9nrntzm');
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into ITL.T_ITL values (3)
insert into ITL.T_ITL values (1)
insert into ITL.T_ITL values (2)
-- TEST B : We create a table with pctfree 0 and 3 transaction slots
SQL> create table T_ITL_3 (ITL_PK number) pctfree 0 initrans 3;
Table created.
SQL> grant insert on T_ITL_3 to ITL1,ITL2,ITL3;
Grant succeeded.
[oracle@pcguy ~]$ sqlplus ITL1/ITL1
SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 22 -- 2012
SQL_ITL1 > insert into ITL.T_ITL_3 values (1);
1 row created.
-- no commit
[oracle@pcguy ~]$ sqlplus ITL2/ITL2
SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 22 -- 2012
SQL_ITL2 > insert into ITL.T_ITL_3 values (2);
1 row created.
-- no commit
[oracle@pcguy ~]$ sqlplus ITL3/ITL3
SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 22 -- 2012
SQL_ITL3 > insert into ITL.T_ITL_3 values (3);
1 row created.
-- no commit
SQL_DBA > select segment_name,header_file,header_block, blocks from dba_segments where owner='ITL';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS
--------------------------------------------------------------------------------- ----------------
T_ITL 4 30770 8
T_ITL_3 4 30778 8
SQL_DBA > alter system dump datafile 4 block min 30778 block max 30785;
System altered.
Block header dump: 0x0100783c
Object id on Block? Y
seg/obj: 0x137f2 csc: 0x00.d46221 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1007838 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.00e.0000182e 0x00c00b77.020c.19 ---- 1 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x0100783c
data_block_dump,data header at 0x7f3361f4a87c
Block header dump: 0x0100783d
Object id on Block? Y
seg/obj: 0x137f2 csc: 0x00.d46221 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1007838 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.015.000017cf 0x00c2e9e0.01e6.02 ---- 1 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x0100783d
data_block_dump,data header at 0x7f3361f4a87cn
Block header dump: 0x0100783f
Object id on Block? Y
seg/obj: 0x137f2 csc: 0x00.d46221 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1007838 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0009.003.00002939 0x00c0f60b.056e.09 ---- 1 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
SQL> column PART1 format a10
SQL> column PART2 format a10
SQL> column PART3 format a10
SQL> select utl_raw.reverse(substr(xid,1,4)) "PART1",utl_raw.reverse(substr(xid,5,4)) "PART2",utl_raw.reverse(substr(xid,9,8)) "PART3" from v$transaction;
PART1 PART2 PART3
---------- ---------- ----------
0002 000E 0000182E
0008 0015 000017CF
0009 0003 00002939
Even though there are 3 transaction slots per block, the records are in different blocks. ( I think this must be caused by ASSM : Automatic Segment Space Management )
SQL > select rowid,a.* from T_ITL_3 a;
ROWID ITL_PK
------------------ ----------------
AAATfyAAEAAAHg8AAA 1
AAATfyAAEAAAHg9AAA 3
AAATfyAAEAAAHg/AAA 2
SQL> select dbms_rowid.rowid_block_number(rowid) "BLOCK" from T_ITL_3;
BLOCK
----------------
30780
30781
30783
How to use dbms_rowid ?
CASE 2 : 3 Concurrent updates with 2 ITL slots per block and pctfree 0 but still some free space left.
We will notice that as long as there is some free space in the block the ITL list will expand ie an additional transaction slot will be created.
We insert 3 records in the same block.
SQL> truncate table T_ITL;
Table truncated.
SQL> select table_name,ini_trans from user_tables where table_name='T_ITL';
TABLE_NAME INI_TRANS
------------------------------ ----------------
T_ITL 2
SQL> insert into T_ITL values (1);
1 row created.
SQL> insert into T_ITL values (2);
1 row created.
SQL> insert into T_ITL values (3);
1 row created.
SQL> commit;
Commit complete.
SQL> select rowid from T_ITL;
ROWID
------------------
AAATi/AAEAAAHg2AAA
AAATi/AAEAAAHg2AAB
AAATi/AAEAAAHg2AAC
SQL> select dbms_rowid.rowid_block_number(rowid) from T_ITL;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
30774
30774
30774
SQL> alter table T_ITL add ITL_TEXT varchar2(100);
Table altered.
SQL> grant update on T_ITL to ITL1,ITL2,ITL3;
Grant succeeded.
[oracle@pcguy ~]$ sqlplus ITL1/ITL1
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 7 -- 2012
SQL_ITL1 > update ITL.T_ITL set ITL_TEXT=dbms_random.string('U',100) where ITL_PK=1;
1 row updated.
-- no commit;
[oracle@pcguy ~]$ sqlplus ITL2/ITL2
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 7 -- 2012
SQL_ITL2 > update ITL.T_ITL set ITL_TEXT=dbms_random.string('U',100) where ITL_PK=2;
1 row updated.
-- no commit;
[oracle@pcguy ~]$ sqlplus ITL3/ITL3
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 7 -- 2012
SQL_ITL3 > update ITL.T_ITL set ITL_TEXT=dbms_random.string('U',100) where ITL_PK=3;
1 row updated.
-- no commit;
SQL> column segment_name format a10
SQL> column partition_name noprint
SQL> column owner format a10
SQL> set linesize 300
SQL> select * from dba_extents where segment_name='T_ITL' and owner='ITL';
OWNER SEGMENT_NA SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
---------- ---------- ------------------ ------------------------------ ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
ITL T_ITL TABLE USERS 0 4 30768 65536 8 4
SQL> alter system dump datafile 4 block 30774;
System altered.
Object id on Block? Y
seg/obj: 0x138bf csc: 0x00.20463bd itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1007830 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.006.00007b98 0x00c0035b.02ba.02 ---- 1 fsc 0x0000.00000000
0x02 0x0007.01e.00007cb1 0x00c01921.02d8.03 ---- 1 fsc 0x0000.00000000
0x03 0x0004.017.00008244 0x00c0523f.0326.06 ---- 1 fsc 0x0000.00000000
Joining with v$transaction, v$session, v$sql can be done similar to what was shown here above ( see examples in case 1 )
How to use dbms_rowid ?
CASE 3 : 3 Concurrent updates with 2 ITL slots per block and pctfree 0 and no more free space left in the blocks.
We will notice that when there is no more free space left in the block the thirth session hangs with wait event "enq: TX - allocate ITL entry"
SQL> connect ITL/ITL
Connected.
SQL> truncate table T_ITL;
Table truncated.
SQL> begin
2 for i in 1 .. 100000 loop
3 insert into T_ITL values (i,dbms_random.string('U',100));
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select ITL_PK,dbms_rowid.rowid_block_number(rowid) from T_ITL where ITL_PK in (1,2,3);
ITL_PK DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------------- ------------------------------------
1 30773
2 30773
3 30773
[oracle@pcguy ~]$ sqlplus ITL1/ITL1
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 7 -- 2012
SQL_ITL1 > update ITL.T_ITL set ITL_TEXT=dbms_random.string('U',100) where ITL_PK=1;
1 row updated.
-- no commit
[oracle@pcguy ~]$ sqlplus ITL2/ITL2
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 7 -- 2012
SQL_ITL2 > update ITL.T_ITL set ITL_TEXT=dbms_random.string('U',100) where ITL_PK=2;
1 row updated.
-- no commit
[oracle@pcguy ~]$ sqlplus ITL3/ITL3
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 7 -- 2012
SQL_ITL3 > update ITL.T_ITL set ITL_TEXT=dbms_random.string('U',100) where ITL_PK=3;
This transaction hangs with waitevent "enq: TX - allocate ITL entry" ( Oracle 11G ), or ITL waits ( Oracle < 11G )
SQL_DBA > select username,event from v$session where username is not null order by 1;
USERNAME EVENT
------------------------------ ----------------------------------------------------------------
ITL1 SQL*Net message from client
ITL2 SQL*Net message from client
ITL3 enq: TX - allocate ITL entry
SYS SQL*Net message to client.
Joining with v$transaction, v$session, v$sql can be done similar to what was shown here above ( see examples in case 1 )
How to use dbms_rowid ?