INITRANS

is there any computation regarding how to properly size the initrans of a table....

your view are very much appreciated

INITTRANS can be allocated for a table based on the following criteria:

http://www.dbasupport.com/forums/images/icons/icon2.gif the space you would like to reserve for transaction entries on the table
http://www.dbasupport.com/forums/images/icons/icon2.gif the number of concurrent transactions that access the same table at any given time

For example, if a table is very large and only a small number of users simultaneously access the table, then, INITRANS can be kept low.

On the other hand, if a table is accessed by multiple users at the same time. use a high INITRANS

tnx for the answer but can u give some figures like, for every 20 concurrent updates , 10 concurrent deletes, and 30 concurrent inserts what would be the most appropriate value for the initrans or maybe a ratio like 1 initrans is to 20 concurrent inserts something like that......

tnx

can someone advice me on this one?

Hi,

INITTRANS --> Number Of transaction slots initially allocated to a BLOCK.

So, this parameter is at block level not table. IMO there
is no fix ratio for concurrent user to INITTRANS. Oracle will allocate slots as necessary upto MAXTRANS during
concurrent operations on block. If the block is full and
INITTRANS was set too low, new slot can not be allocated
and sessions will wait for the Block.

HTH

np70

An example of INITTRANS & MAXTRANS could be 3 & 255 respectively.

What is the result of setting INITTRANS too high?

Thanks in advance.

As I know each transaction entrie takes 26 bytes from block, so you may calculate if your block size 2K and INITRTRANS 20. Free space is 2048 - 26*10 - (pctfree%???) = ???

Quote:

---

Originally posted by dknight
What is the result of setting INITTRANS too high?
Thanks in advance.

---

Usualy for indexes INITTRANS more than for tables, for ex. 2 - table, 4 - index.

INITTRAN parameter can be set to high value ( not more than 10) if you are sure that concurrent insert and update takes place on that table. Many developers think that Web based application inserts/updates a particular table heavily and so the table needs to have a very high INITTRAN value. But it is not true. I have observed most of transactions take place serially unless the box has more than 4 CPU. A high value of INITTRAN needs more space in the block header which I feel mere waste.

Study your application carefully and experiment with sample data, then increase the value to a desired one, but not more than 10. Setting this value too high will not yield performance.

You might also benefit from increasing FREELISTS from the default.
Start with FREELISTS at 2 and see whether your concurrent inserts are still locking.

This is a locking contention issue, so getting familar with the locking mechanism and reporting lockers/blockers will help determine if your settings for INITRANS and FREELISTS are too low.

Have Fun

Yes, freelists might help but mostly in OPS, i.e., when several instances access the same table. And consider then not only FREELISTS but also FREELIST GROUPS.

About INITRANS, if you are familiar with the ORA-08177 error, then the INITRANS problem is not new to you. It occurs when the transaction has to reread the index block and try to generate a consistent read version of that index block.

Some claim that high INITRANS would help, I have seen from practice that .. not always. Oracle says it is some sort of a bug.