Enqueue Waits

Waits for enqueue are not specific to RAC environments. However, they have a higher impact in performance than in the single instance environments because the enqueues are globally coordinated. Enqueue management is handled at the Global Enqueue Services (GES) layer, which is responsible for enqueue operations. The initial allocation of the GES resources is based on the following initialization parameters:

• DB_FILES

• DML_LOCKS

• ENQUEUE_RESOURCES

• PROCESSES

• TRANSACTIONS

The number of Global Enqueue Resources is approximately calculated as

(DB_FILES + DML_LOCKS + ENQUEUE_RESOURCES + PROCESSES + TRANSACTIONS + 200) * (1 + (N-1) / N), where N is the number of nodes participating in the cluster.

The actual number of GES resources allocated during the instance startup will be printed in the alert log. The same is also visible in the V$RESOURCE_LIMIT view:

 Global Enqueue Service Resources = 4086, pool = 2 
 Global Enqueue Service Enqueues = 5947 

Most Common Enqueues

Because the resources are globally coordinated in the RAC setup, tuning enqueue waits becomes a critical component in RAC related waits. However, you need to keep in mind that there will be a small amount of discrepancy between the values of the enqueue waits in the V$SYSTEM_EVENT and V$ENQUEUE_STAT. V$SYSTEM_EVENT is incremented at every enqueue wait, and the X$KSQST (V$ENQUEUE_STAT) is incremented only once per wait ‚ that is, the enqueue wait will be incremented only once, even if it has n timeouts during that wait.

The enqueues that protect the dictionary, database-wide space management, or global library cache/shared pool are most commonly seen in RAC environments. These affect the scalability and performance of the application if they are not properly tuned .

CU Enqueue

This enqueue is used to serialize the cursor binding in the library cache. The parsed cursors in the library cache can be shared between processes if they are having the same bind variables. If literals are used instead of the bind variables, the init.ora parameter CURSOR_SHARING can be used to convert the literals to system-defined bind variables. Usage of bind variables is highly recommended for application scalability and performance because it greatly helps reduce the parse time for the SQL statements. The CU enqueue is used to protect the cursor from binding more than one process at the same time.

CF Enqueue

A control file is used as a single point of synchronization component in the database. It is updated whenever there is a structural change in the database, such as adding a tablespace or a data file, performing recovery-related operations like checkpoint and log switching, and the performing regular database maintenance operations like startup and shutdown. Few operations such as adding a data file or tablespace will require the control file transaction CF enqueue in exclusive mode. During that time the other processes, which requires the enqueue at the same or higher level, will wait 15 minutes and retry the operation. The timeout can be defined by the parameter _CONTROLFILE_ENQUEUE_TIMEOUT, which defaults to 900 seconds.

Contention for CF enqueue may affect the database operations in the RAC environment because the enqueue is deadlock sensitive. Table 8-3 shows the common operations, which require the CF enqueue and respective modes required.

Table 8-3: Operations Requiring CF Enqueue

Operation	Mode Held
Switching logfiles	Exclusive
Updating checkpoint information for datafiles	Exclusive
Opening a log file for redo reading during recovery	Shared
Getting information to perform archiving	Shared
Performing crash recovery	Exclusive
Performing instance recovery	Exclusive
Performing media recovery	Exclusive
Creating a database	Exclusive
Mounting a database	Shared
Closing a database	Shared
Adding a log file or log file member	Exclusive
Dropping a log file or log file member	Exclusive
Checking information about log file group members	Shared
Adding/dropping a new datafiles	Exclusive
Beginning/ending a hot backup	Exclusive
Checking whether datafiles are in hot backup mode after a crash (first shared, then exclusive)	Shared
Executing an ALTER DATABASE BACKUP CONTROLFILE TO TRACE	Shared
Opening the database (control file) exclusive by first instance to open , shared by subsequent instances	Exclusive
Renaming datafiles or log files	Exclusive
Marking a control file as valid and mountable	Exclusive
Handling an error encountered in the control file	Exclusive
Validating data dictionary entries against matching control file records for file entries	Exclusive
Updating control file format after a software upgrade	Exclusive
Scanning for log file info by log sequence #	Shared
Finding the highest-in-use entry of a particular control file record type	Shared
Getting information about the number of log entries and their lowest /highest sequence number and log file numbers	Shared
Looking up a control file record matching a given filename	Shared
Making a backup control file	Exclusive
Dumping the contents of the control file during debugging	Shared
Dumping the contents of the current redo log file during debugging	Shared
Dumping the contents of the redo log header	Shared
Dumping the contents of datafile headers	Shared

In general, contention for CF enqueue may be an indication of a slower I/O subsystem/device. Excessive waits and timeouts for this enqueue sometimes terminate the instance with ORA-0600 [2103] error, especially when it has waited for the background process for checkpoint. Another reason for contention for this enqueue is the number of data files. With fewer data files, the checkpoint process has a smaller workload. Having more DB_WRITERS or enabling the asynchronous I/O will help reduce the number of waits for this enqueue.

HW Enqueue

Whenever new data is inserted into the data segment, the segment grows to accommodate the newly inserted values. High watermark defines the boundary between used and unused space in the segment. Free space above high watermark is used only for direct path inserts, bulk loads, and parallel inserts. Space below high watermark is used for the conventional method of inserts .

During heavy volume inserts, or bulk loads, the segment grows rapidly and the high watermark gets moved up. The HW enqueue serializes this movement. Other than the normal loads, the ALTER TABLE ALLOCATE EXTENT command also requires the HW enqueue to allocate a new extent. In the V$LOCK view column ID1 represents the tablespace number, and column ID2 represents the relative DBA of the segment header of the object for which the space is allocated.

Altering the storage definitions of the segment to larger extent will reduce the number of extents, which in turns reduces the number of HW enqueue conversions. Other than that, preallocating extents with a larger size also helps reduce the contention of this enqueue. During space allocation, the high watermark is moved by 5 blocks or multiples of 5 blocks at a time. These 5 blocks are defined by the parameter _BUMP_HIGHWATER_MARK_COUNT, which default to 5. Changing the value from 5 to a higher number will also reduce contention for this enqueue. In addition to changing the underscore parameter _BUMP_HIGHWATER_MARK_COUNT , increasing the number of freelists also helps because it moves the high watermark in the multiples of freelists.

PE Enqueue

PE enqueues are rarely seen in a well-managed RAC environment. This enqueue is used during ALTER SESSION SET parameter=value statements and it protects the memory structures during the parameter changes and frequent changes to SPFILE. More sessions connecting and disconnecting to the database (not using connection pooling) will also increase the contention for this enqueue. Well-defined connection management, connection pooling, MTS or Shared Servers will help reducing the contention for this enqueue.

Sequence Enqueues: SQ and SV

Sequences are Oracle ‚ s number generators that can be used in the programs or SQL statements. Contention for SQ (Sequence) enqueue is inevitable when too many sessions are accessing the sequence generator because the SELECT statement for the next sequence number causes an update in the data dictionary unless the sequence is defined with the CACHE option. Beware of gaps in the sequences because the cached sequences may be lost during instance shutdown or shared pool flushing. However, caching sequences greatly increases the performance because it reduces the number of recursive calls.

When the sequences are cached, each instance will cache the sequence up to cache size, but the order of the sequence number is not guaranteed . In this case, the ORDER option with the CACHE option can be used to get the cached sequences in order. When cached sequence with order is used, all instances cache the same set of sequences, and the SV enqueue is used to verify that the sequence value [SV] and the order are guaranteed.

The following options can be used for sequences, listed from good to best order:

 NOCACHE ORDER 
 NOCACHE NOORDER 
 CACHE   ORDER 
 CACHE   NOORDER   --Best Performance 

TX Enqueue

TX enqueues are used to serialize the database transactions and to get a consistent view throughout the lifecycle of the transaction. The row-level locks are implemented using the TX enqueue where the change vectors to reverse the transactions are written in the undo segments. The address of the undo blocks are written in the data blocks. So that the reader (or process) looking for a consistent view can follow the address and get the consistent view of that data during that time.

This enqueue is typically used during transactions and index block splits . Sometimes heavy contention for TX enqueue can be an indication of bad application design that unnecessarily lock the rows. The index of the monotonically increasing key increases the contention for the TX enqueue. Any waits that are not for ITLs (Interested Transaction Lists) are also indicators of the TX waits. The waits for the transaction slots can be easily identified by querying the V$SEGMENT_STATISTICS:

 select owner, 
 object_name 
 from   v$segment_statistics 
 where  statistic_name = 'itl waits' and value  > 0 

If more waits are seen for the ‚ITL waits ‚ event, the objects should be rebuilt using higher INITRANS. Keep in mind each INITRANS will take 24 bytes of space in the variable header of the data block and adding more INITRANS potentially wastes space in the data blocks at the cost of concurrency. Oracle Database 10 g has a separate wait event for ITL waits.