INITRANS questions

[Former Member]

Hi all,

In our SAP databases we have 7 tables plus the indexes that have the INITRANS set to 100. Looks like this setting was update in order to avoid deadlocks and appliying the SAP note 84348.

We have tried to figured out why, how and when this setting was updated but we haven't have success. Can be done years ago and it is not documented in any place (Change management issue ).

The tables that have the INITRANS to 100 are "couriously" the biggest tables and the tables with the biggest growth trend, thing that makes a lot of sense because of this setting.

Two of the tables are MSEG and MKPF (material documents) and as per our application team, these tables should have any UPDATE process against. In addition in the Share Cursor Cache and DB statistics there isn't any update operation aginst these tables in the last months.

With these circumstances and In order to reduce the space after a very deep archive process we are thinking about to reorg the tables reducing the INITRANS to 1.

Here several questions and thoughts regarding this topic:

• Does the INITRANS apply only for UPDATE operations? Im my eyes yes, but I'd like to confirm it.
• If one table has the INITRANS to 100 during long time and after that you reorg the table reducing the INITRANS to 100, should the table size be reduced? this is my thought and please let me know if I'm wrong:

  if a table with 1000 blocks and INITRANS = 100  is reorganized and the INITRANS reduced to 1, will its size reduced in 207000? ((23bytes*100)*1000) - (23bytes *1000) = 207.000 bytes.

• If the INITRANS is changed in a table without reorg and after sometime the table is reorg keepping the INITRANS, will its size increased because the INITRANS will be applied to all blocks?

Thanks in advanced.

[stefan_koehler]

Hi Jose,

> Does the INITRANS apply only for UPDATE operations? Im my eyes yes, but I'd like to confirm it.

No. An ITL entry is needed for every kind of change to the data block (DML).

interested transaction list (ITL)
Information in the block header of every segment that determines whether a transaction was uncommitted when the database began modifying the block. Entries in the ITL describe which transactions have rows locked and which rows in the block contain committed and uncommitted changes.

> With these circumstances and In order to reduce the space after a very deep archive process we are thinking about to reorg the tables reducing the INITRANS to 1.

Even if you set INITRANS to 1 - 2 ITL slots are created by default. (Check my comment on this blog here: http://scn.sap.com/community/oracle/blog/2007/11/29/chasing-oracle-deadlocks-part-ii--block-level-lo...)

> if a table with 1000 blocks and INITRANS = 100  is reorganized and the INITRANS reduced to 1

As far as i can remember - one ITL slot needs 24 bytes (would need to do a short test to be absolutely sure, but let's assume this size). INITRANS = 1 creates 2 ITL slots and so you will initially save (100 * 24 bytes) - (2 * 24 bytes) = 2352 bytes per block. ITL slots can be created dynamically (if needed) as long as there is enough space in the block.

> If the INITRANS is changed in a table without reorg and after sometime the table is reorg keepping the INITRANS, will its size increased because the INITRANS will be applied to all blocks?

You need to reorg your table, if you want to reduce the initial ITL slots. INITRANS ITL slots are created by formatting the data block.

The easiest way would be to perform some block dumps and cross check how many ITL slots are really needed / used.

Regards

Stefan

[Former Member]

Hi Stefan,

Thanks for your response.

Checking the tables statistics I can only see SELECTS and INSERTS operations. I cannot see ITL waits on these tables as well.

If I understand correctly, a new ITL is only needed if the same block is modified by another process, I mean, if you have 2 ITLs and there are 3 processes updating the same block a new ITL will be created for this block till you have enough free space in the block header (depending on the PCTFREE) ...

My doubt is if with several INSERTS in parallel we can face the deadlock problem.  For updates is very clear for me that it can happens but I'm not sure with INSERTS. What do you think?

Regarding the reduction, last weekend we have reduced the INITRANS from 100 to 1 and reorg the tables in a test environment but the result hasn't been the expected. The size has been reduced but very few. Is also true that we don't know when the INITRANS was changed and how (with or without reorg).

Honestly is very difficult to found something with block dumps because these tables have 64642928 and 5352312 blocks and I don't know which blocks were updated.

Cheers.

Jose

[stefan_koehler]

Hi Jose,

> I cannot see ITL waits on these tables as well.

Well i hope so, if you have 100 ITL entries ... otherwise you would have a really high parallel DML load

> For updates is very clear for me that it can happens but I'm not sure with INSERTS. What do you think?

An INSERT statement needs  an ITL slot as well (and if several different sessions are inserting data into the same block - several ITL slots are needed). You can also have deadlocks by INSERT statements (just think about foreign keys, which are not used in SAP environments, but you can have such issues with unique values as well).

> Regarding the reduction, last weekend we have reduced the INITRANS from 100 to 1 and reorg the tables in a test environment but the result hasn't been the expected.

Then maybe some other influencing factors kick in or your expectations were based on some wrong facts.

> Honestly is very difficult to found something with block dumps because these tables have 64642928 and 5352312 blocks and I don't know which blocks were updated.

Well this is pretty easy to determine - just use the pseudo columns ora_rowscn and rowid to get the (latest) updated blocks and crosscheck a few of these. This should be just a tiny average cross check of several blocks and not a scientific result (for which ora_rowscn is not good at all).

Regards

Stefan

[Former Member]

Hi,

I forgot another question ....

• Is there any way to see when the INITRANS was changes at DB level.

thanks again.