Previous | Table of Contents | Next

Page 669

being managed by the lock. After that, the lock is returned to the DLM for use by another—or even the same—instance, should it be needed.

In general, you should use hash locks for query-intensive applications because instances can acquire the same locks in shared mode. As long as the blocks are not updated anywhere, these locks will be held indefinitely by every instance performing queries. You can define these hash locks to span many blocks, reducing the total number of locks needed for the instance along with their associated overhead. If you also define the locks to cover contiguous blocks, you will tend to improve the performance of full table scans which read sets of adjacent blocks. In transaction intensive databases, you should consider fine grain locks. Each of these locks covers a single block, or possibly a few related blocks. Consequently, the possibility for false pinging is greatly reduced.

CAUTION
You should not automatically use fine grain locks unless you expect contention for PCM locks between instances. Fine grain locks cause additional lock conversions because they are released automatically when the instance is done with the related blocks, even if another instance is not in need of them.

Of course, if you are using parallel server on a two node system for failover purposes, where you will use only one instance at a time, you can use a minimal number of hash locks regardless of the type of database activity. Locks do not serve any useful inter-instance function in a parallel environment when only one instance has active users.

How do you set values for the GC_DB_LOCKS, GC_RELEASABLE_LOCKS, and GC_FILES_TO_LOCKS parameters to achieve the required PCM lock distribution? First, you should be aware that in Version 7.3 and Oracle8 the default behavior is slightly different. In Version 7.3, the default mechanism is hash locking and the GC_DB_LOCKS parameter sets the total number of hash locks available to the instance. If you don't assign them specifically, the locks are distributed evenly across all the datafiles. Figure 27.5 illustrates how a database requiring only 10 hash locks would have them assigned by the default behavior—not that such a database is likely to exist outside of this example. In Oracle8, the default locking mechanism is fine grain locking, with the number of releasable locks being the same as your DB_BLOCK_BUFFERS value.

When assigning PCM locks, you do not need to assign any locks to tablespaces in the following categories:

Simply leave the file ID numbers for these tablespaces out of the GC_FILES_TO_LOCKS parameter to avoid assigning unnecessary locks.

Page 670

FIG. 27.5
Default allocation of
hashed PCM locks.



TIP
By placing all of your read only segments into their own tablespaces, you can make those tablespaces read only and thus reduce the number of locks needed. Similarly, you can decrease the number of locks you have to assign by using the TEMPORARY keyword when defining tablespaces for your temporary segments. This will prevent any other type of segment from being created in these tablespaces, so you can safely assign zero PCM locks to their datafiles.
Finally, you should reserve tablespaces to contain only rollback segments because you will not need to assign PCM locks to the related datafiles, just to the rollback segments themselves. However, there is no keyword to ensure these tablespaces are kept free of other types of segment.

PCM Locks in Version 7.3 If you assign hash locks with the GC_DB_LOCKS parameter in Version 7.3, they are assigned evenly across the datafiles as shown in Figure 27.5 above. To change this behavior, you allocate some of these locks to your datafiles in the proportions best suited to your applications and database design. You must leave some locks unassigned so that Oracle has a pool of spare locks to allocate to unidentified datafiles, including any you might need to add. Use the GC_FILES_TO_LOCKS parameter to assign the hash locks, with the following syntax:

Page 671

GC_FILES_TO_LOCKS = "file_list=locks:file_list=locks:file_list=locks "

where


     file_list     is a file ID number, or multiple file ID numbers separated by commas for
                   individual files, or dashes for inclusive sets.


     locks     is the number of locks.

You can include as many different file lists as you need, with a colon separating each file list and lock count value. Note that the complete set of entries must be enclosed in double quotation marks and there must be no blank spaces in the entry. Here is an example of a valid parameter which assigns a total of 1,005 locks:

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

File 1 receives 100 locks; files 2 through 5 share another 500; files 6, 8, and 12 share 400 more locks; and files 7, 9, 10, and 11 share just 5 locks. Figure 27.6 shows how the five locks on files 9, 10, and 11 are allocated to these files. Any files that are not listed in the parameter are covered by spare locks. These will be the balance of the locks assigned by GC_DB_LOCKS but not allocated by GC_FILES_TO_LOCKS. In the above case, there would be GC_DB_LOCKS minus 1,005 spare locks, which would be allocated to files in the default round-robin fashion.

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

You can see that the spare locks are assigned in a round-robin fashion using the same default mechanism depicted in Figure 27.5. If you want the locks to cover contiguous sets of blocks, you can use the grouping identifier, the exclamation point (!), to indicate the number of blocks per group. For example, to cover 10 blocks with each hash lock on files 7 and 9 through 11 from the previous example, you would rewrite the parameter as:

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

Figure 27.7 shows you how these locks are allocated to the first few blocks in each of the files.

Previous | Table of Contents | Next