Storage management is an important aspect of database tuning. Oracle manages storage space using several storage management parameters. You can use some of these storage management parameters—in particular, FREELISTS, FREELIST GROUP, and PCTFREE—to improve the performance of Oracle Parallel Server. This chapter explains the concepts behind these parameters and describes how to set them in an OPS environment.
In this chapter we also discuss Oracle’s reverse key index feature, which you can use to spread the index entries in an index tree more evenly when the indexes are based on sequential keys. Use of reverse key indexes can reduce contention for index leaf blocks during index updates when many instances are inserting rows into the same table.
Free lists
and free list
groups are structures that Oracle uses to keep track of
the free space within a segment such as a table, index, or cluster.
If you have a large number of processes updating or inserting data
into a segment, they will all need access to the segment’s free
list. Too much contention for a segment’s free list can cause
performance to suffer. The FREELISTS and FREELIST GROUP space
management parameters are used to reduce contention for a
segment’s free list as follows:
- FREELISTS
You can set this parameter in both OPS and non-OPS environments. Use it to reduce contention for free lists when multiple-user processes are inserting or updating the same table.
- FREELIST GROUP
Unlike FREELISTS, this parameter is useful only in OPS environments. Use it to reduce contention when processes from multiple OPS instances are inserting or updating the same table.
Both of these storage parameters apply to clusters and indexes, as well as to tables. These parameters are not useful for read-only tables.
Whenever you create a table, cluster, or index, Oracle creates at least one segment to hold the data for that object. Each segment consists of one or more extents, and each extent consists of a set of contiguous database blocks. Within the first block of each segment is a segment header. The segment header contains, among other things, a list of blocks allocated to the segment that still have free space available. This list of free blocks is maintained in a linked list known as the master free list.
The high-water mark (HWM) represents the upper limit of blocks that have been used in a segment. Any blocks above the HWM have never been used. When a table, index, or cluster needs additional space, the HWM for that segment is increased, and free blocks above the HWM are made available for use. Oracle automatically allocates new extents when the HWM cannot be increased any further, and those new extents are added above the HWM. Whenever the HWM is increased, five new blocks are added to the master free list. Blocks are removed from the master free list when the free space in those blocks becomes less than that specified by the PCTFREE storage parameter. (See the discussion of this parameter in Section 9.2 later in this chapter.) Blocks are added back to the master free list when their used space falls below the threshold specified by the PCTUSED parameter.
All processes use the master free list during insert and update operations to locate blocks with space for new data. These processes also update the master free list, removing blocks when their free space falls below PCTFREE and adding them back when their used space falls below PCTUSED. All updates to the master free list involve writes to the segment header. When multiple processes in an Oracle instance perform concurrent insert and update operations on a single table, the result is contention for the master free list. Figure 9.1 illustrates this contention.
In order to avoid contention for the master free list, you can organize a segment’s free space into several process free lists . A process free list is another level of free list that is used by specific processes. You create process free lists, sometimes just called free lists, by specifying the FREELIST storage parameter when you first create an object. Each time the high-water mark for the segment is increased, 5 * (number of free lists + 1) free blocks are transferred. For example, if you have a table with 10 free lists, a total of 55 free blocks will be moved. Each free list will get 5 blocks, and another 5 will go into the master free list.
Each user process updating an object is assigned to one process free list and is not allowed to access any of the others. When a user process needs to find a block with free space, it scans its assigned process free list first, before looking in the master free list. The result is that update activity is spread out over several process free lists instead of being concentrated in the master free list. Oracle processes are mapped to specific process free lists using the following modulo function:
Specific process free list used =
(process_id modulo number_of_freelists) + 1The master free list and process free lists are located in the segment header, as illustrated in Figure 9.2. Having multiple process free lists improves performance in OLTP applications in which several processes are concurrently inserting or updating the same object. Ideally, make sure that the value for the FREELISTS parameter is equal to the number of concurrent insert operations.
There is one drawback to using process free lists—you may end up with more disk space allocated to a segment than you would otherwise. This is because each user process takes free blocks from a specific process free list or from the master free list. If the specific process free list and the master free list are both empty, additional extents will be allocated to the segment even though other free lists may have free blocks available. However, free blocks that are available in those other process free lists eventually will be used up as new user processes are assigned to those process free lists.
In an OPS environment, multiple instances may concurrently perform insert or update operations against a table. This can lead to contention for the master free list—not between processes, but between instances. The solution to this problem is similar to the solution for the problem of contention between processes. Instead of creating free lists for individual processes, you create groups of free lists for use by the individual instances.
In an OPS environment, processes from multiple instances refer to the master free list of a segment in order to locate free blocks. These processes also update the master free list to remove any blocks that are no longer free. Because updates to the master free list involve writes to the segment header, concurrent access from multiple instances can result in unwanted ping activity as the instances all contend for the same segment header block. This pinging occurs even if you have used the FREELISTS parameter to specify multiple process free lists for the segment, because the segment header also contains those process free lists. If the number of concurrent insert and update operations is high, the segment header will experience heavy ping activity, resulting in a loss in performance. Figure 9.3 illustrates this situation.
To resolve the contention shown in this figure, you can create a free list group for each of the instances. A free list group is a combination of a group-specific master free list with group-specific process free lists. Each free list group is stored in its own block. Instances are assigned to different free list groups, thus reducing contention between instances for the same segment header block.
The FREELIST GROUP storage parameter is used to create multiple free
list groups, each with its own master free list and each in its own
block. These blocks are referred to as free list
blocks and are always created in the first extent of a
segment, immediately after the segment header. The segment header
block contains a special free list called the central
master free list, or the segment master free
list. When multiple instances insert into or update the
segment concurrently, each instance will access a different free list
group to find free blocks. Pinging is reduced or eliminated because
each free list group is in its own block, and different instances are
assigned to different free list groups. If you can manage to have one
free list group for each instance, you’ll never have two
instances contending for a free list block. This scenario is
illustrated in Figure 9.4.
The FREELISTS and FREELIST GROUP parameters can be specified together in an OPS environment in which each OPS instance also has several processes that perform concurrent inserts or updates. In such a configuration, each free list block stores a master free list together with the number of process free lists specified by the FREELISTS parameter. For example, the following SQL statement creates a table named emp with two free list groups and three process free lists in each group:
CREATE TABLE emp
(
emp_id NUMBER(5),
emp_name VARCHAR2(20),
dob DATE
)
STORAGE
(INITIAL 10K NEXT 10K MAXEXTENTS 10
PCTCREE 20 PCTUSED 60 PCTINCREASE 0
FREELIST GROUP 2 FREELISTS 3);Figure 9.5 illustrates how each instance in a two-instance OPS environment can then access separate free list blocks, each with its own master free list and its own set of process free lists. If multiple processes in an instance are accessing the same segment, those processes will be distributed among the available process free lists within the free list group allocated to the instance.
Both the FREELISTS and FREELIST GROUP parameters can be specified only at the time of object creation. The default value for FREELISTS and FREELIST GROUP is 0. The ALTER TABLE statement cannot change these parameters. To apply new values for these parameters, you have to re-create the database object in question.
The MAXINSTANCES parameter specifies the maximum number of OPS instances that can access a database; you set it when the database is created. If you’re using multiple free list groups, it’s generally best to use a FREELIST GROUP value equal to the MAXINSTANCES value. If both values are the same, each instance will map to a different free list group. However, in an environment with a very large number of OPS instances, space limitations may prevent you from setting FREELIST GROUP to such a high value. When the number of free list groups is less than the number of instances, you’ll have multiple instances assigned to some free list groups. OPS instances are mapped to specific free list groups during instance start-up using the following modulo function:
Free list group allocated to instancei= (instance numbermodulonumber of free list groups) + 1
When the number of free list groups is larger than the number of OPS instances, free list groups are divided among the instances. For example, if there are four free list groups for two OPS instances, the first instance will use the first two free list groups, and the second instance will use the remaining two free list groups. In this case, the particular free list group used by an Oracle process in an instance is determined by applying a hashing function to the process ID of that particular process.
You can specify the FREELISTS and FREELIST GROUP parameters for indexes and clusters in the same way you specify them for tables. With clusters, you have one STORAGE clause that applies to a group of tables. Consequently, all tables in a cluster share the same FREELISTS and FREELIST GROUP settings. The following example shows how you would create an index with two free list groups, and with three free lists in each group:
CREATE INDEX emp_index ON emp (emp_id) STORAGE (FREELIST GROUP 2 FREELISTS 3);
If you’re using multiple free list groups to minimize pinging on the segment header blocks, you can further reduce the potential for pinging by preallocating extents to your instances. To preallocate an extent, you use the ALLOCATE EXTENT clause of the ALTER TABLE, ALTER CLUSTER, or ALTER INDEX commands. It only makes sense to preallocate extents if you use multiple free list groups, and you must first number each of your instances so that you have a way to identify them when you create new extents.
To give each instance a number, use the INSTANCE_NUMBER parameter in your instance-specific parameter files. Give each instance a unique number, starting sequentially from 1, for example:
INSTANCE_NUMBER = 1
These instance number values will be read at instance startup and can then be used to uniquely identify each instance when you preallocate extents.
The ALLOCATE EXTENT clause is used with the ALTER TABLE, ALTER CLUSTER, and ALTER INDEX commands to manually create and allocate an extent for a table, cluster, or index. When you manually allocate an extent for an object, you have the option of associating that extent with a specific instance. The following example shows an ALTER TABLE statement that allocates a 20K extent to instance 1:
ALTER TABLE emp ALLOCATE EXTENT (SIZE 20K INSTANCE 1);
This form of the statement leaves the choice of datafile up to Oracle. If the tablespace containing the object in question is spread across multiple datafiles, you can go even further by specifying the datafile from which the new extent is to be allocated. For example:
ALTER TABLE emp
ALLOCATE EXTENT (SIZE 20K
DATAFILE 'emp_data_1'
INSTANCE 1);When you do allocate an extent to a specific instance in this way, the free blocks from that extent are assigned to the free list group associated with the instance. Assuming that you assign separate datafiles to preallocated extents belonging to the separate instances, and that you allocate separate PCM locks to these datafiles, pinging will be minimized during insert operations. Pinging also will be greatly reduced on those operations if the instance that inserts data is also the same instance that subsequently executes DML statements on the data or is the same instance that queries the data.
If you don’t specify an instance when you preallocate an extent, or an extent is automatically allocated because no more space is available in the segment, the space in that extent is not allocated to any particular free list group. Instead, it is assigned to the central master free list. Space from the segment master free list can be used by any instance when no space is available in that instance’s free list group.
Normally, when you allocate an extent to an instance, other instances will not use the blocks that make up that extent. You can override that behavior, however, using the ALTER SESSION SET INSTANCE command. This command allows you to associate your session with the extents allocated to an instance other than the one to which you are connected. Suppose, for example, that you issue the following command:
ALTER SESSION SET INSTANCE 3;
After executing this command, your session will now go to the free list group normally assigned to instance 3 whenever it needs to find a data block with free space. This occurs regardless of which instance you are really connected to.
In general, every object that is likely to sustain concurrent insert activity from multiple instances should be created with enough free list groups so that each instance can have its own. For example, if you have two OPS instances, then you should set the FREELIST GROUP parameter to 2 for all the tables, indexes, and clusters that will encounter concurrent insert or update activity from both of those instances. If you expect additional OPS nodes to be configured, then you may want to create enough extra free list groups to accommodate the anticipated growth without needing to re-create all those objects. That will save you the effort of re-creating those objects after the new instances are added.
Through careful application partitioning, you can limit insert and update activity on an object to a single instance. Multiple free list groups are not required in such cases nor are they beneficial. However, if you’ve partitioned your application, and you still have a few objects that sustain concurrent inserts and updates from multiple instances, you should create free list groups for just those objects.
If you partition objects using the partitioning features introduced in Oracle8, be aware that each partition is stored in its own segment. If each instance in an OPS environment is inserting into or updating a separate partition, then you don’t need to worry about creating free list groups. Similarly, with process free lists, if multiple processes are updating a table but each is updating a different partition, then multiple process free lists are not necessary.