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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ymlewAuthor Commented:
Edited text of question.
0
ymlewAuthor Commented:
Adjusted points to 200
0
poncejuaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.