Remote DBA Plans
Remote DBA Service

Table Internals and Freelists

Oracle Tips by Burleson Consulting

Inside a segment header (the first block in the table), a freelist is a one-way linked list, with NEXT pointers indicating the data block addresses (Remote DBAs) of the next free block. In addition, when a block is added to the freelist, a flag in the header of the free block is set to indicate that it is on the freelist chain; another bucket in the block header will contain the Remote DBA of the next free block in the segment.

The header node on the freelist chain (in the segment header) contains the Remote DBA of the first block in the freelist and the Remote DBA of the last block in the chain. The last data block in the freelist will have a zero value, indicating that it is the last free block in the freelist chain, as shown in Figure 10-9.

Figure 10-87: The basic composition of a freelist chain

As a table grows, Oracle raises the high-water mark for the table, five blocks at a time, and places these new blocks onto the master freelist in the segment header block. Oracle places the new block at the front of the linked list, changing the pointers for the head and the newly freed block. Figure 10-10 shows what happens when the freelist runs out of blocks. Oracle raises the high-water mark for the table from block 130 to block 135, and then adds links for blocks 130–134 onto the freelist chain. This first-in, first-out linking method allows for very fast unlinking and relinking of data blocks from the freelist.

Figure 10-88:Adding a new block to the freelists chain

Now that we see how free blocks are added to the freelist chain, let’s examine the details about how Oracle determines when to add blocks to the freelist. To understand the operation of Oracle in a high-update environment, you need to understand how Oracle gets free blocks to accept new rows. When a task requests a free block to insert a row, the following steps occur within Oracle:

1. Oracle first looks for the task’s own transaction freelist, looking for free blocks.

2. Oracle next looks into the process freelist group for a free block.

3. Oracle then attempts to move blocks from the master freelist to the process freelist. This operation is called a freelist merge and is normally done in chunks of five blocks. Note that Oracle will not look in other process freelists.

4. Oracle then tries to get blocks from other tasks’ transaction freelists that have committed their rows. Oracle scans the transaction freelists, moving the committed blocks onto the master freelist.

5. Oracle next bumps up the high-water mark for the table by bumping the high-water mark. This may involve allocating a new table extent.

However, we must note that there are exceptions to this process. When using RAW and LONG RAW columns in a row insert, Oracle will not attempt to insert the row below the high-water mark for the table and will immediately bump the high-water mark to get new blocks. This is also true when pctused=1 and when the APPEND hint is used inside an insert statement.

Long Data Columns and Freelist Behavior

One of the most confounding problems with some Oracle tables is the use of
large columns. The main problem with RAW and LONG RAW, BLOB and CLOB datatypes is that they often exceed the block size, and whenever a column is larger than the database block size, the column will fragment onto an adjacent data block. This causes Oracle to incur two I/Os instead of one I/O every time the row is accessed. This block-chaining problem is especially prevalent in tables where column lengths grow to thousands of bytes. Of course, it is a good idea to use the maximum supported db_block_size for your version of Oracle (usually 8,192 bytes) in an effort to minimize this chaining.

In order to avoid fragmentation of a row, Oracle will always insert table rows containing a RAW or a LONG RAW column onto a completely empty block. Therefore, on insert, Oracle will not attempt to insert below the high-water mark (using freelists) and will always bump the high-water mark, pulling the free blocks from the master freelist. Since Oracle pulls free blocks by raising the high-water mark, it means that Oracle will not reuse blocks once they have been placed on the freelist chain. Actually, free blocks below the high-water mark (i.e., blocks on the freelists) may be used for inserting LONG columns, but only if the block is completely empty. If the block is partially used but still below the pctused mark it will not be used to insert the LONG data.

Remember, multiple freelists can waste a significant amount of disk space. Tables with dozens of freelists may exhibit the “sparse table” phenomenon as the table grows and each freelist contains free blocks that are not known to the other freelists. If these tables consume too much space, the Oracle administrator faces a tough decision. To maximize space reuse, you would want the table to be placed onto a freelist as soon as it is capable of receiving more than two new rows. Therefore, a fairly high value for pctused is desired. On the other hand, this would result in slower runtime performance since Oracle will only be able to insert a few rows before having to perform an I/O to get another block.

Long Columns and Freelist Usage

There are cases where large row lengths and an improper setting of pctfree can cause performance degradation during SQL insert operations. The problem occurs when a block becomes too full to accept another row while the block remains on the freelist. As rows are inserted, Oracle must fetch these blocks from the freelist, only to find that there is not enough room for a row. Fortunately, Oracle will not continue fetching freelist blocks forever. After retrieving five too-small blocks from the freelist, Oracle assumes that there will be no blocks on the freelist that have enough space for the row, and Oracle will grab an empty block from the master freelist, as shown in Figure 10-11.

Figure 10-89: Oracle eventually abandons the freelist and raises the high-water mark

This problem usually occurs when the row length for the table exceeds the space reserved on the block. For example, with 4K blocks and pctfree=10, the reserved space will equal 410 bytes (not counting block header space). Therefore, we may see this problem for any rows that are more than 410 bytes in length.

Now that we understand the internal freelist linking mechanism, let’s see how to monitor Oracle to identify when Oracle is waiting because of freelist contention.

This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.