Overview

The INITRANS parameter is a physical property parameter that sets the initial number of Transaction Entries (ITL Entries) in the Data Block Header.

If the INITRANS value is 10, space for 10 concurrent transactions is initially allocated.

This parameter is used in the DDL statements of tablespaces, tables, and indexes to specify the physical properties of storage space.

Method

 create table tibero.test (col1 number, col2 char(5)) pctfree 0 initrans 2;
 
 create table tibero.test_itl5 (id number, name varchar(20), address varchar(20)) pctfree 0 initrans 5;

Each time a transaction is performed in a block, a Transaction Entry in the header is consumed, and INITRANS specifies the initial value for this.

When multiple transactions operate on a single block, each transaction consumes its own entry. The more transactions are performed simultaneously, the more entries are used.

If all entries allocated by INITRANS are consumed initially, additional entries must be allocated, and these additionally allocated entries are not released afterward.

The allocation of ITL Entries uses the free space in the block.

In other words, INITRANS can be understood as the initial value for the number of transactions that can access the same block concurrently.

When There Are Few Concurrent Transactions in the Same Data Block

• Since it is unlikely that multiple Transaction Entries will be used simultaneously, it is acceptable to set a low INITRANS value in this case.

• In this case, a low INITRANS value has the advantage of saving block space.

   

When There Are Many Concurrent Transactions in the Same Data Block

• Multiple Transaction Entries are used simultaneously, and if the INITRANS value is low, you must allocate additional Transaction Entries that exceed the INITRANS count. This may cause additional CPU overhead.

• In such cases, it is recommended to set the INITRANS value somewhat higher.

• However, if you increase the INITRANS value, the actual usable space in the block decreases by that amount.

Tibero INITRANS Default Value

The default INITRANS values in Tibero are as follows.

• In general, the default INITRANS value for tables is 2.

• The default INITRANS value for clusters and indexes is 2.

• MAXTRANS is set to 255.

You can change this with the following query.

  alter table TABLE_NAME maxtrans 10;

Recommendations

It is recommended to use the default value for INITRANS.
The cost of allocating additional entries is not significant, and CPU usage does not increase greatly.

However, if multiple transactions access the same block, a certain level of load may occur as entries are dynamically allocated.

Although the likelihood of this load occurring is low, it is a parameter that should not be overlooked depending on the nature of your workload.

Note

Tibero7 Online Manual SQL Reference Guide