Freelists and Freelist GROUPS. SCOPE & Application

Application
Purpose

=======

The purpose of this article are to provide a understanding of freelists and freelist. SCOPE & Application

===================

This article was intended to being used by DBAs to help understand the how freelists and freelist GROUP work. Freelists and Freelist GROUPS =============================

The following bulletin is comprised of several parts:1. Introduction to Oracle data block organization 2. Free List Overview 3. Free List Types 4. Considerations for optimizing Space Management 5. Algorithms 6. Freelists in Oracle

1. Introduction:

============= before going into the details of freelist management one must know how the Oracle data blocks are organized. Every object in an Oracle database which occupies the "is associated" with a segment. A segment is a set of extents this contains all the data for a specific logical storage structure within a tablespace. There are 4 segment types:o permanent data segment (table, cluster) o index segment o rollback segment o temporary data s Egment Each segment are made up of smaller storage units called extents. An extent was a storage unit composed of contiguous blocks. Each segment are initially created in the database with at least one extent (controlled by parameter). Though empty, the space is reserved. When the initial spaces are full, a new extent is dynamically allocated. The new extent size is determined by the parameters NEXT and pctincrease. The dynamic allocations stop when the number of extents reaches the value of parameter maxextents. The ' the ' fiRST extent of each segment are reserved by Oracle to store system information and it is called the segment header. The Segment Header contains:o the extents Table o the free Lists descriptors o the high Water Mark (HWM) HWM marks a Limit in the segment where we below is ' in use ', and each block above has never the been. The HWM always increases in size; It is reset to "zero" (position to the "start of" segment) when a TRUNCATE is issued.

2. Free List Overview

================== starting with V7.3, it are now possible to create a segment with a either number of limited or W ITH an unlimited number of extents. To accommodate this, five the new block types are introduced:14:unlimited Rollback Segment Header 15:unlimited Deferred R Ollback Segment Header 16:unlimited Data Segment header 17:unlimited data Segment header with FL groups 18:extent Ma P block for each data segment, Oracle keeps track of blocks has available free spaces for incoming INSERT or UPDATE. Free spaces comes from 2 ways:1 blocks beyond the HWM this are allocated not but. 2. All blocks below the HWM linked into a free list, and candidates to be reused. A free list are constituted Of:o Flag to indicate if the free list is used (1) or unused (0) O DBA He free list Chain, O DBA of the "block tail of" the free list Chain. In each of the Data block there are a flag to indicate if it is linked in a free List chain. IF the flag is set the BLOck'll also point to the DBA of the "next block in the" Free list chain. A zero value means that the "current blocks is" the last block in the chain.

3. Free List Types:

================

The Master free List or Common free spaces pool: =============================================== There is Segment. It is implicitly allocated in segment creation time. For each data segment there are a pool, common for each process, of blocks containing free spaces available for incoming INS ERTs called the Master free list. Every process has access to this common pool. So, the level of contention increases when several process simultaneously want to insert rows in the same segment. The blocks of initial extents allocated at segment creation time and of the the new ones dynamically allocated are to T He Master free list.

The Process free Lists: ======================= to reduce contention problems on the Master free list, another level of FR EE lists is available which are called the Process free lists, explicitly created when the freelists parameter has SPE Cified in a create/alter command. Partitioning the free spaces in multiple free lists are used essentially to improve the performance of OLTP applications wit H a high degree of concurrent transactions performing inserts or updates requiring new space allocation. Parameter Freelists has been added in the STORAGE clause of a CREATE table/cluster or INDEX. Example:create TABLE flg (...) ... STORAGE (.....) Freelists 10 ...); The number of process freelists must is close to the maximum number of concurrent process performing simultaneously insert s in the segment. It is important to the improves of the inserts performance, but wastes disk space usage. Each process was mapped to a process free list by its Oracle PID (process ID): Process FREE List entry = (P% NFL) + 1 where p:oracle PID of the process (index in v$process), and nfl:process free lists numb Er as defined by parameter freelists the

Transaction free Lists: =========================== implicitly allocated by Oracle as needed. A Transaction free list are a free list dedicated to one Transaction and only one. There are a minimum transactions free lists/segment, and this number increases as long as it's necessary, until It reaches the limit of the Segment Header block size. A transaction needs to allocate a Tx free Lists entry when:o It releases spaces in a block (DELETE or UPDATE) O and if it has not already allocated one.

4. Considerations for Optimizing Space Management: =============================================== the two parameters PCTFREE and pctused of the Storage Clause in a create/alter statement, are used to control and optimize spaces management W Ithin a data segment block (table, cluster). PCTFREE specifies the percentage of a data block to is reserved (kept free) for possible updates to rows this already are contained in the block. After a blocks becomes full, as determined by PCTFREE, Oracle does not consider the "block is" for the insertion of new rows Until the percentage of the block being used falls below the parameter pctused. It is advised this space calculated as (100%-(PCTFREE + pctused)) to be greater than the size of a table row. 5. Algorithms: =========== "A" is "put on" free list if the "free" in the ' block ' greater than the space reserved b Y PCTFREE. Blocks Linked in a free list are available for future updates or inserts. A block are unlinked from a free list If The free spaces in the "the" is not enough to allow a new row inserts, and if the percentage of the used space remains ABO ve pctused. A block are relinked to a free list if after DELETE or UPDATE operations, the percentage of the used space falls below PCTU Sed. Each time a blocks is added to a free list, where it is linked in the chain. Example: ~~~~~~~~ at time t0, 2 blocks are on free list:data blocks 1 and 3:fl:b1-> B3 at time T1, deletions are Done in block B2 such as the "has to" relinked to free list:FL:B2-> B1-> B3 A Data " Ist only if it isn't already linked. Transaction free list algorithms: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ the TX free list allocation process starts by scanning All Tx free lists allocated in the segment Header blocks and checking if a Tx free list entry has not already been allocat Ed to the transaction. Else, it looks for a unused entry or of empty Tx free list belonging to a "committed" transaction. If the SEarch fails, a new entry in the Tx free lists area is allocated. When there are no chance to expand the Tx free lists area, the unfortunate transaction has to wait for a entry release. The selected entry is determined by (PID%/Tx free lists total number). PID is the Oracle process Id. The wait was done by requesting in S mode the TX enqueue held by the transaction T owning the TX free list entry. The wait would end when T commits. The spaces freed in a blocks by a transaction T1 (DELETE or UPDATE): O are immediately reusable by T1, O'll be reusable fo R Other transactions T1 commits and then looking for spaces in this block. Example: ~~~~~~~~ 5.1 Transaction T1 deletes some rows in table T. These operations release some spaces in the segment. The percentage of the remaining used space falls below Pctused:block can are reused for incoming inserts, it's put on fre E list. To does this, Transaction T1 needs to allocate a entry in the Transaction free lists area in the SeGment Header block, then links the "block" corresponding free list. 5.2. Transaction T2 wants to insert a new row in table T. The spaces freed by T1, in blocks, cannot be reused. Assume that T2 does not have a Tx free list allocated in the segment (because T2, not has spaces in the released) 5. 2.1 T2 starts walking on it Process free List (PRFL) Looking for the A free block. (Let's assume that there are 3 blocks on the PRFL and they don ' t have enough free spaces T2 needs.) 5.2.2 the "fails" check. The block must is exchanged (move to the next block in the list) with another one. This blocks has to is unlinked from the free list if the used spaces in the block goes beyond the spaces defined by pctused O R, if the "UNLINK counter" is below a particular threshold. (The default value is 5). The idea behind this isn't to keep blocks, fail during free spaces search at the head of free lists. The UNLINK action is the "free" list Chain. 5.2.3 after the "blocks" on the PRFL are checked and no blocks is found, the blocks selection in the process free list is Stopped 5.2.4 Oracle tries to move blocks from Master free list to Process free list. This operation are called free Lists Merge. The number of blocks to move are defined by a constant and it is 5. Search would continue from step 5.2.1 Note "At" This point Oracle won't look at the other PRFL even though there migh T is empty blocks on those lists. This would cause some spaces to be wasted temporarily. 5.2.5 (assume that there are no further blocks on the Master free List) Oracle tries now to get blocks from the other Tx free Lists (committed). The Tx free lists entries in the header block are scanned, looking to not empty and "committed" entries. For those entries and all of the blocks are moved to the Master free list. Then, only a part (5) of the blocks in Master free list are moved again to process free list. If There are blocks moved from the TXFL to the MSFL, GO to step 2.1 5.2.6 (If 2.5 fails) Oracle tries to bump up the HWM. To reduce contention in segment header block and Oracle bumps up the HWM by M blocks at a time. The number M is:1: if the block does does not have to go in free list, 1:if "We are on" initial extent (0) and the HWM is & lt;= 4 (Small table) min (5, unused blocks in the extent): Before 7.2 min (5* (nfl+1), unused blocks in the extent): since 7.2. In the example, we assume HWM are at the "end of" the extent. 5.2.7 New extent allocation. The new blocks are moved to Master free list. Free Blocks search continues from the step 2.4 5.3 Transaction T1 wants now to insert a new row in table T. The It looks for available spaces in the blocks linked to the transaction free list. The block has available spaces, and the free spaces in it are enough to the spaces required by the new row, the Ected. The percentage of the "used space" in the "block" goes up above pctused, the blocks has to is unlinked from the transaction fre E list. 6. FreelistS Groups: ================= in Multi-instances (Oracle Parallel Server), to reduce contention at the segment header block, which contains the free lists descriptors of the segment, it's possible to allocate other special to store free L ists descriptors. These groups are called of the free Lists groups. Freelist GROUPS is introduced in STORAGE clause of a CREATE table/cluster or INDEX. Example: ~~~~~~~~ CREATE TABLE flg (...) ... STORAGE (.....) Freelist GROUPS 2 freelists 10 ...); The free list group are associated with a special blocks called the Free list block. These blocks is allocated right after the segment header. So, if and object are created with 2 freelist groups, the ' the ' of the ' the ' of the ' the ' the ' The ' the ' segment header block And the second and third blocks would be the freelist blocks. These are blocks store a Master free list (of the group), and as much process free lists as specified by parameter freelists. The rest of the blocks dedicated to transactions frEE lists. In the segment header block, an object and free list groups There are only one free list called the Super Master free Li St or the Segment Master free list. The free list groups reduce contention on the segment header blocks, and reduce inter-instance "pinging" at the cost of ex Cessive disk space consumption. The algorithm to map a freelist Group, defined on a segment S, to a process P of a instance I, is the following:where:n Fb:number of freelist Groups defined on segment S, p:oracle PID of the process (from v$process), I:instance_number P Arameter (if set in INIT.) ORA) or parameter THREAD value maxinst:maximum of instances sharing the same database (maxinstances parameter of CREATE DATABASE). In a non OPS environment or OPS and single-instance Environment:free list Group are: (P% NFB) + 1 in a multi-instance env Ironment (OPS): o if NFB <= maxinst:free list Group is: ((I-1)% NFB) + 1 o if NFB > maxinst:range = nfb/maxin ST; INITIAL = (RANGE * (I-1)) +1 rest = NFB-(RANGE * maxinst) if (I-1) < rest free List Group Is:initial + (P% (range+1)) if (I-1) >= REST Fre E List group is:initial + REST + (P% RANGE) truncating a table: ~~~~~~~~~~~~~~~~~~~ for the given TABLE and all index A Ssociated to it, the TRUNCATE operation does the following. Get tasks:1 access to the exclusive header. If the segment header is locked process has to, it release by requesting a TX lock in Shared mode. 2. Lock the segment header. 3. Flush all blocks belonging to this segment on the buffer cache to disk. 4. Clear all the free lists in the segment header blocks and in all free list groups blocks (if freelist groups are ). The before image of the changes are recorded in the Rollback segment. 5. Truncate the segment:the HWM returns to it initial position (extent 0 and block 0 if table or 1 if index). The before image of the change was recorded in the rollback segment. 6. Trim all the extents beyond minextents. 7. If OPS, fluSH on disk the segment header blocks and free list groups blocks. Note:performance vs. Space =========================== as can seen to algorithms above, using multiple free list s may cause some empty blocks to go unused, causing the segment to extend. If performance is critical, multiple free lists can are used to improve concurrent access, possibly at the expense of Addit ional space used. However, if primary consideration, then a single freelist should be used, leaving Freelists=1, without the PE Rformance Benefits for concurrent transactions