All About Oracle Database Block

 INITTrans 

Whenever a DML transaction wants to modify a block , it should inform the block by sending 'interested to modify' message to the block .Technically speaking, a dml transaction will always send message to the block , that it is interested to modify the block . This message is stored in ITL slots ,which is preallocated in the block header. Transactions whose entry is there in this ITL slots can only modify the block.This ITL slots are controlled by INITRANS and MAXTRANS values, the inittrans controls the minimum number of slots that will be allocated in the block header. Honestly speaking, the number of ITL slots is equal to number of DML transactions that can happen parallely at one time. Each ITL slot occupies 24 bytes of space in the block. Inittrans values is 1 for table and 2 for index by default.

MAXTrans

Maxtrans is the maximum number of ITL slots that can be allocated in the block . When the block runs out of this ITL slots and there is enough free space in the block , the inittrans value will be increased to the value specified in maxtrans. But, when there is no free space in block and dml transactions are waiting for free ITL slots, then those sessions will undergo wait event 'enq: TX - allocate ITL entry' and the sessions will be serialized .

PCT_FREE

Pct_free points to the amount of free space that a block will allocate to assist space for new updates.Insert operations cannot use this pct_free space for its data . Usually, the pct_free is 10% or 20%.

PCT_USED

When the amount of space usage has reached pct_free, then the block is not considered for newer inserts. The remaining space is only used for updates in the block.The block will be used for newer inserts, only when the space consumption comes below pct_used.

From the above picture, we can clearly see that the box with brown colour represents used space, box with white colour represents empty space available for inserts. The box with yellow colour represents empty space dedicatedly allocated for updates . when the brown colour reaches the upper boundary of pct_used box, then there is no free space in the block for new data inserts, the remaining 20% space is only for updates for the existing data.  In this situation, this block is not considered for insert operations, later when the used space(i.e brown colour) comes below lower boundary of pct_used, then this block will be considered for new inserts.

PCT_FREE and PCT_USED are the values mentioned in % percentile of the block space i.e 10% or 20%

FREELists

Freelists is the list of blocks whose space consumption is below pct_free . Whenever new insert transactions are triggered on the segment, this freelists will be read and the blocks will be allocated for data loading accordinly.

FREELists Groups

When multiple dml transactions are triggered on the segment, then only one transaction at a time has access to freelists information. So other transactions should wait for the access to freelists . To avoid this all the blocks whose space consumption is below pct_free are grouped into multiple groups called freelists groups. Each freelists group will be assigned to a different transaction, so that transactions no need to wait for access to this list, when many transactions triggered at same time on a segment. The transaction cannot access other freelists than the allocated one.

High Water Mark

High Water Mark is the boundary between used and unused space in the block. Whenever space consumption has reached a certain level in the block which is peak in its life time.Then even if we delete some data, the used space does not come down i.e HWM does not come down. Impact of this is the table grows on continously and also queries with full table scan running on this tables need to scan all the space which is used and unused space.

DBA should always concentrated on bringing down the HWM of a table, which can be done through datapump or dbms_redifinition package.

We can find the unused space in the segment using package 'Dbms_Space.Unused_Space'