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

FROM gv$lock;
INST_ID SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
1 10 RS 25 1 2 0 183030 2
1 239 XR 0 0 1 0 183148 2
1 10 XR 4 0 1 0 183148 2
1 10 RD 1 0 1 0 183146 2
1 9 DM 1 0 4 0 183021 2
1 9 MR 201 0 4 0 183023 2
1 10 CF 0 0 2 0 183038 2
1 247 RT 2 1 6 0 183023 2
1 9 RT 2 2 6 0 183023 2
1 249 TS 3 1 3 0 183004 2
1 241 KT 16749 0 4 0 48798 2
1 14 AE 133 0 4 0 182993 2
1 282 PS 1 32766 4 0 1 2
2 10 RS 25 1 2 0 183791 2
2 239 XR 0 0 1 0 183959 2
2 10 XR 4 0 1 0 183959 2
2 10 RD 1 0 1 0 183956 2
2 9 DM 1 0 4 0 183463 2
2 10 CF 0 0 2 0 183824 2
2 9 MR 201 8 4 0 183805 2
2 247 RT 1 0 6 0 183799 2
2 9 RT 1 2 6 0 183473 2
2 269 TM 93128 0 3 0 0 2
2 37 TM 93128 0 3 0 0 2
2 289 TM 93128 0 0 3 0 0
2 280 TM 93120 0 3 0 0 2
CTIME , displayed in seconds, is the time since the current lock mode was acquired/converted. The value of this
column keeps increasing until such time that the lock is not converted.
The following query will give an overview of the locking and enqueue activity since the instance startup:
SELECT statistic#,
name,
class,
value
FROM v$sysstat
WHERE class = 4;
STATISTIC# NAME CLASS VALUE
---------- ---------------------------------- ---------- ----------
38 enqueue timeouts 4 1057
39 enqueue waits 4 254192
40 enqueue deadlocks 4 0
41 enqueue requests 4 29103432
42 enqueue conversions 4 124263
43 enqueue releases 4 29102329
72 max cf enq hold time 4 65700
73 total cf enq hold time 4 2615250
74 total number of cf enq holders 4 8104