11 | 12 | 2017
Latest Articles
Popular Articles

Administration

About Transaction Slots, initrans and v$transaction

User Rating:  / 1
PoorBest 

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 ?