So far in this chapter we’ve discussed various types of PCM locks, their characteristics, and the initialization parameters used to allocate the locks. This section discusses key criteria that you should consider when deciding on PCM lock allocation in your environment and guides you in deciding what types of locks to use in different situations.
There are a number of strategies that you should keep in mind when deciding on PCM lock allocation. These include:
Allocating PCM locks based on blocks in cache, not on disk
Planning for more datafiles
Treating each datafile separately
Giving special treatment to the SYSTEM tablespace
Separating read-only and modifiable data
Separating indexes from tables
Adding locks when you add a datafile
The following sections discuss each of these strategies.
Data on the disk does not contribute to the problem of maintaining cache coherency. Data in the cache does. In addition, however, clean data (data that has not been modified) in the cache and locally accessed data in the cache also do not impact cache coherency. Consequently, you should base the number of instance locks on the amount of data in the cache that is being modified globally, not the total amount on disk.
For example, let’s assume that an OPS database has one billion data blocks distributed among several datafiles. Allocating PCM locks for all of these blocks may not be practical. You might not have the memory to allocate one billion locks. You can maximize the efficiency of your locking scheme by analyzing your data and allocating locks accordingly.
You need to know the usage patterns of your data. You can segregate data into separate datafiles based on their usage patterns. Then you will be able to allocate different types and numbers of locks to each datafile based on the usage pattern of the blocks in that particular datafile. Allocate very few locks to the blocks accessed only locally, since these blocks will not be required by other instances. Similarly, read-only data can be protected by only a single PCM lock. This single PCM lock can be owned by multiple instances in shared mode simultaneously to read the data. Data modified only occasionally can be protected by a few locks. However, frequently modified data blocks need better protection and should have more locks allocated in order to avoid false pings. Try to allocate fine-grained locks to datafiles containing frequently modified blocks if you have sufficient memory to do so. The system memory and startup time may set practical limits for the actual number of locks that you can allocate. Refer to the section Section 8.7.4 later in this chapter for more details.
PCM locks are allocated on a per-file basis. Thus, the granularity of lock allocation (the number of blocks per lock) can be varied from one file to the next. If you plan for more tables, and you place tables having different usage characteristics in separate datafiles, then you can vary the granularity of lock allocation based on the particular access pattern of the file. Thus, it is better to plan for more datafiles (and hence tablespaces) in an OPS environment than in a standalone database.
The purpose of lock allocation is to protect data blocks that remain in the database buffers. Thus you should allocate more locks to files that have more blocks in the cache. Also, allocate more locks to files that have higher write activity.
The SYSTEM tablespace contains the data dictionary tables that are read and modified by all instances. Therefore, be generous in allocating locks to the datafile(s) of the SYSTEM tablespace. If practicable, use fine-grained locking for the SYSTEM tablespace.
Separate read-only and modifiable objects into different tablespaces. This will help you to control lock allocation better. Read-only data is protected by locks in shared mode, and the same lock can be held in shared mode by multiple instances. Thus, you should allocate only a few locks (one is good enough) to datafiles containing read-only data and allocate more locks to datafiles containing modifiable data.
Place indexes in datafiles separate from those used for tables, so that separate PCM locks can be allocated for index blocks. Allocate fewer locks to index datafiles when those indexes are modified infrequently.
When new datafiles are added, allocate additional locks for them. This is something you don’t need to worry about in a standalone instance environment but which you should worry about in an OPS environment. Remember to revisit your GC_FILES_TO_LOCKS parameter setting each time you add a datafile.
All of the discussion so far has pertained to the initial lock allocation for a new OPS database. As the database grows in size and as the number of users increases, you must periodically reevaluate your allocation of locks. You may need to redistribute locks as a result of changing usage patterns or add new locks as a result of growth. Several data dictionary views are available to help you identify blocks that have high ping activity and also provide information as to the nature of the contention causing that pinging. You can fine-tune your lock allocation based on the results of monitoring these data dictionary views. Refer to Chapter 10, for details on how to monitor locks and lock activities.
As we’ve explained, there are four different types of PCM locks. Not all types are suitable for all environments. Each type of lock is suitable for a different type of application. You may see all four types of locking in one database, but a single datafile can have only one type of locking associated with it. The following sections describe the strong points of each type of lock and provide examples of situations for which they’re best suited.
One hashed lock protects multiple blocks. The blocks protected by a hashed lock can belong to one or more datafiles depending on the specifications in the GC_FILES_TO_LOCKS initialization parameter. When you share locks between datafiles, make sure that these datafiles belong to the same tablespace. An exception to that rule is when the files contain read-only data. All read-only data can be protected by just one lock.
Hashed locks are beneficial when there is no false pinging involved. Read-only data and data accessed by only a single instance both fall into this category.
Take advantage of the READ-ONLY tablespace feature of Oracle in this situation. A datafile of a read-only tablespace can be protected by a single hashed lock because every instance can own that lock in shared mode.
Tip
Why do you need to allocate even a single PCM lock to the read-only tablespace? If no PCM locks are specifically assigned to the datafile for the read-only tablespace, then either fixed locks from the default bucket or releasable fine-grained locks are going to be allocated to the datafile based on the GC_FILES_TO_LOCK parameter specification. Fixed locks that are allocated from the default bucket could have been better used for protecting another tablespace. If releasable locks are used, then there is the overhead of acquiring and releasing the locks.
In contrast, if only one PCM lock is allocated to the read-only tablespace, then each instance acquiring the single PCM lock in share mode retains the lock with much less overhead.
If a certain set of tables is accessed by one instance only, put them in a separate datafile. Since the blocks in that datafile will always be accessed by one instance, they will never undergo pinging. All of the blocks in that datafile then can be protected by a single hashed lock.
If a table is accessed by multiple instances but is partitioned in such a way that each partition is accessed by only one instance, you can place each partition in a separate datafile and then allocate one hashed lock for each of those.
Fine-grained locks are recommended for datafiles containing blocks that are accessed by multiple instances and blocks that undergo an appreciable amount of false pinging. By allocating one PCM lock for each block, you can avoid false pings entirely.
Once a fixed lock has been acquired by an instance, it is not released unless it’s requested by another instance. Fixed locks are recommended for datafiles containing blocks accessed primarily by one instance, so that locks remain allocated throughout the life of that instance.
Fixed locks also are beneficial for read-only data. In such cases, each instance acquires the lock in shared mode and keeps it for the lifetime of the instance.
If a database is very large, allocating the number of fixed locks sufficient to avoid false pings won’t be practical because of the amount of memory required. In such a situation, releasable fine-grained locks are useful. You can assign releasable fine-grained locks to datafiles that contain globally modified data. Releasable fine-grained locks help to eliminate false pings, and because they are acquired and released as required, they do not require too much memory.
You’ve seen that by allocating additional PCM locks to datafiles you can reduce false pings. If you allocate one lock per block, then you can totally eliminate false pings. However, locks consume memory in the SGA, and allocating large numbers of locks requires extra time during database startup and recovery. Proper lock allocation is a tradeoff. It isn’t practical to allocate one lock for each block in a large database. Thus, design your tablespaces and allocate your locks prudently, so that the available locks will reduce false pings as much as possible.
The maximum number of PCM locks that can be allocated depends on the memory available on the database server. PCM locks are allocated within the SGA, and allocating more locks increases the size of the SGA.
Tip
You can find out how much memory is consumed by a single PCM lock on your platform by performing a small experiment. Increase the locks allocated for a particular file by changing the GC_FILES_TO_LOCKS parameter. Shut down and restart the instance, and then record the size of the SGA. Make certain that you don’t change any other parameters. You can compute the number or bytes consumed by each lock by making a few such changes in succession and each time dividing the difference in the resulting SGA size by the difference in the number of locks allocated. Results may vary by platform. Under IBM’s AIX operating system, for example, PCM locks take 64 bytes each.
Please note that GC_FILES_TO_LOCKS is a global constant parameter and must be identical across all instances. Therefore, when you conduct this experiment in one instance, you must shut down all other instances.