Pctfree, pctused, initrans, and maxtranx parameters in Oracle

1. pctfree

To describe the operation of a block, we can think of a block as a water cup. The waiter poured the water into the water cup in front of us. How full is it? We asked him to pour 9 full points. At this time, pctfree stands for setting it to 10, which means, when the block reaches 90%, it cannot be used. The block should be removed from the freelist List (UN-link ). Why keep 10% of the space? This is to provide the space that may be added when data is updated. If the space is too small, row chaining may occur.

2. pctused

Pctused indicates when the water can be added. If pctused is set to 40, the waiter will know that water needs to be added when the water in the water cup is less than 40%. You can think about it. If you say that every time you have a mouthful of water in the restaurant, you will not feel very annoying. For the restaurant, you will also need to send many people to help every table of guests with water, can this business be done? Therefore, pctused represents the significance of re-link returning to freelist. If pctused is too large, for example, if it is better than 70, it means that you will need to add water if you just take a sip of water, this implies that the utilization of the Cup increases, but the frequent service of the waiter causes the load.
(I/O overhead ). Pctused is a little smaller, for example, 10, which means that when the water reaches the remaining 10% (like the delete transaction operation), freelist needs to be put back, which means that the water can be added (like the insert transaction operation ).

Simply put, if you want to maximize the efficiency of the storage space, you can set pctused to a larger value. On the contrary, if you want to improve Io performance, set pctused to a smaller value.

Freelist stores available block information. When the block is deleted to pctused, it is placed on freelist again to allow other transactions to use new data. Freelist is stored in the segment header of table/index, which manages all block information that can be added to Data. Think about it. If multiple users want to add data and ask freelist to provide usable block information, will freelist become another overhead? Therefore, it should be adjusted according to the table usage requirements.
The number of freelist is also a key point of Io tuning.

This parameter is no longer used in assm, and only pctfree takes effect.

3. initrans

Initrans refers to the space on a block that is initially pre-allocated to the parallel transaction control (Itls)

(When a row on the block is updated by the transaction lock, a lock will be placed in the block header ITL allocate. When the next transaction wants to update the same row, it will find that he has been locked by the previous transaction, and will first check whether the transaction is active? If yes, then the transaction will be blocking, wait) If a table needs to have a large number of transaction access at the same time, you should set initrans to a large value to reduce the overhead that ITL needs to dynamically expand.

For tables initrans defaults to 1 for indexes 2

4. maxtrans

Maxtrans means that if the initrans space is insufficient, the ITL will be automatically extended until the maximum value is the maxtrans value, which is set to 255 by default. However, if the block space is insufficient, it may not be able to expand to 255 its space.