|Oracle9i Real Application Clusters Concepts
Release 1 (9.0.1)
Part Number A89867-02
This chapter details the interinstance coordination activities that take place in Real Application Clusters. The Global Enqueue Service (GES) coordinates resource, data, and interinstance data requests. This chapter describes the details about how Oracle coordinates these resources with enqueues generated by the GES.
This chapter primarily discusses GES enqueues. Global Cache Service (GCS) resources are discussed in Chapter 5, "Cache Fusion and the Global Cache Service".
Topics in this chapter include:
Understanding how Real Application Clusters synchronize caches across instances can help you understand the overhead affecting system performance. Consider a five-node Real Application Clusters environment where a user drops a table on one of the nodes. Since each of the five dictionary caches has a copy of the definition of the dropped table, the node dropping the table from its cache must also cause the other four dictionary caches to drop their copies of the table. Real Application Clusters handles this automatically through the GES. Users on the other nodes are notified of the change in resource status.
There are significant advantages to having each node store library and table information. Occasionally, the
DROP TABLE statement forces other caches to be flushed, but the brief effect this has on performance does not necessarily diminish the advantage of having multiple caches.
Each instance of Real Application Clusters has a dictionary cache, or row cache, containing data dictionary information in its System Global Area (SGA). The data dictionary structure is the same for Oracle instances in cluster databases as for instances in exclusive mode. Real Application Clusters uses global enqueues to coordinate data dictionary activity among multiple instances.
Well-designed applications used in conjunction with Real Application Clusters can achieve significant speed up and scale up.
The GES tracks the status of all Oracle enqueuing mechanisms. Oracle only creates global enqueues if you start an Oracle instance with Real Application Clusters enabled. The exception to this is the mount lock. The GES synchronizes global enqueues by communicating the status of an enqueue's status to all instances within a cluster.
An instance owns a global enqueue that protects a resource, such as a data block or data dictionary entry, when the resource enters the SGA of an instance. The GES manages resources accessed by more than one instance.
Apart from the mechanism to guarantee coherence of cached database blocks, other layers in Oracle must globally protect their data structures. These other layers are, for example, the:
Each layer has specified needs, semantics, and protocols for access to their respective data structures. All layers of Oracle use the service of the GES API to acquire, convert, and release resources.
You do not need to tune global enqueues. In addition, the GES automatically calculates memory requirements when an instance starts up. Oracle uses all parameters affecting global enqueue resources that existed in previous releases to calculate exact resource memory requirements. GES resources include transaction locks, table locks, library cache locks, and dictionary cache locks.
Global enqueue resources are sometimes held only for a very short time and quickly released. Normally, TX locks, for example, are acquired when a transaction starts. They are released when the transaction commits. In some cases they are used to signal an event, such as in the case of library cache locks, where DDL statements may cause invalidations of library cache objects. However, you do not have to tune or monitor these resources.
Aside from the global enqueue resources in the previous list, Oracle databases have a variety of other global enqueue resource types. However, these resources do not have such an important role as those listed. The next section more closely examines the important global enqueue resource types.
The GES controls access to datafiles and control files. The GES also controls library and dictionary caches, and perform various types of communication between instances. These enqueues do not protect datafile blocks. Examples of GES enqueues are Data Manipulation Language enqueues, transaction locks, and Data Definition Language (DDL) locks or dictionary locks. GES processing includes coordination for enqueues other than data blocks.
The resources described in the following section are local in single instance Oracle, but global in the GES.
The resources managed by the GES include:
This section explains how Oracle uses GES enqueues to manage concurrency for transactions, tables, and other entities within an Oracle environment.
A transaction lock is acquired in exclusive mode when a transaction initiates its first row level change. The lock is held until the transaction is committed or rolled back. The system monitor (SMON) also acquires a transaction lock in exclusive mode when recovering (undoing) a transaction. Transaction locks are used as a queuing mechanism for processes awaiting the release of an object locked by a transaction in progress.
Table locks are GES locks that protect entire tables. A transaction acquires a table lock when a table is modified by one of the following statement types:
SELECTs with the
FOR UPDATE clause, and
LOCK TABLE. A table lock can be held in any of several modes: null (N), row share (RS), row exclusive (RX), share lock (S), share row exclusive (SRX), and exclusive (X).
When an instance attempts to mount the database, a table lock is used to ensure that all participating instances either have
DML_LOCKS = 0 or
DML_LOCKS != 0. If they do not, then Oracle displays error ORA-61 and the mount attempt fails. Table locks are acquired during the execution of a transaction when referencing a table with a DML statement so that the object is not dropped or altered during the execution of the transaction. This occurs if and only if the
DML_LOCKS parameter is nonzero.
You can also selectively turn table locks on or off for a particular table, using the statement:
If you set
DML_LOCKS to zero, then DDL operations are not allowed. The same is true for tables that have disabled table locks.
Oracle9i Real Application Clusters Deployment and Performance for more information about minimizing instance locks and disabling table locks for improved performance
When a database object (such as a table, view, procedure, function, package, package body, trigger, index, cluster, or synonym) is referenced during parsing or compiling of a structured query language (SQL), (Data Manipulation Language (DML) or Data Definition Language (DDL), PL/SQL, or Java statement, the process parsing or compiling the statement acquires the library cache lock in the correct mode. In Oracle9i, the lock is held only until the parse or compilation completes (for the duration of the parse call).
The Data Dictionary Cache contains information from the data dictionary, the metadata store. This cache provides efficient access to the data dictionary.
Creating a new table, for example, causes the metadata of that table to be cached in the data dictionary. If a table is dropped, then the metadata needs to be removed from the data dictionary cache. To synchronize access to the data dictionary cache, latches are used in exclusive mode and in single node cluster databases. Global enqueues are used in cluster database mode.
In Real Application Clusters, the data dictionary cache on all nodes can contain the metadata of a table that gets dropped on one instance. The metadata for this table needs to be flushed from the data dictionary cache of every instance. This is performed and synchronized by using global enqueues.
The Database Mount Lock shows whether an instance has mounted a particular database. This lock is only used with Real Application Clusters and it is the only multi-instance lock used by the Real Application Clusters software in exclusive mode, where it prevents another instance from mounting the database in shared mode.
In Real Application Clusters single node cluster databases, this lock is held in shared mode. Another instance can successfully mount the same database in shared mode. In exclusive mode, however, another instance is unable to obtain the lock.
Oracle9i Database Reference or more information about transaction locks, table locks, library cache locks, and dictionary cache locks.
The GES performs deadlock detection to all deadlock sensitive enqueues and resources. It does not control access to tables or objects in the database itself. Real Application Clusters uses the GES to coordinate concurrent access across multiple instances to resources such as data blocks and rollback segments.