17
Using Free List Groups to Partition Data

This chapter explains how to allocate free lists and free list groups to partition data. By doing this you can minimize contention for free space when using multiple instances.

The chapter describes:

See Also: Chapter 11, "Space Management and Free List Groups", for a conceptual overview.

Overview

Use the following procedure to manage free space for multiple instances.

Analyze database objects and decide how to partition free space and data.
Set FREELISTS and FREELIST GROUPS in the CREATE statement for each table, cluster, and index.
Associate instances, users, and locks with free lists.
Allocate blocks to free lists.
Pre-allocate extents, if desired.

By managing free space effectively, you may improve performance of an application configuration which is not ideally suited to a parallel server.

Attention: For optimal system performance, use care in setting the FREELIST and FREELIST GROUPS options; these values cannot be reset.

Deciding How to Partition Free Space for Database Objects

This section provides a worksheet to help you analyze database objects and decide how to partition free space and data for optimal performance.

Database Object Characteristics

Analyze the database objects you will create and sort them into the categories described in this section.

Objects in a Static Table

If a table does not have high insert activity, it does not need free lists or free list groups.

Figure 17-1 Database Objects in a Static Table

Objects in a Partitioned Application

With proper partitioning of certain applications, only one node needs to insert into the table or segment. In such cases, free lists may be necessary if there are a large number of users, but free list groups are not necessary.

Figure 17-2 Database Objects in a Partitioned Application

Objects Relating to Partitioned Data

Multiple free lists and free list groups are not necessary for objects with partitioned data.

Figure 17-3 Database Objects Relating to Partitioned Data

Objects in a Table with Random Inserts

Free lists and free list groups are needed when random inserts from multiple instances occur in a table. All instances writing to the segment must check the master free list to determine where to write. There would thus be contention for the segment header, which contains the master free list.

Figure 17-4 Database Objects in a Table with Random Inserts

Free Space Worksheet

List each of your database objects (tables, clusters, and indexes) in a worksheet like the following, and plan free lists and free list groups for each.

Table 17-1 Free Space Worksheet for Database Objects

Database Object Characteristics	Free List Groups	Free Lists
Objects in Static Tables	NA	NA
	NA	NA
	NA	NA
	NA	NA
Objects in Partitioned Applications	NA
	NA
	NA
	NA
Objects Related to Partitioned Data	NA	NA
	NA	NA
	NA	NA
	NA	NA
Objects in Table w/Random Inserts

Note: Do not confuse partitioned data here with Oracle8 partitions, which may or may not be in use.

Setting FREELISTS and FREELIST GROUPS in the CREATE Statement

This section covers the following topics:

You can create free lists and free list groups by specifying the FREELISTS and FREELIST GROUPS storage options in CREATE TABLE, CLUSTER or INDEX statements. This can be done while accessing the database in either exclusive mode or shared mode.

Attention: Once you have set these storage options you cannot change their values with the ALTER TABLE, CLUSTER, or INDEX statements.

See Also: The STORAGE clause in Oracle8 SQL Reference for the syntax of these options.

FREELISTS Option

FREELISTS specifies the number of free lists in each free list group. The default value of FREELISTS is 1, which is also the minimum value; the maximum depends on the data block size. If you specify a value that is too large, an error message informs you of the maximum value. The optimal value of FREELISTS depends on the expected number of concurrent inserts per free list group for this table.

FREELIST GROUPS Option

Each free list group is associated with one or more instances at startup. The default value of FREELIST GROUPS is 1, which means that the table's free lists (if any) are available to all instances. Typically, you should set FREELIST GROUPS to the number of instances in the parallel server. Using free list groups also partitions data. Blocks allocated to one instance, freed by another instance, are no longer available to the first instance.

Note: Even in a non-shared environment, multiple free list groups can benefit performance. With multiple free list groups, the free list structure is delinked from the segment header, thereby reducing contention for the segment header. This is very useful when there is a high volume of UPDATE and INSERT transactions.

Example

The following statement creates a table named DEPT that has seven free list groups, each of which contains four free lists:

CREATE TABLE dept 
            (deptno   NUMBER(2), 
             dname    VARCHAR2(14), 
             loc      VARCHAR2(13) ) 
            STORAGE ( INITIAL 100K        NEXT 50K 
                      MAXEXTENTS 10       PCTINCREASE 5 
                      FREELIST GROUPS 7   FREELISTS 4 );

Creating Free Lists for Clusters

You cannot specify the FREELISTS and FREELIST GROUPS storage options in the CREATE TABLE statement for a clustered table. The free list options must be specified for the whole cluster, rather than for individual tables. This is because the tables in a cluster use the storage parameters of the CREATE CLUSTER statement.

Clusters are an optional method of storing data in groups of tables that have columns in common. Related rows of two or more tables in a cluster are physically stored together within the database to improve access time. A parallel server allows clusters (other than hash clusters) to use multiple free lists and free list groups.

Some hash clusters can also use multiple free lists and free list groups, if they are created with a user-defined key for the hashing function, and the key is partitioned by instance.

Creating Free Lists for Indexes

You can use the FREELISTS and FREELIST GROUPS storage options of the CREATE INDEX statement to create multiple free space lists for concurrent user processes. Use these options in the same manner as described for tables.

When multiple instances concurrently insert rows into a table having an index, contention for index blocks decreases performance unless index values can be separated by instance. Figure 17-5 illustrates a situation in which all instances are trying to insert into the same leaf block (n) of an index.

Figure 17-5 Contention for One Index Block

To avoid this problem, have each instance insert into its own tree, as illustrated in Figure 17-6.

Figure 17-6 No Index Contention

Compute the index value with an algorithm such as

instance_number * (100000000) + sequence_number

Associating Instances, Users, and Locks with Free List Groups

This section explains how you can associate the following with free list groups:

Associating Instances with Free List Groups

You can associate an instance with extents or free list groups as follows:

INSTANCE_NUMBER parameter

You can use various SQL options with the INSTANCE_NUMBER initialization parameter to associate extents of data blocks with instances.

SET INSTANCE option

You can use the SET INSTANCE option of the ALTER SESSION command 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 SET INSTANCE feature is useful when an instance fails and a user connects to another instance. For example, consider a database where space is pre-allocated to the free list groups in a table and users are distributed across instances such that the data is well partitioned, with the result that minimal pinging of data blocks occurs. If an instance fails, moving all users onto other instances does not need to ruin the data partitioning because each new session can use the original free list group associated with the failed instance.

Associating User Processes with Free List Groups

User processes are automatically associated with free lists based on the Oracle process ID of the process in which they are running, as follows:

(oracle_pid modulo #free_lists_for_object) + 1

You can use the ALTER SESSION SET INSTANCE statement if you wish to use the free list group associated with a particular instance.

Associating PCM Locks with Free List Groups

If each extent in the table is in a separate datafile, you can use the GC_FILES_TO_LOCKS parameter to allocate specific ranges of PCM locks to each extent, so that each set of PCM locks is associated with only one group of free lists.

Pre-allocating Extents (Optional)

This section explains how to pre-allocate extents. Note, however, that this useful but static approach requires a certain amount of database administration overhead.

The ALLOCATE EXTENT Option

The ALLOCATE EXTENT option of the ALTER TABLE or ALTER CLUSTER statement enables you to pre-allocate an extent to a table, index or cluster with options to specify the extent size, datafile, and a group of free lists.

The syntax of the ALLOCATE EXTENT option is given in the descriptions of the ALTER TABLE and ALTER CLUSTER statements in Oracle8 SQL Reference.

Exclusive and Shared Modes. You can use the ALTER TABLE (or CLUSTER) ALLOCATE EXTENT statement while the database is running in exclusive mode, as well as in shared mode. When an instance is running in exclusive mode, it still follows the same rules for locating space. A transaction can use the master free list or the specific free list group for that instance.

The SIZE Option. This option of the ALLOCATE EXTENT clause is the extent size in bytes, rounded up to a multiple of the block size. If you do not specify SIZE, the extent size is calculated according to the values of storage parameters NEXT and PCTINCREASE.

The value of SIZE is not used as a basis for calculating subsequent extent allocations, which are determined by NEXT and PCTINCREASE.

The DATAFILE Option. This option specifies the datafile from which to take space for the extent. If you omit this option, space is allocated from any accessible datafile in the tablespace containing the table.

Note that the filename must exactly match the string stored in the control file, even with respect to the case of letters. You can check the DBA_DATA_FILES data dictionary view for this string.

The INSTANCE Option. This option assigns the new space to the free list group associated with instance number integer. Each instance acquires a unique instance number at startup that maps it to a group of free lists. The lowest instance number is 1, not 0; the maximum value is operating system specific. The syntax is as follows:

ALTER TABLE tablename ALLOCATE EXTENT ( ... INSTANCE n )

where n will map to the free list group with the same number. If the instance number is greater than the number of free list groups, then it is hashed as follows to determine the free list group to which it should be assigned:

modulo(n,#_freelistgroups) + 1

If you do not specify the INSTANCE option, the new space is assigned to the table but not allocated to any group of free lists. Such space is included in the master free list of free blocks as needed when no other space is available.

Note: Use a value for INSTANCE which corresponds to the number of the free list group you wish to use-rather than the actual instance number.

Setting MAXEXTENTS, MINEXTENTS, and INITIAL Parameters

You can prevent automatic allocations by pre-allocating extents to free list groups associated with particular instances, and setting MAXEXTENTS to the current number of extents (pre-allocated extents plus MINEXTENTS). You can minimize the initial allocation when you create the table or cluster by setting MINEXTENTS to 1 (the default) and setting INITIAL to its minimum value (two data blocks, or 10 K for a block size of 2048 bytes).

To minimize contention among instances for data blocks, you can create multiple datafiles for each table and associate each instance with a different file.

If you expect to increase the number of nodes in your loosely coupled system at a future time, you can allow for additional instances by creating tables or clusters with more free list groups than the current number of instances. You do not have to allocate any space to those free list groups until they are needed. Only the master free list of free blocks has space allocated to it automatically.

For a data block to be associated with a free list group, either it must be brought below PCTUSED by a process running on an instance using that free list group or it must be specifically allocated to that free list group. Therefore, a free list group that is never used does not leave unused free data blocks.

Setting the INSTANCE_NUMBER Parameter

The INSTANCE_NUMBER initialization parameter allows you to start up an instance and ensure that it uses the extents allocated to it for inserts and updates. This will ensure that it does not use space allocated for other instances. The instance cannot use data blocks in another free list unless the instance is restarted with that INSTANCE_NUMBER.

Note that you can also override the instance number during a session by using an ALTER SESSION statement.

Examples of Extent Pre-allocation

This section provides examples in which extents are pre-allocated.

Example 1

The following statement allocates an extent for table DEPT from the datafile DEPT_FILE7 to instance number 7:

ALTER TABLE dept 
ALLOCATE EXTENT ( SIZE 20K
                  DATAFILE 'dept_file7' 
                 INSTANCE 7);

Example 2

The following SQL statement creates a table with three free list groups, each containing ten free lists:

CREATE TABLE table1 ... STORAGE (FREELIST GROUPS 3 FREELISTS 10);

The following SQL statement then allocates new space, dividing the allocated blocks among the free lists in the second free list group:

ALTER TABLE table1 ALLOCATE EXTENT (SIZE 50K INSTANCE 2);

In a parallel server running more instances than the value of the FREELIST GROUPS storage option, multiple instances share the new space allocation. In this example, every third instance to start up is associated with the same group of free lists.

Example 3

The following CREATE TABLE statement creates a table named EMP with one initial extent and three groups of free lists, and the three ALTER TABLE statements allocate one new extent to each group of free lists:

CREATE TABLE emp ...
    STORAGE ( INITIAL 4096
              MINEXTENTS 1 
              MAXEXTENTS 4 
              FREELIST GROUPS 3 ); 
ALTER TABLE emp 
    ALLOCATE EXTENT ( SIZE 100K DATAFILE 'empfile1' INSTANCE 1 )
    ALLOCATE EXTENT ( SIZE 100K DATAFILE 'empfile2' INSTANCE 2 )
    ALLOCATE EXTENT ( SIZE 100K DATAFILE 'empfile3' INSTANCE 3 );

MAXEXTENTS is set to 4, the sum of the values of MINEXTENTS and FREELIST GROUPS, to prevent automatic allocations.

When you need additional space beyond this allocation, use ALTER TABLE to increase MAXEXTENTS before allocating the additional extents. For example, if the second group of free lists requires additional free space for inserts and updates, you could set MAXEXTENTS to 5 and allocate another extent for that free list group:

ALTER TABLE emp  ...
    STORAGE ( MAXEXTENTS 5 )
    ALLOCATE EXTENT ( SIZE 100K DATAFILE 'empfile2' INSTANCE 2 );

Dynamically Allocating Extents

This section explains how to use the !blocks option of GC_FILES_TO_LOCKS to dynamically allocate blocks to a free list from the high water mark within a lock boundary. It covers:

Translation of Block Database Address to Lock Name

As described in the "Allocating PCM Instance Locks" chapter, the syntax for setting the GC_FILES_TO_LOCKS parameter specifies the translation between the database address of a block, and the lock name that will protect it. Briefly, the syntax is:

GC_FILES_TO_LOCKS = "{ file_list=#locks [!blocks] [EACH] [:] } ..."

The following entry indicates that 1000 distinct lock names should be used to protect the files in this bucket. The data in the files is protected in groups of 25 blocks.

GC_FILES_TO_LOCKS = "1000!25"

!blocks with ALLOCATE EXTENT Syntax

Similarly, the !blocks parameter enables you to control the number of blocks which are available for use within an extent. (To be available, blocks must be put onto a free list.). You can use !blocks to specify the rate at which blocks are allocated within an extent, up to 255 blocks at a time. Thus,

GC_FILES_TO_LOCKS = 1000!10

means that 10 blocks will be made available each time an instance requires the allocation of blocks.

Identifying and Deallocating Unused Space

This section covers:

How to Determine Unused Space

The DBMS_SPACE package contains procedures by which you can determine the amount of used and unused space in the free list groups in a table. In this way you can determine which instance needs to start allocating space again. The package is created using the DBMSUTIL.SQL script as described in the Oracle8 Reference.

Deallocating Unused Space

Unused space you have allocated to an instance using the ALLOCATE EXTENT command cannot be deallocated, because it exists below the high water mark.

Unused space can be deallocated from the segment, however, if the space exists within an extent that was allocated dynamically above the high water mark. You can use DEALLOCATE UNUSED with ALTER TABLE or ALTER INDEX command in order to trim the segment back to the high water mark.

Space Freed by Deletions or Updates

Blocks freed by deletions or by updates that shrank rows will go to the free list and free list group of the process that deletes them.

17Using Free List Groups to Partition Data