Release 8.0
A54639_01
 Library |
 Product |
 Contents |
 Index |
| Oracle8 Parallel Server
Concepts & Administration Release 8.0 A54639_01 |
|
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.
Use the following procedure to manage free space for multiple instances.
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.
This section provides a worksheet to help you analyze database objects and decide how to partition free space and data for optimal performance.
Analyze the database objects you will create and sort them into the categories described in this section.
If a table does not have high insert activity, it does not need free lists or free list groups.
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.
Multiple free lists and free list groups are not necessary for objects with partitioned data.
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.
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.
Note: Do not confuse partitioned data here with Oracle8 partitions, which may or may not be in use.
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 Server SQL Reference for the syntax of these options.
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.
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.
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 );
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.
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.
To avoid this problem, have each instance insert into its own tree, as illustrated in Figure 17-6.
Compute the index value with an algorithm such as
instance_number * (100000000) + sequence_number
This section explains how you can associate the following with free list groups:
You can associate an instance with extents or free list groups as follows:
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.
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
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.
See Also: "Free Lists Associated with Instances, Users, and Locks" on page 11-14.
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 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 Server 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.
See Also: "Instance Numbers and Startup Sequence" on page 18-13.
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.
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.
This section provides examples in which extents are pre-allocated.
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);
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.
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 );
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:
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"
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.
See Also: Chapter 15, "Allocating PCM Instance Locks".
This section covers:
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 Server Reference Manual.
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.
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.
