3

I need to calculate a tablespace size and part of the calculation requires a value for INITRANS which I don't fully understand and I wasn't sure if I should just use the default value 2.

My business rules specify that data is inserted from up to 50 different locations so I am to assume some concurrency, is this an indication that I should use an INITRANS of 50 or am I misunderstanding what INITRANS is?

  • how does inittrans influence your tablespace size? – miracle173 Aug 25 '15 at 20:52
8

As always: it depends. However in most cases the default values are adequate.

The business requirement of handling 50 concurrent inserts (transactions) alone does not imply the need for setting INITRANS 50. Are they really that concurrent? Will the duration of the transactions overlap each other so they really happen at the same time? All the 50?

INITRANS specifies the minimum number of transaction slots (ITL slots) in a database block. Of course, the number of ITL slots can go higher if needed, the database manages that. The maximum number depends on the database block size (as the ITL entries are not allowed to occupy 50% of more of a database block, and 1 ITL entry takes 24 bytes), and MAXTRANS (=255 from version 10.2).

So, if:

  1. all the new data end up in the same database block (not necessarily a table, a common example is a PK index on an ID column, where the ID values come from a sequence)
  2. changes are not committed immediately (or quick enough), so locks will be held for a long time, and all the 50 sessions will hold the locks at the same time

then yes, the database block will need the 50 ITL slots. But even then, most likely you will not need to set INITRANS to 50, because the database will manage this automatically, and 50 is well below the actual limit in a standard 8K database block.

This may not be true for DELETE, UPDATE, SELECT ... FOR UPDATE, as when they happen, the database block is most likely full and the number of ITL slots may not increase in that case. Imagine a full database block that stores 50 rows and has 2 ITL slots, and 50 concurrent transactions want to lock 1 row each with SELECT ... FOR UPDATE. But with INSERT, that uses empty database blocks or continues writing to existing ones with free space in them, it should not be a problem.

Note that there are many ifs, mays and questions here, and as I said, in most of the cases, the default values are adequate. Yes, I can create an artificial example that works as above, but real scenarios are fortunately different.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.