Oracle performance | Table Freelists

Every Oracle table contains a special data block at the front of the table called the segment header. This contains pointers to a list of free data blocks that contain room for Oracle to store a row. Whenever Oracle performs an SQL INSERT, Oracle accesses the segment header to find a free data block for the new row. Here is how it works:

The concept of freelists in Oracle databases has remained consistent in purpose between Oracle 11g and Oracle 19c, though its usage has diminished in relevance with modern Oracle features. Here’s a comparison of its purpose and functionality in both versions:
Purpose of Freelist

• Freelists specify the number of lists of free blocks available for inserts in a segment (e.g., a table or index).
• They are primarily used to reduce contention on the segment header for multiple sessions performing inserts by allowing parallel processes to access different freelists.

Oracle 11g:

• Freelists are relevant for manual segment space management (MSSM):
  • MSSM relies on freelists to manage the allocation of space in segments.
  • The FREELISTS and FREELIST GROUPS parameters can be explicitly specified at the object level (e.g., tables, indexes) when creating segments using MSSM.
  • This configuration was significant in systems with high concurrent inserts to mitigate contention on segment headers.
• Automatic Segment Space Management (ASSM):
  • In Oracle 11g, ASSM (introduced in Oracle 9i) was already the default for most tablespaces.
  • ASSM uses bitmaps to manage free space, which makes freelists unnecessary for segments using ASSM.

Oracle 19c

• Freelists are rarely used due to ASSM dominance:
  • By Oracle 19c, ASSM is the default and recommended segment space management mode for tablespaces.
  • With ASSM, freelists are entirely superseded because space management is handled dynamically through bitmap blocks rather than fixed freelists.
• Legacy MSSM Support:
  • MSSM is still supported, and freelists can still be configured for legacy applications that require it.
  • The functionality of freelists remains unchanged for segments in MSSM-managed tablespaces.
• Modern Features Reduce the Need for MSSM:
  • Features such as Oracle Real Application Clusters (RAC) and enhancements to ASSM make freelists less critical.
  • For high-concurrency environments, Oracle 19c offers more efficient ways to handle contention, such as Automatic Indexing, partitioning, or advanced table-level optimizations.

Key Changes:

1. Shift to ASSM:
   • ASSM reduces the reliance on freelists by using bitmaps for space management.
   • ASSM is default in Oracle 19c and recommended for most use cases, making freelists largely obsolete.
2. Freelists Still Supported:
   • Freelists functionality remains the same for legacy MSSM-managed tablespaces.
   • MSSM is less commonly used but is available for backward compatibility.
3. Performance Improvements in ASSM:
   • Modern versions of ASSM have improved to better handle high-concurrency workloads, further diminishing the need for freelists.

Conclusion

• Purpose and Functionality: The purpose of freelists has not changed—they still serve to manage free space for segments in MSSM.
• Practical Use: In Oracle 19c, freelists are rarely necessary due to the dominance of ASSM and the availability of better space management techniques. However, for legacy systems using MSSM, freelists operate the same way as in Oracle 11g.

Blocks are placed on a table freelists when:

1. Oracle increases the high-water mark for the table
2. Rows are deleted from a table and the percentage of space in the data block falls below the PCTUSED threshold

This structure works very well except in cases with multiple freelists defined for a table or where the value of PCTUSED is set too high for the table. In these cases, there may be excessive I/O when the table is accessed.

Multiple freelists occur in Oracle whenever there is more than one task concurrently inserting rows into the table. All INSERT requests require Oracle to access the first block in the table (the segment header) to get the freelist. If multiple tasks are inserting, there will be segment header contention while many tasks wait their turn to fetch a free block from the segment header. Hence, Oracle allows the DBA to define any table or index with multiple freelists, one for each concurrent INSERT or UPDATE task against the table. However, multiple freelists can lead to a serious problem called a sparse table. A sparse table occurs when an INSERT request causes a table to extend even though there are many free blocks already on the tables freelists. Here is how it happens.

In Oracle 19c, :freelists are not used when you use Automatic Segment Space Management (ASSM):.
Here's why: For the following list of 3 elements, put the elements in a HTML unordered list .

• ASSM's bitmap mechanism: ASSM uses a bitmap to track free space within a segment. This bitmap provides a more efficient and granular way to manage space than freelists.
• Freelists and their limitations: Freelists were the older method of managing free space in tablespaces. They could sometimes lead to contention and inefficiencies, especially with multiple users inserting data concurrently.
• Simplified space management: ASSM eliminates the need for manual tuning of freelists and freelist groups, making segment space management more automatic and efficient.

Key takeaway: If your table is in an ASSM tablespace, freelists are not used. ASSM provides a superior mechanism for managing free space.
Of course, a table reorganization will coalesce freelists, but the best remedy is prevention. Here is another nasty freelists issue.

There is a DIRECT tradeoff between efficient space re-use and high performance. For the best performance, every new block should be completely empty. Then, we can INSERT many rows before the block becomes full and we must do another I/O to fetch a new block. If we set PCTUSED to a large number, say 70, a data block becomes eligible to receive rows when the amount of data falls below 70%. This means that there is only 30% of free space in the block. When we go to INSERT, we will only be able to insert a few rows before Oracle has to go to the segment header and perform another I/O to get a new block. If you are not short on disk space, be sure to set PCTUSED to a value less then 50.
The next lesson wraps up this module.