Data Blocks and Freelists (from ixora)[1]

transaction and process freelists

26 october 1998

if a segment is created with multiple free lists, then the segment header block also contains a free list header record for each of these process free lists in addition to the master free list.

when a process performs dml that causes a block to fall below the pctused value for the table/cluster, or entirely frees an index leaf block (however, the index case is more complex, because blocks on a free list are not unlinked from the b*-tree structure until an attempt is made to reuse them), that block is put onto a transaction free list. transaction free lists are dynamically created as necessary, and also have a header record in the segment header block. the transaction free list header also records the transaction id.

when a transaction needs to insert/migrate a row, it uses the first block on its transaction free list, if it has previously freed some blocks (except, for an index, because block splits always occur in a recursive transaction). otherwise a process free list is used, based on the process number (pid) mod the number of process free lists. if the process free list is empty, or if there are no process free lists, then the master free list is used.

if the master free list is empty, and if there are some transaction free lists for transactions that have committed, then the transaction free list is marked as unused and the blocks are merged into the master free list. otherwise, the high water mark is raised, initially by 1 block at a time for the first 5 data blocks in the segment, and thereafter by the greater of 5 blocks or _bump_highwater_mark_count blocks (which defaults to 0) times the number of process free lists plus 1 (for the master free list), up to the number of blocks remaining in the extent. these blocks are newed, which means that a free buffer is allocated in the cache and the block header is formatted to make the blocks part of the segment. raising the high water mark may involve dynamic extension. once the master free list is not empty, up to 5 blocks are moved to the target process free list if any, from where they can be used.

the number of free list headers that can fit into the segment header block is limited by the database block size. at least half of the free list slots must be available for transaction free lists. you can see the exact number with the query
   select kviival from x$kvii where kviitag = 'ktsmtf';
connected as sys (or internal). on busy segments, more transaction free lists than that may be dynamically created, slots permitting.

if a segment is created with multiple free list groups, one block after the segment header is used for the free list header records for each free list group. there is still a master free list in the segment header, as well as a master free list in each group. free list group selection is based on the instance number mod the number of free list groups.

as mentioned previously, free list contention occurs when multiple processes using the same free list attempt to modify the data block on the head of the free list concurrently. it is shown in v$waitstat against the data block class. v$waitstat can also show contention for the segment header and free list blocks. this occurs where multiple transaction in the same free list group need to update their free list header records simultaneously. there are various ways of addressing these problems such as rebuilding the table with more free list groups, or increasing _bump_highwater_mark_count, or the novel idea of fixing the application.

to drill down on which segments are causing data block contention, i suggested using event 10046, level 8. this creates a trace file much like to one produced by the sql_trace facility, except that for each event wait a line is printed to the trace file. in particular, each buffer busy wait is recorded together with the p1 and p2 values which are the data file and block number of the wait. so to find which blocks a process has been waiting on, you just grep the trace file for buffer busy waits lines and produce a histogram of the file and block numbers most commonly waited for. once you have suspect file and block numbers, you can relate them to a segment by querying dba_extents. in the case of free list contention on a table it is common to have several hot blocks just below the high water mark for the segment.

there is no cpu cost to having multiple process free lists, and a trivial cost to having multiple free list groups. if you have too many process free lists the worst you get is more free space below the hwm that is not accessible to any particular process. you also get a lower limit on the number of possible transaction free lists you can have.

data block and free list waits

1 april 1999

however, waits for 'data block' class blocks are commonly due to insufficient process freelists. if multiple insert processes use the same process free list concurrently, then they will all attempt to modify the data block on the head of that free list. the associated buffer busy waits are recorded as 'data block' class waits because that is the class of block on which they are trying to establish a buffer lock.

discrete transactions hold buffer locks for the duration of the transaction and can also cause data block buffer busy waits.

v$waitstat

5 april 1999