row cache lock Reference Note

This is a reference note for the wait event "row cache lock" which includes the following subsections:

Brief definition
Individual wait details (eg: For waits seen in V$SESSION_WAIT)
Systemwide wait details (eg: For waits seen in V$SYSTEM_EVENT)
Reducing waits / wait times
Troubleshooting
Known Bugs

See Note:61998.1 for an introduction to Wait Events.

Definition:

Versions: 7.3 - 12.1
Documentation:
This event is used to wait for a lock on a data dictionary cache specified by "cache id" (P1).
If running Real Application Clusters (RAC) then LCK0 is signalled to get the row cache lock for the foreground waiting on this event. The LCK0 process will get the lock asynchronously. In exclusive mode the foreground process will try to get the lock.

Individual Waits:

Parameters:

P1 = cache - ID of the dictionary cache
P2 = mode - Mode held
P3 = request - Mode requested

cache - ID of the dictionary cache
Row cache lock we are waiting for. Note that the actual CACHE# values differ between Oracle versions. The cache can be found using this select - "PARAMETER" is the cache name:
```
SELECT cache#, type, parameter 
  FROM v$rowcache     
 WHERE cache# = &P1
;
```
In a RAC environment the row cache locks use global enqueues of type "Q[A-Z]" with the lock id being the hashed object name.

mode - Mode held

The mode the lock is currently held in:

  KQRMNULL 0   null mode - not locked
  KQRMS    3   share mode
  KQRMX    5   exclusive mode KQRMFAIL 10  fail to acquire instance lock

request - Mode requested

The mode the lock is requested in:

  KQRMNULL 0   null mode - not locked
  KQRMS    3   share mode
  KQRMX    5   exclusive mode KQRMFAIL 10  fail to acquire instance lock

Wait Time:

In exclusive mode any process other than PMON will timeout after 8 hours (10000 waits of 3 seconds)
In RAC the foreground will wait 60 seconds for the LCK0 to get the lock, the foreground will wait in infinite loop until the lock has been granted (LCK0 will notify foreground).

In either case PMON will wait for only 5 seconds.

If a session times out when waiting for a row cache lock then it will report this to the alert log and tracefile with a message like:
WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK

Finding Blockers:

Holders and requesters can be seen in view X$KQRFP for parent objects, and X$KQRFS for subordinates.
eg: The following select will show all holders of parent row cache objects so can be used to help find the blocking session.
SELECT * FROM x$kqrfp WHERE kqrfpmod!=0;
(KQRFPSES is the address of the holding session V$SESSION.SADDR)

Systemwide Waits:

It is important to determine which cache is being waited for. The V$ROWCACHE view gives an overview of which caches are being used most, but the waits may not necessarily be on the most used cache. The V$ACTIVE_SESSION_HISTORY view can be used to get an idea of which cache ids (P1) are involved in waits.

If the issue is general across various caches (different cache ids) then the shared pool may need increasing in size to allow more dictionary information to be cached

If the issue is focused on a specific cache id then options typically depend on the cache involved - see the Troubleshooting section below.

Reducing Waits / Wait times:

Options to reduce waits depends on the specific cache that has the contention. See the documents in the Troubleshooting section below for advice on various caches.

Troubleshooting

See the following documents for help troubleshooting issues relating to "row cache lock" waits
Document:1476670.1 Resolving Issues Where 'Row Cache Lock' Waits are Occurring
Document:278316.1 Troubleshooting "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!"

Known Issues / Bugs:

You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button:

NB Prob Bug Fixed Description

- 17828499 12.1.0.1.4, 12.1.0.2, 12.2.0.0 Opening PDB hangs waiting for row cache lock on open UPGRADE

- 16921340 12.1.0.1.1, 12.1.0.2, 12.2.0.0 Non-CDB to PDB plugin hangs

II 13884774 12.1.0.2, 12.2.0.0 Deadlock from concurrent select/ALTER SUMMARY and ALTER TABLE if query_rewrite_enabled set to true/force - superseded

II 16994952 12.1.0.1 Unable to unschedule propagation due to CJQ self-deadlock / hang

II 14117976 11.2.0.3.BP14, 11.2.0.4, 12.1.0.1 Database hangs when executing multiple MV DDL's in parallel

III 13496395 11.2.0.4, 12.1.0.1 Hang / deadlock involving ACCOUNT_STATUS object for concurrent LOGON and ALTER USER

III 7715339 11.2.0.1 Logon failures causes "row cache lock" waits - Allow disable of logon delay

III 21153142 12.2.0.0 Row cache lock self deadlock accessing seed PDB

II 21091431 12.1.0.2.160419, 12.1.0.2.DBBP13, 12.2.0.0 row cache lock during trigger creation using editions

II 19907473 12.2.0.0 GEN0 process causing database hang

III 15850031 11.2.0.4, 12.1.0.2, 12.2.0.0 Rare instance hang: deadlock between 'row cache lock' and 'cursor: pin S wait for X'

II 13622515 11.2.0.4, 12.1.0.2, 12.2.0.0 library cache <-> row cache deadlock / hang when altering constraint with MV involved - superseded

II 13869467 12.1.0.2 Many waits for 'row cache lock' in RAC while creating many tables with constraints

II 13979519 11.2.0.3.BP11, 11.2.0.4, 12.1.0.1 Locks on dc_users kept too long

II 13916228 11.2.0.4, 12.1.0.1 Enabling/disabling constraints makes DML timeout in RAC - superseded

I 13502860 11.2.0.4, 12.1.0.1 "row cache lock" contention on SYS_PLSQL_xx objects using PIPELINED functions

- 13387978 11.2.0.4, 12.1.0.1 Sessions running TRUNCATE causing a deadlock, even if the constraints are disabled

- 12953743 11.2.0.3.BP13, 11.2.0.4, 12.1.0.1 Parallel CTAS of table with a Securefile is slower than parallel IAS / PIDL

I 12889054 11.2.0.2.BP16, 11.2.0.3.BP05, 11.2.0.4, 12.1.0.1 AWR snapshot hangs on dc_objects row_cache_lock held by ctas job

D - 12792862 11.2.0.4, 12.1.0.1 Performance of INSERT using binary xml is very slow due to "row cache lock" in RAC environment - superseded

II 12351027 11.2.0.4, 12.1.0.1 Redefinition causing deadlock between "row cache lock" and "library cache lock"

II 11693365 11.2.0.3, 12.1.0.1 Concurrent Drop table and Select on Reference constraint table hangs (deadlock)

I 10382754 11.2.0.2.BP17, 11.2.0.3, 12.1.0.1 Poor performance/ rowcache contention in 11g with partitioning due to invalidation of objects

IIII 10204505 11.2.0.3, 12.1.0.1 SGA autotune can cause row cache misses, library cache reloads and parsing

II 10126219 11.2.0.1.BP08, 11.2.0.2.BP02, 11.2.0.3, 12.1.0.1 Undetected deadlock 'library cache lock'/'row cache lock' with concurrent DDLs on partition tables.

II 9952554 11.2.0.2.8, 11.2.0.2.BP18, 11.2.0.3, 12.1.0.1 Undetected deadlock 'library cache lock'/'row cache lock' with a session modifying a constraint

II 9866045 11.2.0.3, 12.1.0.1 Long wait on 'wait for master scn' in LCK causing long row cache lock waits

III 9776608 11.2.0.2, 12.1.0.1 Hang from concurrent login to same account with a wrong password

II 9278979 11.2.0.2, 12.1.0.1 Instance hang / ORA-4021 with OPTIMIZER_USE_PENDING_STATISTICS = true

I 8268775 11.1.0.7.4, 11.2.0.1.2, 11.2.0.1.BP07, 11.2.0.2, 12.1.0.1 High US enqueue contention during a login storm or session failover

- 8364676 11.1.0.7.2, 11.2.0.1 row cache lock waits from background space preallocation

III 7529174 10.2.0.5, 11.1.0.7.3, 11.2.0.1 Deadlock / hang between SMON and foreground process

- 7416901 11.1.0.7.1, 11.2.0.1 Deadlock between QC and PQ slaves when CELL_PARTITION_LARGE_EXTENTS = ALWAYS

II 7313166 10.2.0.5, 11.2.0.1 Startup hang with self deadlock on dc_rollback_segments

II 6870994 10.2.0.5, 11.1.0.7.3, 11.2.0.1 High US enqueue / rowcache lock while trying to online a NEW undo segment

III 6027068 10.2.0.5, 11.2.0.1 Contention on ORA_TQ_BASE$ sequence

- 5756769 10.2.0.4.1, 10.2.0.5, 11.1.0.7, 11.2.0.1 Deadlock between Create MVIEW and DML

III 6143420 10.2.0.5, 11.1.0.6 Deadlock involving "ROW CACHE LOCK" on dc_users AND "CURSOR: PIN S WAIT ON X"

III 6004916 10.2.0.5, 11.1.0.6 Hang involving row cache enqueues in RAC (ORA-4021)

II 5883112 10.2.0.4, 11.1.0.6 False deadlock in RAC

II 5138741 10.2.0.4, 11.1.0.6 High waits on 'row cache lock' when using materialized views on RAC

II 4604972 11.1.0.6 Deadlock on dc_users by Concurrent Grant/Revoke

- 4579381 10.1.0.5, 10.2.0.2, 11.1.0.6 Deadlock on DC_USERS in RAC (ORA-4020)

- 4446011 9.2.0.8, 10.1.0.5, 10.2.0.2, 11.1.0.6 Hang with row cache lock deadlock from concurrent ALTER USER / TRUNCATE

II 4390868 10.1.0.5, 10.2.0.3, 11.1.0.6 Contention on DC_SEGMENTS due to small cache size on SYS.AUDSES$

- 4313246 9.2.0.8, 10.1.0.5, 10.2.0.2, 11.1.0.6 PLSQL execution can hold dc_users row cache lock leading to hang / deadlocks

I 4153150 9.2.0.8, 10.2.0.2, 11.1.0.6 Deadlock on dc_rollback_segments from concurrent parallel load and undo segment creation

I 6051177 10.2.0.4.1, 10.2.0.5 Hang / deadlock between coalesce and DBMS_STATS.gather_table_stats

- 5983020 10.2.0.4 MMON deadlock with user session executing ALTER USER

- 4275733 9.2.0.8, 10.1.0.5, 10.2.0.1 Deadlock between library cache lock and row cache lock from concurrent rename partition

I 5641198 10.2.0.1 Some waits may be longer than needed ("row cache lock") in RAC

- 4137000 10.1.0.5, 10.2.0.1 Concurrent SPLIT PARTITION can deadlock / hang

- 3627263 9.2.0.6, 10.1.0.4, 10.2.0.1 Deadlock / hang during RAC instance startup

II 3424721 9.2.0.6, 10.1.0.3, 10.2.0.1 Hang/deadlock from ALTER INDEX REBUILD on partition with concurrent SQL

- 2615271 9.2.0.6, 10.1.0.2 Deadlock from concurrent GRANT and logon

'*' indicates that an alert exists for that issue.

'+' indicates a particularly notable issue / bug.

See Note:1944526.1 for details of other symbols used

WAITEVENT: "row cache lock" Reference Note (文件 ID 34609.1)