Page 680
Also, if you need to change the status of an enabled thread, for example, to turn it from a public to a private thread, you will first have to shut down any instance that is currently using the thread and make the change from a different instance.
You need to choose a unique thread number for each instance's redo logs. You also need to use group numbers that are unique across the whole database, not just unique within each thread. Unlike rollback segments, the option to make a thread public or private is part of the ALTER command, rather than the CREATE command. To enable a thread privately, just leave out the PUBLIC keyword.
You do not have to make all your threads identical if the instances will not be doing similar work. For example, you can have more, larger redo logs in a thread for an instance that will be processing many transactions and fewer, smaller redo logs in a thread for a read-intensive instance. If you decide to use such a variety of redo structures, you will have to enable the threads privately to ensure that each instance acquires the proper thread. Also, if you intend your instances to act as failover instances for each other, while continuing with their normal work, you should consider using identical redo threads regardless of the primary needs of each instance.
A freelist is a chain of blocks that contain empty space to be used for new data, that is, space above the amount reserved by the table's PCTFREE setting. By default, Oracle assigns a single freelist to each segment. When a table, index, or cluster, contains multiple freelists, each one contains a different subset of blocks. Such multiple freelists can be maintained in a single group, or they can be assigned to different freelist groups. In this section, you will learn when and how to use multiple freelist groups to improve performance in a multi-instance database.
CAUTION |
Remember that UPDATEs can also use blocks on the freelist, not just INSERTs. If, as the result of an UPDATE, a row grows too long to fit into its original block, the whole may be moved (or migrated, to use Oracle's terminology) to a different block, called an overflow block. If there are no overflow blocks available, Oracle will take the next block on the freelist to store the migrated row. The user performing the UPDATE may not be assigned to the same freelist, or freelist group, as the user performing the original INSERT. Therefore a row can migrate between freelist groups as well as between blocks. |
There are two main reasons for using multiple freelist groups with Parallel Server. The first is to avoid contention for freelist access. Only one user at a time can control a freelist and, in a multi-user database, multiple freelists are needed to allow concurrent INSERTs into the same table. There is an upper limit to the number of freelists that can be assigned to a segment, based on the database block size. For very active databases and tables, you may need to add more freelists by including multiple freelist groups, each with multiple freelists.
The second reason to use freelist groups is to control which blocks are used by an instance for INSERTs. When a block or set of blocks belong to an instance's freelist group, only that instance is able to use those blocks for INSERTs or for UPDATEs that require relocating a rows to overflow
Page 681
blocks. These activities need to be performed under exclusive PCM locks and it is convenient to group the blocks being used for this work under different locks for each instance. This way, one instance performing a series of INSERTs (or UPDATEs) can acquire and keep the locks required, while the same activity on another instance can proceed under the control of its own locks.
Oracle stores freelist information in a segment's header block. When multiple freelist groups are used, an additional header block is built for each group. Figure 27.8 shows how Oracle builds a segment with four freelist groups.
If your users are likely to perform INSERTs from two or more instances, the additional header blocks used by freelist groups will also avoid contention, and consequent pinging, on the normal header block when freelist information needs to be updated.
TIP |
When creating tables, indexes, or clusters with multiple freelist groups, put the initial extents into one specific datafile, separate from the extents assigned to the individual instances. By keeping the initial extents, which should not contain any data, as small as possible, you will not need a large file for these extents. You can then assign fine grain locks to the initial datafile. This will prevent false pinging when one of the freelist group header blocks needs to be updated. |
When an instance starts up, its instance number determines which freelist group it will use for any segment defined with multiple groups. So, for the table shown in Figure 27.8, instance 1 would look for a freelist it could use on the first freelist group block.
FIG. 27.8
Table with four freelist
groups.
Page 682
Blocks are allocated to a specific freelist group in one of two ways. You can use the ALTER TABLE...ALLOCATE EXTENT command to assign the extent to a specific freelist group using the INSTANCE clause. All blocks in such an extent will be on the freelist for the designated instance number, or you can use the grouping factor with releasable locks, as in GC_FILES_TO_ LOCKS="10=0!20", to allocate sets of blocks to an instance. When an instance needs a new block and has none on its freelists for the segment in question, the number of blocks defined by the grouping factor is assigned to the requesting instance's freelist group and allocated to the same PCM lock. Figure 27.9 shows how these two mechanisms work.
FIG. 27.9
Allocation of blocks to
freelist groups.
It is not safe to assume that a block will always belong to just one instance when using freelist groups. Once a block is allocated to a freelist group, it stays on its associated freelist until it fills to its PCTFREE value, just the same as any other block. If the space used in the block subsequently falls to its PCTUSED value, the block is returned to the freelist group of the instance that makes the change. This instance may not necessarily be the one that originally had the block on one of its freelists. Similarly, if you export and then import the rows from a segment with multiple freelist groups, the rows are all placed on blocks from the freelist group belonging to the instance performing the import. They will not necessarily, therefore, go back to their original freelists.
Finally, if you do not assign instance numbers using the initialization parameter, you have no control over which instance will acquire which freelist group. A block on a freelist for instance 1, for example, remains on that freelist across instance startups, regardless of which instance is designated as number 1.
If you choose to allocate extents manually to each instance freelist group, you need to prevent Oracle from adding extents of its own. This is because Oracle assigns its own extents to the