There are three initialization parameters that control the allocation of PCM locks in an OPS environment:
- GC_FILES_TO_LOCKS
Controls PCM lock allocation to datafiles. The value of this parameter must be the same for all instances of an OPS database.
- GC_RELEASABLE_LOCKS
Allocates releasable locks. The value of this parameter may be different for each instance of an OPS database.
- GC_ROLLBACK_LOCKS
Specifies the number of locks available for each rollback segment that is being modified concurrently. The value of this parameter must be the same for all instances of an OPS database.
The following sections describe these parameters, how you can use them to best advantage, and how you can verify lock allocation.
This parameter specifies the manner in which PCM locks are allocated to different datafiles. It specifies the number of locks allocated to a specific file, and/or to a set of datafiles, except for those files that contain rollback segments. The GC_ROLLBACK_LOCKS parameter is used to allocate locks to files with rollback segments.
The syntax for the GC_FILES_TO_LOCKS parameter is rather complex and looks like this:
GC_FILES_TO_LOCKS = "LOCK_ALLOCATION[:LOCK_ALLOCATION...]" LOCK_ALLOCATION := FILE_LIST=num_locks[!blocking][R][EACH] FILE_LIST :=fileid[-fileid][,fileid[-fileid]...]
- LOCK_ALLOCATION
Allocates a specific number of locks to one or more files. Multiple LOCK_ALLOCATION entries are permissible and should be separated by colons.
- FILE_LIST
Can be a single file ID number or a range of file ID numbers (fileid). The ID numbers of your datafiles can be obtained from the DBA_DATA_FILES data dictionary view. Multiple ranges of file IDs may be specified, but one file ID cannot be referenced more than once. Use commas to separate ranges of file IDs.
- num_locks
Specifies the number of locks to be allocated to the files specified in FILE_LIST.
- blocking
Specifies the blocking factor that determines the number of contiguous data blocks that each PCM lock protects. The default blocking factor is 1.
- R
Specifies that the locks are releasable and are to be included in the number specified by the GC_RELEASABLE_LOCKS parameter. Otherwise, the locks will be fixed.
- EACH
Specifies that the number of locks is for each file in the file list. If EACH is not specified, then the locks are allocated collectively to all the files in the list. Allocating locks collectively results in each lock’s protecting one or more blocks in each of the datafiles listed.
This section shows several sample GC_FILES_TO_LOCKS settings. Each of the following examples assumes the use of a hypothetical database with seven files. Table 8.7 shows the file ID numbers, the number of blocks, and the tablespace name for each of those seven files.
Table 8-7. FILE_ID and Block Count for the Files in the Example Database
|
FILE_ID |
Blocks |
Tablespace Name |
|---|---|---|
|
1 |
1000 |
SYSTEM |
|
2 |
1000 |
TOOLS |
|
3 |
2000 |
USERS |
|
4 |
2000 |
APP1 |
|
5 |
1000 |
APP2 |
|
6 |
2000 |
RBS (for rollback segments) |
|
7 |
1000 |
TEMP |
Don’t include the datafiles containing rollback segments in the GC_FILES_TO_LOCKS parameter, because locks for rollback segments are allocated using the GC_ROLLBACK_LOCKS parameter. In addition, don’t include the datafiles comprising temporary tablespaces in GC_FILES_TO_LOCKS, because temporary segments are not shared. Thus, none of our examples include file IDs 6 or 7 in the GC_FILES_TO_LOCKS specification.
The following examples show various permutations of the syntax for the GC_FILES_TO_LOCKS parameter. Most of these examples aren’t complete, because they don’t illustrate lock allocation for all the files. They just illustrate various features of the parameter specification syntax. Example 8, however, which happens to be the last example, is complete. Example 8 illustrates lock allocation to each of the files listed in Table 8.7.
GC_FILES_TO_LOCKS = "1=10:2-3=20:4-5:20EACH"File 1 has been allocated 10 hashed locks. There are 1000 blocks in the file, so each lock covers 100 blocks.
Files 2 and 3 together have been allocated 20 locks. Each lock covers blocks from both files. For file 2, each lock covers 50 blocks. For file 3, each lock covers 100 blocks. In total, each lock covers 150 blocks.
Files 4 and 5 each have been allocated 20 locks. For file 4, each lock covers 100 blocks. For file 5, each lock covers 50 blocks.
All of the locks allocated in this example are fixed hashed locks. If the number of blocks in the file is not evenly divisible by the number of locks requested, then the number of blocks covered by each PCM lock may vary by one. For example, if file 1 with 1000 blocks is protected by six PCM locks, then four PCM locks will cover 167 blocks each, and the other two PCM locks will cover 166 blocks each.
GC_FILES_TO_LOCKS = "2=20R:3-5=20REACH"Because R has been used in the specification, file 2 has been allocated 20 releasable hashed locks. Files 3, 4, and 5 each have been allocated 20 releasable hashed locks. As illustrated in the syntax diagram, REACH is valid when you want to combine R and EACH. You cannot, however, use EACHR.
GC_FILES_TO_LOCKS = "0=200:1=100"In this example, 200 locks are allocated to file ID 0. Note that is not a real file ID. You allocate locks to file ID in order to reserve a pool of locks to use for files that are not explicitly mentioned in the GC_FILES_TO_LOCKS parameter.
100 locks are allocated for file 1. All remaining files use locks from the pool of 200 locks that was allocated using a file ID of 0. All the locks, both those in the pool and those for file ID 1, are fixed locks.
GC_FILES_TO_LOCKS = "1=100"File 1 is allocated 100 fixed locks. Unlike the previous example, no locks have been set aside in a pool using a file ID of 0, so there is no default bucket of hashed locks available. Thus, by default, releasable fine-grained locks are allocated to all remaining files. These locks are included in the number of locks specified by the GC_RELEASABLE_LOCKS parameter.
GC_FILES_TO_LOCKS = "1-3=20EACH:4=0"The GC_FILES_TO_LOCKS parameter also can specify the use of releasable fine-grained locking for a file by explicitly allocating zero locks to that particular file. For file ID 4, the number of locks allocated is zero. This indicates that releasable fine-grained locks should be used for this file. These locks are allocated from the pool of releasable locks specified using the GC_RELEASABLE_LOCKS parameter.
GC_FILES_TO_LOCKS = "1=1000"File 1 has 1000 blocks, and we have allocated 1000 locks to it. This results in fixed fine-grained locks (one lock per block) for file 1.
GC_FILES_TO_LOCKS = "1=100!5"Locks allocated to file 1 are set with a blocking factor of 5. In this case, the granularity is 10 blocks per lock. However, each lock protects five contiguous blocks in one location and another five contiguous blocks in another location. A blocking factor like this would be useful for an application in which contiguous blocks are often referenced together. In such a case, specifying the proper blocking factor may result in fewer lock operations.
Tip
The GC_FILES_TO_LOCKS parameter setting must be the same for all OPS instances accessing a database. This parameter cannot be changed in one instance at a time, because the changed instance can’t be restarted with a setting that doesn’t match the others. Thus, if you want to change GC_FILES_TO_LOCKS, you must stop and restart all OPS instances. This makes it a difficult parameter to tune in OPS implementations that also have high availability requirements.
GC_FILES_TO_LOCKS = "1=100:2=0:4=100R:5=1000:0=500"GC_RELEASABLE_LOCKS = 5000This example summarizes various lock allocation types and granularities for our hypothetical database. It’s a complete example that illustrates lock allocation to all the files listed in Table 8.7 (except for the files for the temporary tablespace and rollback tablespace), and both the GC_FILES_TO_LOCKS and GC_RELEASABLE_LOCKS parameters have been included. The next several paragraphs cover each piece of this scenario in detail. The results then are summarized in Table 8.8.
File 1 is allocated 100 locks to cover 1000 blocks. These are fixed hashed locks.
No hashed locks are allocated to file 2. Thus, releasable fine-grained locks are used. These count against the 5000 locks specified by the GC_RELEASABLE_LOCKS parameter.
File 4 is allocated 100 hashed locks. The R after the 100 makes them releasable locks.
File 5 is allocated 1000 hashed locks covering 1000 blocks. This results in fine-grained locking being used, since there is one lock for every block.
There is no specific allocation for file 3. The entry 0=500 results in 500 fixed locks being placed into the default bucket. Some of these will end up being allocated to file 3.
Table 8-8. Lock Allocation for Example 8
|
File Number |
File Size in Blocks |
Value Specified in GC_FILES_TO_LOCKS |
Lock Type |
Granularity |
|---|---|---|---|---|
|
1 |
1000 |
100 |
Fixed hashed |
1 lock per 10 blocks |
|
2 |
1000 |
0 |
Releasable fine-grained |
1 lock per block |
|
3 |
2000 |
NONE |
Fixed hashed lock from default bucket of 500 locks |
1 lock per 4 blocks |
|
4 |
2000 |
100R |
Releasable hashed |
1 lock per 20 blocks |
|
5 |
1000 |
1000 |
Fixed fine-grained |
1 lock per block |
This parameter specifies the number of releasable locks that are allocated. The default value for this parameter is equal to the number of DB_BLOCK_BUFFERS. Files are protected by releasable locks when the GC_FILES_TO_LOCKS specification has these characteristics:
The file ID is specified in the GC_FILES_TO_LOCKS parameter with an R indicator. Those files use releasable locks.
The file ID is specified in the GC_FILES_TO_LOCKS parameter with a value of for the number of locks. For example, a GC_FILES_TO_LOCKS = “2=0” specification means that file 2 is protected by releasable locks.
The file ID is not specified in the GC_FILES_TO_LOCKS parameter, and a default bucket has not been allocated using a file ID of 0.
The GC_ROLLBACK_LOCKS parameter controls the number of locks used to protect rollback segment blocks. The format of the GC_ROLLBACK_LOCKS parameter specification is similar to that of the GC_FILES_TO_LOCKS parameter. However, unlike datafiles, rollback segments do not share locks. Let’s assume that, in addition to the SYSTEM rollback segment, our hypothetical database has four rollback segments named R1, R2, R3, and R4. The following specification then allocates 40 locks to each rollback segment, including the SYSTEM rollback segment, for a total allocation of 200 fixed locks.
GC_ROLLBACK_LOCKS = "0-5=40EACH"
Rollback segments are identified in the GC_ROLLBACK_LOCKS parameter by their rollback segment ID. You can get that from the SEGMENT_ID column in the DBA_ROLLBACK_SEGS data dictionary view.
The data dictionary view FILE_LOCK displays the allocation of locks to datafiles. This display will match what was specified in the GC_LOCKS_TO_FILES parameter setting and can be used to verify lock allocation. The following example shows a sample GC_FILES_TO_LOCKS setting together with the results that you would get from querying the FILE_LOCK view with that parameter setting in effect:
GC_FILES_TO_LOCKS = "1=100:2=0:3=400!5:4-5=200R" SQL>SELECT file_id, ts_name, start_lk, nlocks, blocking2FROM file_lock3ORDER BY file_id;FILE_ID TS_NAME START_LK NLOCKS BLOCKING ---------- ---------------- ---------- ---------- ---------- 1 SYSTEM 0 100 1 2 TOOLS 0 0 1 3 USER 100 400 5 4 APP1 500 200 1 5 APP2 500 200 1 6 RBS 0 0 1 7 TEMP 0 0 1
Column START_LK shows the starting lock number for each file, while NLOCKS shows the total number of locks allocated to that file. The total number of locks allocated overall is determined by summing the NLOCKS column for all rows with a different START_LK value. If two rows have the same START_LK value, then the files represented by those rows share the same locks. Notice that in our example, files 4 and 5 have the same START_LK value. That indicates that they share the same locks. Each lock beginning with number 500 and extending through number 699 protects one or more blocks in both file 4 and file 5.