B
Using Free Lists and Free List Groups in Real Application Clusters (Optional)

If you cannot use automatic segment-space management as Oracle Corporation recommends, then refer to the procedures in this appendix that describe how to use free lists and free list groups in Oracle Real Application Clusters environments. The sections in this appendix include:

• Using Free List Groups For Concurrent Inserts from Multiple Nodes
• Extent Management

Using Free List Groups For Concurrent Inserts from Multiple Nodes

If you cannot use locally managed tablespaces and automatic segment-space management, then consider managing free space manually by using free lists and free list groups. However, Oracle Corporation strongly recommends that you use automatic segment-space management.

The Purpose of Free Lists and Free List Groups

Without automatic segment-space management, when data is frequently inserted into a table from multiple nodes and the table is not partitioned, you can use free list groups to avoid performance issues. In such situations, performance issues can be due to concurrent access to data blocks, table segment headers, and other global resource demands.

Free list groups separate the data structures associated with the free space management of a table into disjoint sets that are available for individual instances. With free list groups, the performance issues among processes working on different instances is reduced because data blocks with sufficient free space for inserts are managed separately for each instance.

Deciding Whether to Create Database Objects with Free List Groups

Free lists and free list groups are usually needed when random inserts to a table from multiple instances occur frequently. Processes looking for space in data blocks can contend for the same blocks and table headers. The degree of concurrency and the overhead of shipping data and header blocks from one instance to another can adversely affect performance. In these cases, use free list groups.

Identifying Critical Tables

You can identify tables that are subject to high insert rates by querying the V$SQL view and searching for INSERT commands as shown in the following example:

SELECT SUBSTR(SQL_TEXT,80), DECODE(COMMAND_TYPE,2,'INSERT'),EXECUTIONS
FROM V$SQL
WHERE COMMAND_TYPE = 2 
ORDER BY EXECUTIONS;

Search for the table name in the string for the statements with the highest number of executions. These statements and the indexes that are built on them are candidates for free list groups.

Determining FREELIST GROUPS Reorganization Needs

You can monitor free list group performance by examining the rate of cache transfers and forced disk writes by using the V$CLASS_CACHE_TRANSFER view. V$CLASS_CACHE_TRANSFER view contains information about the number of cache transfers that occurred since instance startup for each class of block. If your output from the following select statement example shows a relatively high amount for segment header and free list forced disk writes, for example, more than 5% of the total, then consider changing the FREELIST GROUPS parameter for some tables to improve performance.

SELECT CLASS, (X_2_NULL_FORCED_STALE + X_2_S_FORCED_STALE) CACHE_TRANSFER 
FROM V$CLASS_CACHE_TRANSFER;

Because the V$CLASS_CACHE_TRANSFER view does not identify cache transfers by object name, use other views to identify the objects that significantly contribute to the number of cache transfers. For example, the V$CACHE_TRANSFER view has information about each block in the buffer cache that is transferred. Block class 4 identifies segment headers and block class 6 identifies free list blocks. The output from the following select statement can show objects that could benefit from increased free list groups values:

SELECT NAME, CLASS#, SUM(XNC) CACHE_TRANSFER
FROM V$CACHE_TRANSFER
WHERE CLASS# IN (4,6)
GROUP BY NAME, CLASS#
ORDER BY CACHE_TRANSFER DESC;

Creating Tables, Clusters, and Indexes with FREELISTS and FREELIST GROUPS

Create free lists and free list groups by specifying the FREELISTS and FREELIST GROUPS storage parameters in CREATE TABLE, CREATE CLUSTER or CREATE INDEX statements. The database can be opened in either exclusive or shared mode. If you need to use free list groups, then the general rule is to create at least one free list group for each Real Application Clusters instance.

FREELISTS Parameter

The FREELISTS parameter 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, then an error message informs you of the maximum value. The optimal value for FREELISTS depends on the expected number of concurrent inserts for each free list group for a particular 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. This means that all existing free lists of a segment are available to all instances. As mentioned, you would typically set FREELIST GROUPS equal to the number of instances in your Real Application Clusters environment.

Free list group blocks with enough free space for inserts and updates are effectively disjoint once Oracle allocates them to a particular free list group. However, once data blocks that are allocated to one instance are freed by another instance, they are no longer available to the original instance. This might render some space unusable and possibly create a skew.

Example

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

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

Creating FREELISTS and FREELIST GROUPS for Clustered Tables

Use clustered tables to store records from different tables if the records are frequently accessed as a group by one or more SELECT statements. Using clustered tables can thus improve performance by reducing the overhead for processing reads. However, clustered tables may be less useful for DML statements.

You cannot specify FREELISTS and FREELIST GROUPS storage parameters in the CREATE TABLE statement for a clustered table. Instead, you must 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.

Without automatic segment-space management, Real Application Clusters enables instances 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.

Creating FREELISTS for Indexes

You can also use the FREELISTS and FREELIST GROUPS parameters in the CREATE INDEX statement. However, you should be aware that inserting into an index differs from inserting into a table because the block Oracle uses is determined by the index key value.

For example, assume you have a table with multiple free list groups that also has an index with multiple free list groups. If two sessions connect to different instances and insert rows into that table, then Oracle uses different blocks to store the table data. This minimizes cache block transfers for the affected data segment. However, index segment cache block transfers can still occur if these sessions insert similar index key values. Therefore, you can only anticipate a slight reduction in cache transfers for the index segment header because Oracle must use more index blocks to store the index free lists.

Associating Instances and User Sessions with Free List Groups

When Oracle creates an object with multiple free list groups, the number of a free list group block becomes part of the object's data dictionary definition. This is important because instances and users need to be associated with a free list group block. You can establish this association statically by assigning a fixed instance number to an instance using an initialization parameter, or by specifying the instance number in DDL statements.

Associating Instances with Free List Groups

You can associate instances with 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 re-connect to other instances. For example, consider a database where space is preallocated to the free list groups in a table. If an instance fails and all the users are failed over to other instances, then their session can be set to use the free list group associated with the failed instance.

If you omit the SET INSTANCE clause, then the failed over sessions would begin inserting data into blocks and extents would be allocated to the instance that they failed over to. Later, when the failed instance is restored and the users connect to it again, the data they inserted would be part of a set of blocks associated with the other instance's free list group. Thus, interinstance communication could increase.

Extent Management

This section discuses the following topics:

• Preallocating Extents
• Extent Management and Locally Managed Tablespaces

Preallocating Extents

Before Oracle inserts rows into a table, the table only has an initial extent with a number of free blocks allocated to it. Otherwise the table is empty. Therefore, you might consider preallocating space for the table in a free list group. This guarantees an optimal allocation of extents containing free blocks to the free list groups, and therefore to the instances. Preallocation also avoids extent allocation overhead.

The advantage of doing this is that the physical storage layout can be determined in advance. Moreover, the technique of allocating extents enables you to select the physical file or volume from which the new extents are allocated. However, you should consider whether and how to implement the ALLOCATE EXTENT clause and how to use a few Oracle initialization parameters when you preallocate as described in the following paragraphs:

• Preallocating Extents with The ALLOCATE EXTENT Clause
• Extent Preallocation Using MAXEXTENTS, MINEXTENTS, and INITIAL

Preallocating Extents with The ALLOCATE EXTENT Clause

The ALLOCATE EXTENT clause of the ALTER TABLE or ALTER CLUSTER statement enables you to preallocate an extent to a table, index, or cluster with parameters to specify the extent size, datafile, and a group of free lists with which to associate the object.

Exclusive and Shared Modes and the ALLOCATE EXTENT Clause

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 runs in exclusive mode, the instance 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 and the ALLOCATE EXTENT CLAUSE

The SIZE 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, then Oracle calculates the extent size according to the values of the NEXT and PCTINCREASE storage parameters.

Oracle does not use the value of SIZE as a basis for calculating subsequent extent allocations, which are determined by the values set for the NEXT and PCTINCREASE parameters.

The DATAFILE Parameter and the ALLOCATE EXTENT Clause

This parameter specifies the datafile from which to take space for an extent. If you omit this parameter, then Oracle allocates space from any accessible datafile in the tablespace containing the table.

The filename must exactly match the string stored in the control file and the filename is case-sensitive. You can query the FILE_NAME column of the DBA_DATA_FILES data dictionary view for this string.

The INSTANCE Parameter and the ALLOCATE EXTENT Clause

This parameter assigns the new space to the free list group associated with the instance number integer. At startup, each instance acquires a unique instance number that maps the instance to a group of free lists. The lowest instance number is 1, not 0; the maximum value is operating system-specific. The syntax is:

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

where n maps 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 is assigned:

Text description of the illustration psadm025.gif

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

Extent Preallocation Using MAXEXTENTS, MINEXTENTS, and INITIAL

You can prevent automatic extent allocations by preallocating extents to free list groups associated with particular instances and by setting MAXEXTENTS to the current number of extents (preallocated extents plus MINEXTENTS). You can minimize the initial allocation when you create the table or cluster by setting MINEXTENTS to 1, which is the default, and by setting INITIAL to its minimum value which is two data blocks, or 10K for a block size of 2048 bytes. To also minimize performance issues among instances for data blocks, 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 system, then enable 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 lower the data block's usage to be less than the value set for PCTUSED by a process running on an instance using that free list group, or specifically allocate the block to that free list group. Therefore, a free list group that is never used does not leave unused free data blocks.

Extent Management and Locally Managed Tablespaces

Allocating and deallocating extents are expensive operations that you should minimize. Most of these operations in Real Application Clusters require interinstance coordination. In addition, a high rate of extent management operations can more adversely affect performance in Real Application Clusters environments than in single instance environments. This is especially true for dictionary managed tablespaces.

Identifying Extent Management Issues

If the "row cache lock" event is a significant contributor to the non-idle wait time in V$SYSTEM_EVENT, then there is a performance issue in the data dictionary cache. Extent allocation and deallocation operations could cause this.

V$ROWCACHE provides data dictionary cache information for DC_USED_EXTENTS and DC_FREE_EXTENTS. This is particularly true when the values for DLM_CONFLICTS for those parameters increase significantly over time. This means that excessive extent management activity is occurring.

Minimizing Extent Management Operations

Proper storage parameter configuration for tables, indexes, temporary segments, and rollback segments decreases extent allocation and deallocation frequency. Do this using the INITIAL, NEXT, PCTINCREASE, MINEXTENTS, and OPTIMAL parameters.

Using Locally Managed Tablespaces

You can greatly reduce extent allocation and deallocation overhead if you use locally managed tablespaces. For optimal performance and space use, segments in locally managed tablespaces should ideally have similar space allocation characteristics. This enables you to create the tablespace with the proper uniform extent size that corresponds to the ideal extent size increment calculated for the segments.

For example, you could put tables with relatively high insert rates in a tablespace with a 10MB uniform extent size. On the other hand, you can place small tables with limited DML activity in a tablespace with a 100K uniform extent size. For an existing system where tablespaces are not organized by segment size, this type of configuration can require significant reorganization efforts with limited benefits. For that reason, the compromise is to create most of your tablespaces as locally managed with AUTOALLOCATE instead of UNIFORM extent allocation.