Oracle Tips by Burleson

Oracle10g Grid Computing with RAC
Chapter 8 - RAC Administration

Associating Instances with Free List Groups

Once you have defined multiple freelist groups for an object in the RAC system, you can associate the instances and user sessions with free list groups as follows:

instance_number parameter: You can use various SQL clauses with the instance_number initialization parameter to associate extents of data blocks with instances.

SET INSTANCE clause: You can use the SET INSTANCE clause of the ALTER SESSION statement to ensure that a session uses the free list group associated with a particular instance, regardless of the instance to which the session is connected.

For example:

ALTER SESSION SET INSTANCE = inst_no

The ALLOCATE EXTENT clause of the ALTER TABLE or ALTER CLUSTER statement enables you to pre-allocate an extent to a table, index, or cluster using parameters that specify the extent size, data file, and group of free lists with which to associate the object. Pre-allocating and associating extents to different instances helps reduce contention during the concurrent inserts.

For example:

ALTER TABLE SALES
ALLOCATE EXTENT (SIZE 5M INSTANCE 2);