 |
|
Traditional Freelists
Management
and Oracle Objects
Oracle Tips by Burleson
|
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 delays.
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. We 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 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.
Linking and Unlinking from the
Freelists
As we now know, the pctfree and
pctused table parameters are used to govern the movement of
database blocks to and from the table freelists. In general, there is
a direct trade-off between performance and efficient table utilization
because efficient block reuse requires some overhead when linking and
unlinking blocks with the freelist. As you may know, linking and
unlinking a block requires two writes: one to the segment header for
the freelist head node, and the other to the new block to make it
participate in the freelist chain. The following general rules apply
to freelists:
-
insert An insert may trigger the
pctfree threshold, causing a freelist unlink. Since insert
operations always use the free block at the head of the freelist
chain, there will be minimal overhead when unlinking this block.
-
update An update that expands row
length is affected by pctfree, but it will not cause a
freelist unlink since the target block would not be at the head of
the freelist chain.
-
delete A delete of rows may
trigger the pctused threshold and cause a freelist link.
You also need to understand how new free blocks
are added to the freelist chain. At table extension time, the
high-water mark for the table is increased, and new blocks are moved
onto the master freelist, where they are, in turn, moved to process
freelists. For tables that do not contain multiple freelists, the
transfer is done five blocks at a time. For tables with multiple
freelists, the transfer is done in sizes (5*(number of freelists +
1)). For example, in a table with 20 freelists, 105 blocks will be
moved onto the master freelist each time that a table increases its
high-water mark.
To see how this
works, let’s review the mechanisms associated with freelist links and
unlinks. For the purposes of the following examples, let’s use Figure
10-7.
Figure 10-85: A sample freelist chain
The segment header contains a space to hold a
pointer to the first free block in the table. Inside Oracle, a pointer
to a block is called a data block address, or DBA for short. The first
block on the freelist chain also has a space in the block header to
contain the DBA for the next free block, and so on.
Let’s explore what happens internally during
row operations.
Freelist Unlinks with Insert Operations
As new rows are inserted, the block may be
removed from the freelist if the free space becomes less than the
bytes specified by pctfree. Since the block being inserted is
always at the head of the freelist chain, only two blocks will be
affected. In our example, let’s assume that the insert has
caused block 20 to be removed from the freelist chain:
1.
Oracle detects that free space is less than pctfree for
block 20 and invokes the unlink operation. Since block 20 is the first
block on the freelist chain, Oracle reads the data block address (DBA)
inside the block header and sees that the next free block is block 60.
2.
Oracle next adjusts the freelist header node and moves the DBA
for block 60 to the head of the freelist in the segment header. Block
20 no longer participates in the freelist chain, and the first entry
in the freelist is now block 60, as shown in Figure 10-8.
Figure 10-86: A freelist unlink operation
Freelist Relinks with Update Statements
As updates to existing rows cause the row to
expand, the block may be unlinked from the freelist if the free space
in the block becomes less than pctfree. Of course, this will
only happen if the row contains VARCHAR, RAW, or LONG RAW column
datatypes, since these are the only datatypes that could expand upon
update. Because the updated block is not at the head of the
freelist chain, the prior block’s freelist pointer cannot be adjusted
to omit the block. Note that the dead block remains on the freelist
even though it does not have room to accept a row.
The dead block remaining on the list will cause
additional Oracle overhead, especially if there is a large number of
“unavailable” blocks on the freelist. At runtime, Oracle will incur
additional I/Os when reading these freelists, and will try the
freelist as many as five times attempting to find a block with enough
room to store the new row. After five attempts, Oracle will raise the
high-water mark for the table.
Reducing Freelist Relinks
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. Freelists
relinks can be reduced in two ways:
-
Freelists relinks can be “turned down” by
setting pctused to 1. Setting pctused to a low value
means that data blocks are not relinked 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, 8i and 9i SQL hint
that will bypass freelist checking. By placing /*+ append */
immediately after the INSERT keyword, Oracle will be directed to
increase the high-water mark for the table and place the row into a
fresh empty block.
Now that we understand how freelists operate
within each Oracle table and index, we are ready to dig deeper and
look at the internals of table management.
This is an excerpt from "Oracle
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. |
|
