| Oracle7 Parallel Server Concepts and Administrator's Guide |  Library |
 Product |
 Contents |
 Index |
| Oracle7 Parallel Server Concepts and Administrator's Guide | Library |
Product |
Contents |
Index |
The chapter describes:
See Also: "Space Management and Free List Groups" ![[*]](jump.gif)
for a conceptual overview.
To Manage Free Space on 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.
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 18 - 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 18 - 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 18 - 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 18 - 4. Database Objects in a Table with Random Inserts
| 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 | ||
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 Oracle7 Server SQL Reference for the syntax of these options.
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 );
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.
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 18 - 5 illustrates a situation in which all instances are trying to insert into the same leaf block (n) of an index.
Figure 18 - 5. Contention for One Index Block
To avoid this problem, have each instance insert into its own tree, as illustrated in Figure 18 - 6.
Figure 18 - 6. No Index Contention
Compute the index value with an algorithm such as
instance_number * (100000000) + sequence_number
| 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 |
(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
See Also: "Free Lists Associated with Instances, Users, and Locks" .
The syntax of the ALLOCATE EXTENT option is given in the descriptions of the ALTER TABLE and ALTER CLUSTER statements in Oracle7 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" .
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.
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 ); If datafile empfile2 does not have enough space for the new extent, you can allocate the extent from any other datafile in the same tablespace.
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"
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: "Allocating PCM Instance Locks" chapter .
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 with ALTER TABLE or ALTER INDEX command in order to trim the segment back to the high water mark.
|
Prev Next |
 Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |