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.
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
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.