INITRANS, MAXTRANS, FREELISTS AND FREELIST GROUPS, PCTFREE AND PCTUSED
Mike Aultjuly 29th, 2004?
The Automatic Segment Space Management features (bitmap freelists) is a wonderful features for most shops.? However, using ASSM in some high-DML environments can result in poorer performance, and you will need to manually set the values for INITRANS and FREELISTS (as Well as FreeElist Groups and PctFree and Pctused.
Let's Examine Some Brief Guidelines for these settings.
First Let's Discuss INITRANS:
THE INITTRANS Setting Controls Initial Transaction Slots (ITLS). A Transaction Slot Is Required for Any Session That Needs To Modify A Block in A 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.
ITL's and Block Waits
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 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.
?
Freeelists and their coundin freeelist groups:
A freelist if a one-way linked list (or a bitmap) that identified blocks that can accept data.FREELIST GROUPS was designed for Oracle Parallel Query (OPQ) and used in Oracle Real Application Clusters (RAC) where many instances need to attach to The Same Data Block.
For example, a setting of FREELISTS 4 and FREELIST GROUPS 2 result in 8 total FREELISTS. 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 FreeElist and 0 FreeElist 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.
PCTFree and PCTUSED
The PCTFREE and PCTUSED parameters tell Oracle when to link and unlink a block from the freelist chain.? The following discussion only applies if you are not using Automatic Segment Space Management (ASS Management).
The ASS management tablespace is new in Oracle9i and is implemented by adding the SEGMENT SPACE MANAGEMENT AUTO clause to the tablespace definition. ASM tablespaces automate freelist management and remove the ability to specify PCTFREE, PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters.
The setting for PCTFREE sets the value for the percent of a block to reserve for updates. A block will remain on a FREELIST until it reaches blocksize * (1- (ptcfree / 100)) full or greater. Here are the main issues with incorrect SETTINGS:
High Chained Rows - If PCTFREE is to small, adequate space may not be reserved in the block for update of variable sized rows in the block, or, may not have enough space for a complete row insert In this case a block chaining will occur. where the data is migrated to a new block and a pointer will be established from the old block to a new block High I / O -.? This will result in doubling the IO required to retrieve this data For new data blocks, the space. available for inserts is equal to the block size minus the sum of the block overhead (84-107 bytes) and free space (PCTFREE / 100 * blocksize). When you update existing data Oracle uses any available space in the block. So, updates Will Eventually Reduce The Available Space in a Block To Less Than Pctfree, The Space Reserved for Updates But Not Accessible To Inserts. This Removes The Block Form The FreeElist On Which It Resides.
The un-link process
Blocks with total filled volume less than BLOCKSIZE - overhead -. (Blocksize * (1- (PCTFREE / 100)) are available for inserts When you issue an INSERT statement, Oracle checks a free list of the table for the first available data block and uses it if possible. If the free space in the selected block is not large enough to accommodate the data in the? INSERT statement, and the block is at least filled to the value PCTUSED, then Oracle will remove the block from the free list. Multiple Free Lists for Each Segment Can Reduce Contention for Free Lists When Concurrent Inserts Take Place.
THE RE-LINK Process
After processing a DELETE or UPDATE statement, Oracle checks to see if the space being used in the block is now less than. (BLOCKSIZE - overhead) * PCTUSED / 100 If it is, then the block goes to the beginning of the transaction free list , and it is the first of the available blocks to be used in that transaction. However, it is only when the transaction commits, that the free space in the block becomes available for other transactions.For tables with high levels of updates, setting PCTFREE To a high value is suggester, the set this to a low value. for example, for a limited, for example, while for a low or no update table (SUCH AS) in a data warehouse) a setting of 5 or less is acceptable. PCTUSED must be set to less than 100-PCTFREE and should be set such that PCTFREE PCTUSED is less than 100. If PCTFREE PCTUSED = 100 this can result in see- Sawing of the block on and off the free lists which can be becom.
