|Oracle9i Real Application Clusters Administration
Release 2 (9.2)
Part Number A96596-01
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:
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.
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.
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.
Oracle9i Real Application Clusters Deployment and Performance for more information on associating instances and users with 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.
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.
Oracle9i SQL Reference for complete syntax of these statements
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.