ymlew
asked on
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?
Does it have any side effect when multiple program are trying to update the table at the same time?
ASKER
Adjusted points to 200
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Thanks for all the explanation.
ASKER