Freelist Management and DML Performance 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 delays.

This dead block problem occurs when the setting for pctused allows a block to re-link 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 are 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 re-added to the freelist. Once a block is re-linked 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.