Init trans

When creating table, we can specify initrans, the default value is 1, what is initrans for?

Does it have any side effect when multiple program are trying to update the table at the same time?
ymlewAsked:
Who is Participating?
 
poncejuaConnect With a Mentor Commented:
The INITRANS parameter specifies the initial number of transaction entries allocated within each data block allocated to the table.

You can set it from 1 to 255 and its default is 1. Oracle highly recommend to avoid changes to its default.

Each transaction that updates a block requires a transaction entry in the block. The size of a transaction entry is operating system dependent.

This parameter ensures that a minimum number of concurrent transactions can update the block and helps avoid the overhead of dynamically allocating a transaction entry.

On the other hand, MAXTRANS specifies the maximum number of concurrent transactions that can update a data block allocated to the table. This limit does not apply to queries. This value can range from 1 to 255 and the default is a function of the data block size. You should not change the MAXTRANS value from its default.

If the number concurrent transactions updating a block exceeds the INITRANS value, Oracle dynamically allocates transaction entries in the block until either the MAXTRANS value is exceeded or the block has no more free space.

The quantity of transaction entries may vary from INITRANS value to MAXTRANS value.
0
 
ymlewAuthor Commented:
Edited text of question.
0
 
ymlewAuthor Commented:
Adjusted points to 200
0
 
mshaikhCommented:
Now, for your case, if there are a lot of updated that will take place on a particular table. You should set INITRANS higher. This is because, if the number concurrent transactions updating a block exceeds the INITRANS value, Oracle dynamically allocates transaction entries in the block until either
1..  the MAXTRANS value is exceeded or,
2..  the block has no more free space.

Both of these case as bad for perfomance. Because, there is an overhead associated with dynamically allocating a transaction entry. And, lack of free space will cause the transaction to wait for an existing inuse transaction slot(ITL) becomes free. Moreover, you may get Snapshot too old messages if you have reused a ITL and another long running query need rollback info on that block.
0
 
ymlewAuthor Commented:
Thanks for all the explanation.
0
All Courses

From novice to tech pro — start learning today.