B
Associating Instances and Users with Free Lists and Free List Groups (Optional)

This appendix explains how to associate instances and users with free lists and free list groups. It also discusses SQL-specific free list options and the preallocation of extents to free list groups. Only use the information in this appendix if you cannot use automatic segment-space management. The topics in this appendix are:

• Associating Instances with Free Lists
• Associating User Processes with Free Lists and Free List Groups
• SQL Options for Managing Free Space with Free Lists
• Preallocating Extents to Free List Groups

  Note: Oracle Corporation strongly recommends that you use automatic segment-space management instead of free lists and free list groups.

Associating Instances with Free Lists

In general, all tables should have the same number of free list groups, but the number of free lists within a group may vary, depending on the type and amount of activity of each table. The Cache Fusion resources that often cover blocks in one free list group tend to be held primarily by the instance using that free list group. This is because an instance that modifies data is usually more likely to reuse that data than other instances. If multiple instances take free space from the same extent, then they are more likely to contend for blocks in that extent if they subsequently modify the data that they inserted. Automatic segment-space management, however, eliminates this performance issue in Real Application Clusters.

Assignment of New Instances to Existing Free List Groups

If MAXINSTANCES is greater than the number of free list groups in the table or cluster, then an instance number maps to the free list group associated with:

instance_number modulo number_of_free_list_groups

Modulo (or rem for remainder) is a formula for determining which free list group should be used by calculating a remainder value. In the following example there are 2 free list groups and 10 instances. To determine which free list group instance 6 will use, the formula would read 6 modulo 2 = 0. Six divided by 2 is 3 with zero remainder, so instance 6 will use free list group 0. Similarly, instance 5 would use free list group 1 because 5 modulo 2 = 1. Five is divisible by 2 with a remainder of 1.

If there are more free list groups than MAXINSTANCES, then a different hashing mechanism is used. If multiple instances share one free list group, they share access to every extent specifically allocated to any instance sharing that free list group.

FREELIST GROUPS and MAXINSTANCES

In a system with relatively few nodes, the FREELIST GROUPS option for a table should generally have the same value as the MAXINSTANCES option of CREATE DATABASE, which limits the number of instances that can access a database concurrently. In a Massively Parallel Processing system, however, MAXINSTANCES could be many times larger than FREELIST GROUPS so that many instances share one group of free lists.

Associating User Processes with Free Lists and Free List Groups

User processes associate with free lists based on their Oracle process IDs. Each user process has access to only one free list in the free list group for the instance on which it is running. Every user process also has access to the master free list of free blocks. If a table has multiple free lists but does not have multiple free list groups, or if it has fewer free list groups than the number of instances, then free lists and free list groups would be shared among user processes from different instances.

Using the statement ALTER SESSION INSTANCE_NUMBER, you can increase the instance number value beyond the value of MAXINSTANCES. You can also dynamically alter a table's free list assignment with the ALTER TABLE statement. However, you cannot use this statement for free list groups.

SQL Options for Managing Free Space with Free Lists

Several SQL options enable you to create free lists and free list groups for tables, clusters, and indexes. You can explicitly specify that new space for an object be taken from a specific datafile. You can also associate free space with particular free list groups that you can then associate with particular instances.

The SQL statements include:

   CREATE [TABLE | CLUSTER | INDEX]
     STORAGE
     FREELISTS
     FREELIST GROUPS
   ALTER [TABLE | CLUSTER | INDEX]
     ALLOCATE EXTENT
     SIZE
     DATAFILE
     INSTANCE

You can use these SQL options with the INSTANCE_NUMBER parameter to associate data blocks with instances.

Preallocating Extents to Free List Groups

Preallocating extents is a static approach to the problem of preventing automatic allocation of extents by Oracle. You can preallocate extents to tables that have free list groups. This means that all free blocks are formatted into free lists that reside in the free list group of the instance to which you are preallocating the extent. This approach is useful if you need to accommodate objects that you expect will grow in size.