In an Oracle Parallel Server environment, several database resources are shared across multiple instances. As we’ve discussed previously, data blocks are protected by PCM locks. All other shared resources are protected by non-PCM locks. When parallel server is enabled, local enqueues become global. These are included in the non-PCM locks. The most important non-PCM lock types are the following:
- Transaction locks
These locks are row-level locks acquired by transactions during the execution of INSERT, UPDATE, DELETE, and SELECT FOR UPDATE statements. These locks are acquired in exclusive mode and are held for the life of the transaction. Transaction locks are released when the transaction is committed or rolled back.
- DML locks
These locks protect an entire table during a DML or DDL operation. The number of DML locks is controlled by the DML_LOCKS initialization parameter. If this parameter is set to zero in one instance, then it must be set to zero in all of the instances. Setting DML_LOCKS to zero disables table locking for the whole database. The advantage of setting this parameter to zero is that doing so eliminates contention and overhead for these locks. However, the disadvantage is that you cannot perform any DDL operations such as DROP TABLE, CREATE INDEX, LOCK TABLE, and so forth.
- System Change Number lock
Oracle uses a number called the System Change Number (SCN) to record the proper sequencing of changes to the database. The SCN is global to all instances in a parallel server database. To reduce communication overhead, a cached copy of the SCN is kept in the memory of each instance. Each instance may increment the SCN as a result of a change. Oracle uses a lock known as the SCN lock to protect the global SCN. To increment the global SCN, a process must acquire this lock in exclusive mode.
- Library cache locks
The library cache resides in the SGA of each instance and holds the parse trees and execution plans for SQL statements and compiled PL/SQL program units. This facilitates reuse of identical SQL statements executed by multiple users. When a process parses a DML or DDL statement, it acquires a library cache lock on the objects referenced in the statement. Similarly, when a PL/SQL program unit is compiled, library cache locks are acquired on the objects referenced. These locks are released after completion of parsing or compilation.
- Dictionary cache locks
A data dictionary cache is maintained in the SGA of each instance in a parallel server database to hold information from the data dictionary. This information includes the definition of database objects, integrity constraints, user security, and so on. Since this information is replicated in the SGA of each instance, there is a danger of its getting out of sync when one instance drops a table or grants a privilege to a user. Dictionary cache locks are used to synchronize between the dictionary caches of multiple instances.
- Database mount lock
In Oracle Parallel Server, multiple instances mount the database in shared mode. An instance acquires the mount lock in shared mode before mounting the database. If an instance acquires the mount lock in exclusive mode, it mounts the database in exclusive mode and no other instance can mount the database.
Non-PCM locks constitute 5 to 10 percent of total locks. You do not have much control over non-PCM locks, but you must take them into account when you’re performing capacity planning.