Freelist Management and Table Access
Oracle Tips by Burleson Consulting
One of the benefits of having Oracle is that
it manages all of the free space within each tablespace. Oracle
handles table and index space management for us and insulates humans
from the inner workings of the Oracle tables and indexes. However,
experienced Oracle tuning professionals need to understand how
Oracle manages table extents and free data blocks.
Knowing the internal Oracle table management
strategies will help you become successful in managing high-volume
performance within Oracle. To be proficient at object tuning, you
need to understand the behavior of freelists and freelist groups,
and their relationship to the values of the pctfree and
pctused parameters. This knowledge is especially imperative for
enterprise resource planning (ERP) applications, where poor table
performance is often directly related to improper table settings.
The most common mistake for the beginner is
assuming that the default Oracle parameters are optimal for all
objects. Unless disk consumption is not a concern, you must consider
the average row length and database block size when setting
pctfree and pctused for a table such that empty blocks
are efficiently placed back onto the freelists. When these settings
are wrong, Oracle may populate freelists with “dead” blocks that do
not have enough room to store a row, causing significant processing
This dead block problem occurs when the
setting for pctused allows a block to relink onto the
freelist when it does not have enough free space to accept a new
row. I will explain the relationship between average row length and
freelist behavior later in this chapter.
Freelists are critical to the effective reuse
of space within the Oracle tablespaces and are directly related to
the pctfree and pctused storage parameters. When the
database is directed to make blocks available as soon as possible
(with a high setting of pctused), the reuse of free space is
maximized. However, there is a direct trade-off between high
performance and efficient reuse of table blocks. When tuning Oracle
tables and indexes, you need to consciously decide if you desire
high performance or efficient space reuse, and set the table
parameters accordingly. Let’s take a close look at how these
freelists affect the performance of Oracle.
Whenever a request is made to insert a row
into a table, Oracle goes to a freelist to find a block with enough
space to accept a row. As you may know, the freelist chain is kept
in the first block of the table or index, and this block is known as
the segment header. The sole purpose of the pctfree and
pctused table allocation parameters is to control the movement
of blocks to and from the freelists. While the freelist link and
unlink operations are simple Oracle functions, the settings for
freelist link (pctused) and unlink (pctfree)
operations can have a dramatic impact on the performance of Oracle.
The default settings for all Oracle objects
is pctused = 40 and pctfree = 10. As you may know from
Remote DBA basics, the pctfree parameter governs freelist unlinks.
Setting pctfree = 10 means that every block reserves 10
percent of the space for row expansion. The pctused parameter
governs freelist relinks. Setting pctused = 40 means that a
block must become less than 40 percent full before being relinked on
the table freelist.
Let’s take a closer look at how freelist
management works, and how it affects the performance of Oracle. Many
neophytes misunderstand what happens when a block is readded to the
freelist. Once a block is relinked onto the freelist after a delete,
it will remain on the freelist
even when the space exceeds 60
percent. Only reaching pctfree will take the
database block off of the freelist.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.