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
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
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
Oracle first looks for the task’s own transaction freelist,
looking for free blocks.
Oracle next looks into the process freelist group for a free
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.
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.
Oracle next bumps up the high-water mark for the table by
bumping the high-water mark. This may involve allocating a new table
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.
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
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.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.