JL Computer Consultancy

Practical Oracle 8i - Building Efficient Databases

Appendix D: Space Management.


Addenda

Freelists: The errata section of this page highlights a naming error in the book (p.621) regarding freelists. This section expands on the content of page 621, using the appropriate names of segment freelist and transaction freelist.

When you create a table using the default value of the FREELISTS storage parameter, Oracle allocates a single segment freelist in the segment header block; if you then insert a row into that table, Oracle will attach a few blocks to that free list. A block dump of the segment header will then contain the following information about freelists:

	#blocks in seg. hdr's freelists: 1

	nfl = 1, nfb = 1 typ = 1 nxf = 0
	SEG LST:: flg: USED lhd: 0x00806f7c ltl: 0x00806f7c 

This shows us that there is one segment freelist (nfl = number of segment freelists), no transaction freelists (nxf = number of transaction frelists), and the single segment free list contains a linked list of blocks where the list head is the same as the list tail (i.e. one block on the list). If we now insert a number of rows into the table to fill a few blocks, then use two different transactions to delete some rows so that a few blocks become empty enough to fall below the PCTUSED value, we get the following details from a segment header block dump:

	#blocks in seg. hdr's freelists: 7 

	nfl = 1, nfb = 1 typ = 1 nxf = 2
	SEG LST:: flg: USED lhd: 0x00806f87 ltl: 0x00806f89 
	XCT LST:: flg: USED lhd: 0x00806f7e ltl: 0x00806f7c xid: 0x0002.008.0000193e
	XCT LST:: flg: USED lhd: 0x00806f7f ltl: 0x00806f7d xid: 0x0003.008.00001800

In this case we have seven blocks in the free lists - you cannot see the details without walking the freelists and dumping blocks as you go, but there are 3 blocks in the SEG LST:, and two block each in the XCT LST. Note also that nxf is set to 2 to show that we have to transaction freelists. A further interesting, but invisible, point is that I actually commited just one of the transactions before dumping this block, but both transactions have recorded free blocks - in other words, the blocks go on to the transaction freelists before the commit.

At this point, I committed the second transaction as well, then used another transaction to insert a new row. Where would it go ? The following is the segment header block dump immediately after the insert.

	SEG LST:: flg: USED lhd: 0x00806f88 ltl: 0x00806f89 
	XCT LST:: flg: USED lhd: 0x00806f7e ltl: 0x00806f7c xid: 0x0002.008.0000193e
	XCT LST:: flg: USED lhd: 0x00806f7f ltl: 0x00806f7d xid: 0x0003.008.00001800

Note how the segment freelist has been used - the transaction freelists are unchanged. Inserting a few more rows, slowly and carefully, and dumping blocks as I went, this is what happens as the next two blocks are grabbed:

	SEG LST:: flg: USED lhd: 0x00806f89 ltl: 0x00806f89 
	XCT LST:: flg: USED lhd: 0x00806f7e ltl: 0x00806f7c xid: 0x0002.008.0000193e
	XCT LST:: flg: USED lhd: 0x00806f7f ltl: 0x00806f7d xid: 0x0003.008.00001800
	nfl = 1, nfb = 1 typ = 1 nxf = 2
	SEG LST:: flg: USED lhd: 0x00806f7f ltl: 0x00806f7c 
	XCT LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000 xid: 0x0000.000.00000000
	XCT LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000 xid: 0x0000.000.00000000

As the last block came off the segment freelist, Oracle scanned the transaction freelists and re-linked blocks the blocks onto the segment freelist.

This note simply scratches the surface of how freelists are handled - for example if you create the table with (FREELISTS 1 FREELIST GROUPS 2) and use a couple of transactions to insert some rows, you get a segment header block dump showing (note the nfb - number of freelist blocks, and that the SEG LST is unused, which is different from the first example):

	#blocks in seg. hdr's freelists: 0     
	nfl = 1, nfb = 3 typ = 1 nxf = 0
	SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000 

But you also get two further 'free list blocks' (the things recorded in v$waitstat under 'free list'), whose complete block dump is as follows:

	scn: 0x0000.0069ea3c seq: 0x01 flg: 0x00 tail: 0xea3c1601
	frmt: 0x02 chkval: 0x0000 type: 0x16=DATA SEGMENT FREE LIST BLOCK WITH FREE BLOCK COUNT
	blocks in free list = 1
	SEG LST:: flg: USED lhd: 0x00806f7e ltl: 0x00806f7e 

	scn: 0x0000.0069ea3d seq: 0x01 flg: 0x00 tail: 0xea3d1601
	frmt: 0x02 chkval: 0x0000 type: 0x16=DATA SEGMENT FREE LIST BLOCK WITH FREE BLOCK COUNT
	blocks in free list = 1
	SEG LST:: flg: USED lhd: 0x00806f7f ltl: 0x00806f7f 

Each block contains a single segment freelist, and the number of the blocks reported in the segment header block as begin on the seg hdr's freelists stays at zero. From this point onwards the freelist in the segment header seems to be ignored (not that I've tested this exhaustively, so there may be special conditions that come into play) and each of the two freelist blocks seems to behave completely independently in much the same fashion as the segment header block would if there were only one freelist group.

There is plenty more to learn about freelists, (if you create a table with N freelists, you get N+1 SEG LST: entries in the segment header block, do they all get used, or is one idle ? The same occurs if you create the table with N freelists and M freelist groups - you get N+1 SEG LST: entries in each freelist group block. However, the detail to remember it that the total number of freelists (segment plus transaction) per block is dictated by the size of the block. The higher you put FREELISTS, the fewer freelists are left for for transaction freelists, and you can, in extreme circumstances, get contention on transaction freelists.


Errata

In the section titled Segments (p. 621) there is a discussion about process freelists and transaction freelists. I have accidentally swapped the names these two types of freelists in. A transaction freelist holds a linked list of blocks which have been made free by a single transaction (I have called this a 'process freelist' in the book) which may or may not have committed. A process freelist holds a linked list of blocks that are have committed free space available for any new processes that wish to insert data into the segment (I have called this a 'transaction freelist' in the book, and should probably have called it a segment freelist anyway - and will do so from here on)..


Back to Book Index