Reducing Freelist Re-link Operations Oracle Tips by Burleson Consulting

Either of these techniques will cause the freelists to be populated largely from new extents. Of course, this approach requires lots of extra disk space, and the table must be reorganized periodically to reclaim the wasted storage. Freelist re-links can be reduced in two ways:

• "Turn down" freelist re-links by setting pctused to 1. Setting pctused to a low value means that data blocks are not re-linked onto the freelist chain unless they are completely empty.

• Use the append hint when adding rows. By using append with inserts, you tell Oracle to bypass the freelists and raise the high-water mark for the table to grab a fresh, unused data block.

Tip: Remember the cardinal rule of object tuning. There is a direct trade-off between efficient space reuse and fast performance of insert statements. If high performance is more important than space reuse, you can use an Oracle8 SQL hint that will bypass freelist checking. By placing /*+ append */ immediately after the insert keyword, you direct Oracle to increase the high-water mark for the table and place the row into a fresh empty block.

Now that you understand how freelists operate within each Oracle table and index, you are ready to dig deeper and look at long data columns and DML behavior.

Long Data Columns and DML 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 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, 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 blocks that are not known to the other freelist chains. 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 run-time performance, since Oracle will be able to insert only a few rows before having to perform an I/O to get another block.

There are cases when 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 17-7.

Figure 5: 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 you 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 "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.