Oracle8i Parallel Server Administration, Deployment, and Performance
Release 2 (8.1.6)

Part Number A76970-01

Library

Product

Contents

Index

Go to previous page Go to next page

8
Using Free List Groups to Partition Data

This chapter explains how to allocate free lists and free list groups to partition data. Free lists exist in single-instance Oracle. However, free list groups only exist in Oracle Parallel Server environments. You use free list groups in Oracle Parallel Server to partition data to minimize contention for free space.

Only use free list groups to partition data when your application profile does not allow table partitioning. It is much simpler to use partitioned tables and indexes to accomplish the same thing that free list groups accomplish.

See Also:

Chapter 5 explains how to use partitioned tables and indexes.  

Topics in this chapter include:

Overview of Free List Implementation Procedures

Use the following procedures to use free lists to manage free space for multiple instances:

  1. Analyze your database objects and decide how to partition free space and data.

  2. Set FREELISTS and FREELIST GROUPS clauses in the CREATE statements for each table, cluster, and index.

  3. Associate instances, users, and locks with free lists.

  4. Allocate blocks to free lists.

  5. Pre-allocate extents, if desired.

By effectively managing free space, you can improve the performance of an application that initially appears not to be ideally suited to Oracle Parallel Server.

Deciding How to Partition Free Space for Database Objects

Use the worksheet in this section to analyze database objects and to decide how to partition free space and data for optimal performance.

Database Object Characteristics

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

Objects Read-Only Tables

If a table does not have high insert activity or sufficient updates to require new space allocations, the table does not need free lists or free list groups.

Objects in Partitioned Applications

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

Objects Relating to Partitioned Data

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

Objects in Tables 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 containing the master free list.

Free Space Worksheet

List each of your database objects, such as tables, clusters, and indexes, in a worksheet as shown in Table 8-1, and plan free lists and free list groups for each.

Table 8-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 with Oracle8i partitions that may or may not be in use.  


Using the CREATE Statement FREELISTS and FREELIST GROUPS Parameters

This section covers the following topics:

Create free lists and free list groups by specifying the FREELISTS and FREELIST GROUPS storage parameters in CREATE TABLE, CLUSTER or INDEX statements. Do this while accessing the database in either exclusive or shared mode.

A general rule is to create a free list group for an Oracle Parallel Server instance if the instance experiences significant amounts of DML. Then set a value for FREELIST GROUPS equal to the number of instances in the cluster.


Note:

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


FREELISTS Parameter

FREELISTS specifies the number of free lists in each free list group. The default and minimum value of FREELISTS is 1. The maximum value 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 Parameter

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 equal to the number of instances in Oracle 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:

With multiple free list groups, the free list structure is detached 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 Clustered Tables

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

Clusters are an optional method of storing data in groups of tables having common key columns. Related rows of two or more tables in a cluster are physically stored together within the database to improve access time. Oracle 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 you created them with a user-defined key for the hashing function and the key is partitioned by instance.


Note:

Using the TRUNCATE TABLE table_name REUSE STORAGE syntax removes extent mappings for free list groups and resets the high water mark to the beginning of the first extent.  


See Also:

Oracle8i SQL Reference for more information on the REUSE STORAGE clause of the TRUNCATE TABLE statement.  

Creating Free Lists for Indexes

You can use the FREELISTS and FREELIST GROUPS storage parameters of the CREATE INDEX statement to create multiple free space lists for concurrent user processes. Use these parameters 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 8-1 illustrates a situation where all instances are trying to insert into the same index leaf block (n).

Figure 8-1 Contention for One Index Block


To avoid this, have each instance insert into its own tree, as illustrated in Figure 8-2.

Figure 8-2 No Index Contention


Compute the index value with an algorithm such as:

Associating Instances, Users, and Locks with Free List Groups

This section explains how to 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 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 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 clause is useful when an instance fails and users connect to other instances. For example, consider a database where space is pre-allocated to the free list groups in a table. With users distributed across instances and the data well-partitioned, minimal pinging of data blocks occurs. If an instance fails, moving all users to other instances does not disrupt 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:

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 data file, use the GC_FILES_TO_LOCKS parameter to allocate specific ranges of PCM locks to each extent, so each PCM lock set is associated with only one group of free lists.

See Also:

Oracle8i Parallel Server Concepts for more information about associating free lists with instances, users, and locks.  

Pre-Allocating Extents

This section explains how to pre-allocate extents. This method is useful but a static approach to extent allocation requires a certain amount of database administration overhead.

The ALLOCATE EXTENT Clause

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 with parameters to specify the extent size, data file, and a group of free lists.

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 parameter

This parameter 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 parameter

This parameter specifies the data file from which to take space for the extent. If you omit this parameter, space is allocated from any accessible data file in the tablespace containing the table.

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 parameter

This parameter 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:

If you do not specify the INSTANCE parameter, 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. 


See Also:

"Shutting Down Instances" for more information about the INSTANCE parameter.  

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 10K for a block size of 2048 bytes).

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

If you expect to increase the number of nodes in your system, 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 space to those free list groups until it is needed. Only the master free list of free blocks has space allocated to it automatically.

To associate a data block with a free list group, either bring it below PCTUSED by a process running on an instance using that free list group, or specifically allocate it 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 an instance and ensure that it uses the extents allocated to it for inserts and updates. This ensures that it does not use space allocated for other instances. The instance cannot use data blocks in another free list unless another instance is restarted with that INSTANCE_NUMBER. 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 data file 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 parameter, 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 the ALTER TABLE statement 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 parameter 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 Data Block 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 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 10 blocks will be available each time an instance requires the allocation of blocks.

See Also:

Chapter 9 for more information about the ALLOCATE EXTENT syntax.  

Identifying and Deallocating Unused Space

This section covers:

Identifying Unused Space

The DBMS_SPACE package contains procedures with 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 begin allocating space again. Create the package using the DBMSUTIL.SQL script as described in the Oracle8i Utilities.

Deallocating Unused Space

Unused space you have allocated to an instance using the ALLOCATE EXTENT command cannot be deallocated. This is 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 the ALTER TABLE or ALTER INDEX statement to trim the segment to the high water mark.

Space Freed by Deletions or Updates

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


Go to previous page Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index