a close up of a sleeping dog with its tongue hanging out

INITRANS grabs a little more space

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.

image

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.

initrans

3 comments

  1. 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

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.