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:
Ramifications of freelists in Oracle
In Oracle, a freelists parameter is used to specify the number of free lists that are used for a segment in a tablespace. Each free list tracks the free blocks in a segment, and multiple freelists can help distribute the workload of managing free space among multiple concurrent transactions, improving performance and reducing contention for free space.
The ramifications of freelists in Oracle are as follows:
Performance: Increasing the number of freelists can improve the performance of insert operations, as it reduces contention for free space among multiple transactions. However, setting the freelists parameter too high can also cause overhead in maintaining the free lists, which can degrade performance.
Scalability: Freelists can help improve the scalability of a database by allowing multiple transactions to allocate free space concurrently. This can help prevent contention for free space and ensure that the system can handle a large number of concurrent transactions.
Space Management: With multiple freelists, space can be managed more efficiently as blocks are spread across multiple freelists, reducing the likelihood of hot spots where multiple transactions are trying to allocate free space from the same list.
Maintenance: Having multiple freelists means that more memory is used to maintain them, which can result in additional maintenance overhead. It is important to balance the number of freelists with the amount of available memory and the workload of the system.
In summary, freelists in Oracle can have significant ramifications on performance, scalability, space management, and maintenance. The optimal number of freelists depends on the workload and available resources of the system, and should be carefully configured to balance these factors.
Explanation or freelist
Oracle Blocks
Blocks are placed on a table freelists when:
Oracle increases the high-water mark for the table
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
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.
Of course, a table reorganization will coalesce freelists, but the best remedy is prevention. Here is another nasty freelists issue.
PCTUSED is set too high
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.