Oracle Database Tips by Donald Burleson

Oracle10g Grid Computing with RAC
Chapter 8 - RAC Administration

Manual Space Management

To handle such contention situations, we can specify many free lists at the segment level, sometimes called process free lists. The freelist storage parameter indicates the number of free list sets. Each user process is assigned to one process free list. When the user process needs a block with free space, it scans its assigned process free lists. Existence of multiple process free lists improves performance for concurrent OLTP activities on the same segment and avoids segment header contention. As shown in Fig. 8.4, multiple free lists help reduce contention.

Figure 8.4 Multiple Process Free Lists in the Segment Header

The process free lists are created when the FREELIST clause is used in a create TABLE/ ALTER TABLE statement.

For example:

CREATE TABLE salesman
( name varchar2(4), salesid number(6) )
STORAGE ( FREELISTS 6) ;

There is a possibility of additional overhead and contention in the RAC system since multiple instances concurrently access the segment for DML operations. To reduce such contention, you can create groups of free lists and assign them to the individual instances. This way, users from a particular instance are confined to free lists from a group. This is shown in Fig 8.5. Thus the free list group is a combination of a group specific master free lists and group specific process free lists. Each free list group is stored in its own block. Instances are assigned to different free list groups, thereby reducing contention between instances.