INITRANS AND MAXTRANS

I WANT TO UNDERSTAND IN VERY SIMPLE WAY WHAT IS INITRANS AND MAXTRANS AND HOW IT WORKS?


THANKS IN ADVANCE
SHAILENDRA

INITRANS AND MAXTRANS

Initial and Maximum transactions allowed to read/write to the block concurrently

----------------------------------------------------------

INITRANS specifies the number of DML transaction entries for which space is initially reserved in the data block header. Space is reserved in the headers of all data blocks in the associated segment. As multiple transactions concurrently access the rows of the same data block, space is allocated for each DML transaction’s entry in the block. Once the space reserved by INITRANS is depleted, space for additional transaction entries is allocated out of the free space in a block, if available. Once allocated, this space effectively becomes a permanent part of the block header. The MAXTRANS parameter limits the number of transaction entries that can concurrently use data in a data block. Therefore, you can limit the amount of free space that can be allocated for transaction entries in a data block using MAXTRANS.

The INITRANS and MAXTRANS parameters for the data blocks allocated to a specific schema object should be set individually for each schema object based on the
following criteria:

The space you would like to reserve for transaction entries compared to the space you would reserve for database data

The number of concurrent transactions that are likely to touch the same data blocks at any given time

For example, if a table is very large and only a small number of users simultaneously access the table, the chances of multiple concurrent transactions requiring access to the same data block is low. Therefore, INITRANS can be set low,
especially if space is at a premium in the database.
Alternatively, assume that a table is usually accessed by many users at the same time. In this case, you might consider preallocating transaction entry space by using
a high INITRANS. This eliminates the overhead of having to allocate transaction entry space, as required when the object is in use. Also, allow a higher MAXTRANS so that no user has to wait to access necessary data blocks.

HTH

Sameer

[Edited by Sameer on 08-26-2002 at 06:42 AM]

THANKS

INITRANS and MAXTRANS are used when you expect multiple access to the same data block.

Every transaction which modifies a block must acquire an entry in the Interested Transaction List (ITL). Space for this list is defined by INITRANS. The ITL grows dynamically as needed by transactions up to the value MAXTRANS. It also shrinks back down to the setting for INITRANS.

INITRANS
The default value is 1 for tables and 2 for clusters and indexes.

MAXTRANS
The default value is an operating system-specific function of block size, not exceeding 255.

HTH.