Waits, Enqueues, and Latches - Expert Oracle RAC Performance Diagnostics and Tuning - page 586

In the preceding output, the enqueue timeouts gives the total number of enqueue operations that could not
complete successfully and was timed out before the request was completed. The counters include both the get and
convert requests.
Enqueue waits show the total number of times the sessions had to wait for an enqueue.
Enqueue deadlocks output shows the number of times a deadlock had occurred. This value should not be
ignored; the system/application/trace files should be checked to understand what's causing the deadlocks. Almost
always, deadlocks indicate inefficient SQL operations and poor business logic.
Enqueue requests and enqueue releases show how many locks are currently requested and released. The
difference between the two values should be the total number of locks currently being held. This value should
normally be close to the number of locks received from V$LOCK view.
Enqueue conversions indicate the number of lock conversions that have occurred. Basically, this indicates the
number of times the lock was requested in a mode and then later converted to another mode due to operation change
requirements, for example, when the session changed its SQL operation from a SELECT to an UPDATE operation.
In an Oracle RAC environment, there are two types of enqueues: the enqueues that are specific to a RAC
environment and those that apply to a single-instance environment. However, both types of enqueues can have a
higher impact in a RAC environment. In this section, we try to analyze few of the enqueues.
Script: MVRACPDnTap_EnqStats.sql
SELECT inst_id INT,
eq_name,
eq_type EQ,
req_reason RR,
total_req# TR,
total_wait# TW,
succ_req# SR,
failed_req# FR,
cum_wait_time CWT
FROM gv$enqueue_statistics
WHERE total_req# > 100
ORDER BY inst_id,
cum_wait_time DESC;
INT EQ_NAME EQ RR TR TW SR FR CWT
---- -------------------------- -- -------------- --------- -------- --------- ------ ---------
2 Transaction TX row lock conte 10450 10417 10450 0 1153800
Multiple Object Reuse RO fast object re 1520 414 1520 0 125560
Process Startup PR contention 588 14 588 0 114140
KSV slave startup PV syncstart 355 3 355 0 84290
PX Process Reservation PS contention 4261 2302 4249 12 11630
DML TM contention 434473 120 434468 0 10080
Controlfile Transaction CF contention 12899 178 12875 24 9950
Transaction TX index contenti 171 168 171 0 8000
Format Block FB contention 484 224 484 0 2490
Reuse Block Range CR block range re 1162 12 1162 0 1890
SGA Log Operation MC Securefile log 2318 5 2318 0 1020
Transaction TX contention 106432 1 106405 35 430
Job Scheduler JS queue lock 62151 5 62151 0 230
Temp Object TO contention 542 57 542 0 180
Segment High Water Mark HW contention 289 23 289 0 90