Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

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.


 

 

  
 

 
 
 
 
 

 
 
 
 
 
 
Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.