Posted by INITRANS defines how many concurrent transactions you can have within a single datablock for a segment (table, materialized view, etc).
A quick perusal of the documentation for INITRANS and you’ll see that it defaults to 1.
We can easily verify this by creating a table and checking the data dictionary.
SQL> create table t ( id int, x char(5) );
Table created.
SQL> select ini_trans
2 from user_tables
3 where table_name = 'T';
INI_TRANS
----------
1
You may have heard the term “ITL” which stands for Interested Transaction List. INITRANS defines how many entries are initially present in this list of interested transaction, thus forming the cap on concurrent transactions for this block. But that cap is flexible. If a block has some free space in it, then we are allowed to consume some of that free space for additional concurrent transactions in the block, and (free space permitting) go all the way up to 255 concurrent transactions.
But what if there was no free space at all in a block? If I have consumed all of the free space, and the INITRANS value is 1, then theoretically I should not be able to initiate two transactions on the same block because I’d need room for a second transaction entry in the block and there is no space for it.
Lets see what happens when I try this experiment with a table. I’ll set PCTFREE 0 to allow the block to be completely filled and set INITRANS explicitly to 1. Then I will load the table with enough data to at least fill a single block.
SQL> create table t ( id int, x char(5) ) pctfree 0 initrans 1;
Table created.
SQL> insert into t
2 select rownum, 'x'
3 from dual
4 connect by level <= 5000;
5000 rows created.
SQL> commit;
Commit complete.
Based on my hypothesis, starting the first transaction in this block should be fine, and indeed it is.
SQL> update t
2 set x = 'y'
3 where id = 1;
1 row updated.
In a new session, I’ll now start a second transaction in this block. Because INITRANS is 1, and there is no space in the block to allow growth of the ITL, I am expecting it to block:
SQL> update t
2 set x = 'y'
3 where id = 2;
1 row updated.
And lo and behold it does not! Let me try a third session with a third transaction on this block.
SQL> update t
2 set x = 'y'
3 where id = 3;
[waiting]
And only now do we see the transaction fail to start because I could not find space for the ITL to grow. Going back to session 1 and committing the UPDATE allows this third session to commence its transaction.
--
-- Session 1
--
SQL> commit;
Commit complete.
--
-- Session 3
--
SQL> update t
2 set x = 'y'
3 where id = 3;
1 row updated.
This suggests that INITRANS is not 1 on this block. Perhaps the experiment is flawed, and the rows with ID=1,2,3 were not all on the same block, but we can validate this easily enough with DBMS_ROWID:
SQL> select distinct dbms_rowid.rowid_block_number(rowid)
2 from t
3 where id in (1,2,3);
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
156
All three rows occupy the same block, so it is time to dig a little deeper. We can grab the file number as well from the rowid and get a symbolic dump of the block to a trace file.
SQL> select dbms_rowid.rowid_relative_fno(rowid)
2 from t
3 where id = 1;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------
64
SQL> alter system dump datafile 64 block 156;
System altered.
Once we scroll down past the hex dump of the data, we get to the info that shows the size of the ITL in this block, and even with our INITRANS set to 1, the database seems to always give us 2!
Block header dump: 0x1000009c
Object id on Block? Y
seg/obj: 0x3ba672 csc: 0x00000eda50a8cb90 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x10000098 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.017.0004911c 0x02c01250.2e87.31 C--- 0 scn 0x00000eda50a8ca98
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x1000009c
data_block_dump,data header at 0x7f856064
===============
tsiz: 0x1f98
hsiz: 0x452
pbl: 0x7f856064
76543210
flag=--------
ntab=1
nrow=544
frre=-1
fsbo=0x452
fseo=0x460
avsp=0xe
tosp=0xe
0xe:pti[0] nrow=544 offs=0
0x12:pri[0] offs=0x130a
0x14:pri[1] offs=0x1316
0x16:pri[2] offs=0x1322
0x18:pri[3] offs=0x132e
0x1a:pri[4] offs=0x133a
0x1c:pri[5] offs=0x1346
I’m sitting on the fence as to whether I would call this a documentation bug, because the docs say 1 and the data dictionary says 1, so they are indeed in alignment. It is just that under the covers, we’ve now picked 2 as our minimum value.
An obvious follow-up experiment is to see if INITRANS values greater than 1 (or 2) are still observed by the database, so we can repeat the exercise with a higher value.
SQL> create table t ( id int, x char(5) ) pctfree 0 initrans 5;
Table created.
SQL> insert into t
2 select rownum, 'x'
3 from dual
4 connect by level <= 5000;
5000 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> select dbms_rowid.rowid_block_number(rowid)
2 from t
3 where id = 1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
158
SQL>
SQL> select dbms_rowid.rowid_relative_fno(rowid)
2 from t
3 where id = 1;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------
64
SQL>
SQL> alter system dump datafile 64 block 158;
System altered.
Block header dump: 0x1000009e
Object id on Block? Y
seg/obj: 0x3ba71e csc: 0x00000eda50a8d334 itc: 5 flg: E typ: 1 - DATA
brn: 0 bdba: 0x10000098 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.01a.00049110 0x02c0125e.2e87.2b --U- 540 fsc 0x0000.50a8d338
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
bdba: 0x1000009e
data_block_dump,data header at 0x770020ac
===============
tsiz: 0x1f50
hsiz: 0x44a
pbl: 0x770020ac
76543210
flag=--------
ntab=1
nrow=540
frre=-1
fsbo=0x44a
fseo=0x44c
avsp=0x2
tosp=0x2
Should this be any cause for alarm? Doubtful, because it is so rare to see people set these values to anything but their defaults anyway nowadays, and you’d need to have a very niche circumstance where you’re expecting concurrent transactions on the same block to such a level that the default PCTFREE meant you could not expand the ITL.
Luckily, the great instrumentation inside the Oracle Database means you can simply monitor for event “enq: TX – allocate ITL entry” in your AWR or Statspack reports to see if you’ve hit one of these rare scenarios.
But as far I can tell, INITRANS is no longer 1 for any segment.
Learning is not a spectator sport 
Hi Connor, nice demo – thank you!
I was working on a system having been in production for some 20 years, without any data ever being deleted.
Once we implemented delete routines, we ran into this problem: Deadlocks on ITL waits coming from 8 threads deleting data in parallel using dbms_scheduler, and most/many blocks full.
We ended up moving/rebuilding a good handful of tables and some 20 indexes, setting initrans=8 in the process. During investigations of the problem we came to the very same conclusion, there are by default 2 ITL slots for tables, not just 1.
Lately, I have seen a similar problem. Some application being fond of Transaction Isolation Level Serializable. Turned out that when using Serializable, a 3rd ITL slot is needed. Luckily we managed to persuade the developers to abandon that (They could not explain why it was needed), so that we avoided rebuilding all tables and indexes 🙂
Best regards
Peter G
Hi Connor, The spelling mistake in the title caught me off guard , I was wondering what is INTRANS ? Hope you fix it…. Do I get a timtam ?
Thank you for spotting it 🙂 It will be fixed.
I can’t promise Tim Tams 🙂