PCTFREE , PCTUSED ,INITRANS & MAXTRANS
PCTFREE , PCTUSED
The datafiles are the physical storage space on the server. Storage in the datafiles is allocated by blocks to each extent that is used by an object. The block size is operating system-dependent and is determined when the database is created and cannot be changed. The database block size is set in the database parameter file DB_BLOCK_SIZE. Typically, a database block is either 2K,4K or 8K in normal system and 16k or max in exadata system.
PCTFREE : parameter specifies the percentage of space in each segment's data block reserved for future record expansion into the block.
If records within a block are rarely updated after they are inserted, you should set PCTFREE lower to allow for full space usage. If records within a block are subject to many updates, you should set PCTFREE higher to allow for more growth.
Once the PCTFREE threshold is reached, no additional rows will be inserted until the PCTUSED threshold is reached due to deletion of data. If PCTFREE is set too low, row chaining and row migration will result because updates to the record will not fit into the block.
After that point, no new rows can be inserted into that block until the free space percentage falls below the PCTUSED threshold. The default for PCTFREE is 10%.
The PCTFREE and PCTUSED parameters tell Oracle when to link and unlink a block from the freelist chain. This is only relevant if you are not using the new Automatic Segment Space Management .
PCTUSED : parameter defines the minimum percentage of data block used space that is necessary before the block is eligible for row insertion. A segment block is added to the free space list once its used space falls below this threshold. The default for PCTUSED is 40%.
If data is static, you will be able to set the PCTUSED lower and more fully use the space. If large amounts of data are inserted and deleted, you should set the PCTUSED higher to prevent block fragmentation.
You might set these parameters as follows: CREATE table emp (......) PCTFREE 5 PCTUSED 80.
The combined sum of PCTFREE and PCTUSED must be less than 100. Correct choice of these parameters can be used to improve the efficiency of table and index segments.
For example, tables that are insert-only (auditing tables, history tables, etc.) should have a PCTFREE setting of 1.
This simple change to a table's storage definition can reduce its total disk space requirements by 10%.
This means less object extension and less physical disk I/O when reading and writing: 10% more data can be read or written with a single data block read; 10% more data can be held in the database's SGA.
This is a very simple way of increasing your block buffer cache without having to buy any more memory.
Initial and Maximum transactions allowed to read/write to the block concurrently
----------------------------------------------------------
INITRANS : specifies the number of DML (INSERT, UPDATE, DELETE, and SELECT) 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
Every transaction which modifies a block must acquire an entry in the Interested Transaction List (ITL) in block. Space for this list is defined by INITRANS. The space required for Each and every ITL entry is operating system dependent; however, ITL entry in most operating systems requires approximately 23 bytes.
The database block size plays an important role in allocating the number of inital ITLs for the blocks.
The rule is "the total size allocated for initial ITLs SHOULD be LESS THAN 50% of the database block size"
ie : sizeof(INITIAL ITLs) < ( 50 % of the DATABASE BLOCK SIZE )
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.
Example 1:
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.
Example 2 :
Let's say you have a block that has 50 records and 5 people simultaneously trying to update 5 records in the block. Let's say INITRANS parameter value is 1 for this block, therefore, at a time only one update will proceed and others update will wait. If INITRANS parameter value increase to 5 then at a time 5 record will update concurrently. Those will boost up the performance.
Short Note:
The INITRANS setting controls Initial Transaction Slots (ITLs). A transaction slot is required for any session that needs to
modify a block in an object. For tables INITRANS defaults to 1 for indexes, 2.
The MAXTRANS setting controls the maximum number of ITLs that a block can allocate (usually defaults to 255). If a block is
sparsely populated then Oracle will dynamically increase the number of ITLs up to MAXTRANS.
However, if the block has little or no free space then transactions will serialize waiting on a free ITL
This is one cause for data base block waits. By setting INITRANS to the number of expected simultaneous DML (data manipulation language ? insert,
update and delete) transaction for a single block, you can avoid serialization for ITL slots.
The maximum value suggested for INITRANS is 100 and settings over this size rarely improve performance. Therefore a setting of INITRANS to
the average number of simultaneous DML users and setting MAXTRANS to 100 will most likely result in the best utilization of resources and
performance. Remember, each ITL requires approximately 23 bytes in the block header.
If we use ALTER TABLE/INDEX to change the value of INITRANS, it will affect only the NEW BLOCKS (From Oracle Documentation). So, it will not affect or change INITRANS for the existing DATA BLOCKS/INDEX BLOCKS
Generally speaking FREELIST groups are used in Oracle real application clusters where the setting should be equal to the number of nodes (instances) participating in the cluster. FREELISTS should be set to the number of simultaneous DML users for the table (not the block!), tables default to 1 FREELIST and 0 FREELIST GROUPS.
Again, settings of greater than 100 rarely result in better performance for FREELISTS. Also, tables will, by default, extend based on the minimum allowed extension times the number of FREELISTS so be aware of this when setting FREELISTS.
