Freelists or freelist groups secrets Oracle Tips by Burleson Consulting April 9, 2003

 

Many people who experience segment header contention (buffer busy waits) are confused about the proper use of multiple freelists versus multiple freelist_groups for a table or index.

As we may know, multiple freelists add additional process freelists within a single segment header, while multiple freelist groups add multiple segment headers.

Of course, if you are using Oracle, the best solution is to redefine your tablespace with automatic segment space management (ASSM) and use the wonderful Oraclebitmap freelists.

As an old Oracle Parallel Server (OPS) DBA, we were taught that freelist_groups were only for OPS and RAC, and that there should be one freelist group per RAC node.

However, there is growing evidence that some segment header contention can be relieved in non-RAC systems by adding multiple freelist_groups to tables and indexes.  This is especially useful for partitioned tables, and system with extremely high DML activity.

Maybe the misunderstanding is from MOSC which suggests that segment header contention is relieved with freelists:

---

According to MOSC Note:1060377.6

It is a good idea to always use the freelists option when creating a table that will have high inserts or updates requiring new space contention because this  creates multiple process (segment) free lists instead of the default ONE  master free list on a segment header.

From MOSC note: 1029850.6:

As can be seen from the algorithms above, using multiple free lists may cause some empty blocks to go unused, causing the segment to extend. If performance is critical, multiple free lists can be used to improve concurrent access, possibly at the expense of additional space used.

---

Steve Adams, the world famous Oracle Internals guru from http://www.ixora.com.au/ has the following comments on this issue:

?Yes, freelist groups work almost as well as freelists for avoiding contention on data blocks, and of course will also help to reduce segment header block contention.

However, so far as avoiding data block contention is concerned, freelist groups should not be part of your strategy in a non-RAC environment. They do not work as well as freelists, and are more expensive.?

However, there is growing evidence that multiple freelist_groups are now used with partitioned tables and system with Oracle parallel query (OPQ) to reduce segment header contention.

From Melissa Holman - MOSC - 12/19/01:

"Free list groups can have a positive impact in an exclusive environment, they're not just for OPS. Free List groups will reduce contention on the segment header by having concurrent processes attempting to insert assigned to separate free list group blocks to look for blocks eligible for insert.

The algorithm in a parallel environment uses the instance/thread number to assign an instance to a free list group. In an exclusive environment, the process number is used to assign a process to a free list group.

The primary cost with using multiple process free lists or multiple free list groups is increased space usage, but in a highly concurrent insert environment, the performance gains will far outweigh the potential downside of a little extra space being used."

---

Many related topics are covered in my book Oracle Tuning: The Definitive Reference by Rampant TechPress. 

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

References:

MOSC note: 1029850.6: "As can be seen from the algorithms above, using multiple free lists may cause some empty blocks to go unused, causing the segment to extend. If performance is critical, multiple free lists can be used to improve concurrent access, possibly at the expense of additional space used."

This MOSC paper "Free list management in Oracle8i" is also excellent for describing the sharing issue with linked-list (non ASSM) freelists (emphasis added):

?There are three main types of freelists used to manage segment space; the Master Freelist, the Process Freelist and the Transaction Freelist. Each one controls its own set of datablocks . . . ?

?When using multiple process freelists, the amount of unused space within datablocks can increase. The reason for this is a user process maps onto a particular process freelist using an algorithm (described below), and will not search other process freelists for space if none is found within its own. If a large number of blocks are linked in a particular process freelist, and another user process has no free blocks on its process freelist or no free blocks exist on the master freelist, the process may request movement of the HWM or creation of a new extent. This could leave free blocks on the other process freelist unused.?

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.