|Oracle9i Real Application Clusters Deployment and Performance
Release 2 (9.2)
Part Number A96598-01
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:
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.
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.
Oracle9i Real Application Clusters Concepts for a conceptual overview of 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.
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.
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 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;
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;
If you did not create your database with the Database Configuration Assistant, then certain Real Application Clusters-specific views such as
Create free lists and free list groups by specifying the
FREELIST GROUPS storage parameters in
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.
You cannot change the value of
FREELISTS parameter specifies the number of free lists in each free list group. The default and minimum value of
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.
Oracle ignores a setting for
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.
With multiple free list groups, the free list structure is detached from the segment header and located in the free list block, which is a separate block. This reduces for the segment header performance issues and provides separate free block lists for instances.
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 );
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
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.
You can also use the
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.
Oracle9i SQL Reference for more information on the SQL mentioned in this section
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.
You can associate instances with free list groups as follows:
INSTANCE_NUMBERparameter--You can use various SQL clauses with the
INSTANCE_NUMBERinitialization parameter to associate extents of data blocks with instances.
SET INSTANCEclause--You can use the
SET INSTANCEclause of the
ALTER SESSIONstatement 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:
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.
This section discuses the following topics:
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:
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.
You can use the
ALTER TABLE (or
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.
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
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
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.
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:
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:
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.
Use a value for
Oracle9i Real Application Clusters Administration for more information about the
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.
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.
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_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.
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
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.
Oracle9i SQL Reference for more information about the