Previous | Table of Contents | Next

Page 672

FIG. 27.7
Allocation of hash PCM
locks where
GC_FILES_TO_LOCKS
equals "...7,9-
11=5!10".

Another option, to save you typing and shorten the length of the parameter string, is to use the keyword EACH to allocate the same number of locks to each of the files in a file list. Rewriting the above example as:

GC_FILES_TO_LOCKS = "1=100:2-5=500:6,8,12=400EACH:7,9-11=5!10"

you would allocate a total of 1,805 locks, with files 6, 8, and 12 each obtaining 400 locks.

If you would prefer to use releasable locks in your Version 7.3 database, you need to modify the global cache parameters slightly from their defaults. First, you need to set the number of fine grain locks required with the GC_RELEASABLE_LOCKS parameter. This must be set no less than

Page 673

the value for your DB_BLOCK_BUFFERS parameter and may be higher if you wish. Your next option is to decide if you want to use all fine grain locks, or if you want to mix fine grain with hash locks. The former is accomplished simply by setting GC_DB_LOCKS to zero. The second option requires setting the individual parameters for each of the lock categories after setting both GC_DB_LOCKS and GC_RELEASABLE_LOCKS to non-zero values.

To use fine grain locks instead of hash locks on a particular category of locks, set the related parameter to zero. The parameters and lock categories you can control are shown in Table 27.1. You can also assign fine grain locks to your database segments by allocating zero locks to them in the GC_FILES_TO_LOCKS parameter. Listing 27.2 is an extract from a parameter file that sets fine grain locking on all segment header blocks as well as on files 13 through 17.

Listing 27.2 Sample Parameter Values to Set Fine Grain Locks on Segment Header Blocks and Specific Datafiles

DB_BLOCK_BUFFERS = 1000
GC_DB_LOCKS = 500
GC_RELEASABLE_LOCKS = 1000
GG_SEGMENTS = 0
GC_FREELIST_GROUPS = 0
GC_ROLLBACK_SEGMENTS = 0
GC_ROLLBACK_BLOCKS = 200
GC_FILES_TO_LOCKS = "13-17=0"

Data blocks from the other files in the database share the locks from the pool of 500 set in GC_DB_LOCKS in the normal round-robin fashion. Actually, there will be a few more than 500 locks available because Oracle uses an internal algorithm to increase the number beyond the value you set in GC_DB_LOCKS. The rollback segment undo blocks also share hash locks, using the pool of 200 established by the GC_ROLLBACK_BLOCKS parameter.

Fine grain locks may not be your best option for segment header blocks, as used in the previous listing, unless you have a very busy database with lots of active instances, such as an MPP platform running an online transaction processing (OLTP) system. However, the default number of hash locks assigned to certain lock categories may be inadequate. The two you should probably change are GC_SEGMENTS and GC_ROLLBACK_SEGMENTS. The former should be set to the total number of tables and indexes you have in your database and the latter to the number of rollback segments.

In Version 7.3, you can assign fine grain PCM locks to cover contiguous sets of blocks as discussed earlier with respect to hash locks. To set the grouping character for fine grain locks in GC_FILES_TO_LOCKS, you must use the value of zero for the number of locks or else the files revert to hash locking. The following listing is taken from a parameter file where both hash and fine grain locks are used, some with and some without the grouping option. The zeroes on the right-hand side of the equals signs enforce fine grain locking:

GC_FILES_TO_LOCKS = "1=50:5-8=100EACH:9-14=0!20:15,18=0:16-17,19-22=1000!10"

Page 674

PCM Locks in Oracle8 In Oracle8, you have many additional options on how to assign locks. First, you should note that the default locking mechanism is fine grain rather than hash, and that Oracle8 has assumed the responsibility for assigning the locks to many lock categories. Second, hash locks can behave as they did in Oracle 7.3 or they can assume the releasable characteristics of fine grain locks. Third, the locks on rollback segment undo blocks can be assigned using similar syntax to the GC_FILES_TO_LOCKS parameter. Once a block is assigned to a particular type of lock, regardless of how it was assigned, it behaves just as it would in Version 7.3. A hash lock will not be relinquished by an instance unless requested to do so by the DLM, on behalf of another instance, while a fine grain lock will be released as soon as the instance finishes working with it.

Table 27.1 identifies the lock categories that Oracle8 has internalized and handles itself with the keyword obsolete in the Oracle8 column. This same column in the table also indicates, with the key word revised, which parameters changed between releases to allow the new Oracle8 functionality. The remainder of this section examines these changes.

First, there is a new option in the GC_FILES_TO_LOCKS parameter that enables you to identify which hash locks you want to be releasable. Include the letter "R" to identify these locks, either by itself or as a prefix to the "EACH" keyword. The entry in the following listing shows the valid uses of this new feature. You can see that the 500 hash locks:

GC_FILES_TO_LOCKS = "1=100:4-6=500R:7,9=200REACH"

shared by files 4 through 6 are releasable, as are the 400 locks assigned to files 7 and 9, each of which have 200 locks.

The second parameter that takes on a new form in Oracle8 is GC_ROLLBACK_LOCKS. Instead of providing a single number for the value, use a format similar to GC_FILES_TO_LOCKS to assign different numbers of locks to each rollback segment. There is a stipulation that rollback segments cannot share locks, so when using this parameter, you must include the EACH option if you specify ranges of rollback segments, as shown in the listing below:

GC_ROLLBACK_LOCKS = "1=50:2=100:3-10=200EACH:11-15=30EACH"

In this parameter, the values on the left of the equals signs are the ID numbers of the rollback segments, not file ID numbers as in GC_FILES_TO_LOCKS. You can correlate the ID numbers to the names of the rollback segments in your database by querying either DBA_ROLLBACK_ SEGMENTS or GV$ROLLNAME. Both of these sources show the undo segment number (USN) or the rollback segment ID number along with the segment name.

If you want to use hash locks in Oracle8, you need to set GC_FILES_TO_LOCKS just as in Version 7.3. Similarly, you can assign hash locks and fine grain locks to groups of contiguous blocks. In fact, the syntax to achieve this is identical in both versions.

The change to GC_RELEASABLE_LOCKS introduced in Oracle8 is only a minor one. You can now set the value to be less than DB_BLOCK_BUFFERS, although this is not recommended.

Previous | Table of Contents | Next