"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: 12.1 11.2 11.1 10.2 10.1
- 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:
- 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 DISTINCT cache#, type, parameter FROM v$rowcache WHERE cache# = &P1 With the release of 11g, v$rowcache may not report all valid cache# values. If the query above does not return any rows, you can also query: SELECT DISTINCT cache#, cache_name FROM v$rowcache_parent 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.(KQRFPSES is the address of the holding session V$SESSION.SADDR)SELECT * FROM x$kqrfp WHERE kqrfpmod!=0;
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" waitsDocument: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 III 26798411 12.1.0.2.220118, 12.2.0.1.DBRU:191015, 12.2.0.1.DBRU:200114, 18.8, 19.1 the utlrp.sql hangs on row cache lock in ebs environment II 17828499 12.1.0.1.4, 12.1.0.2, 12.2.0.1 Opening PDB hangs waiting for row cache lock on open UPGRADE - 16921340 12.1.0.1.1, 12.1.0.2, 12.2.0.1 Non-CDB to PDB plugin hangs II 13884774 12.1.0.2, 12.2.0.1 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 II 34944713 19.21 RAC Database Hang And Crash With ORA-600[KCLWRT_9] II 32740506 19.19 row cache lock contention with DROP ROLE command and concurrent operations I 34471029 19.18 Row Cache Lock/Mutex Contention While Adding User To DB VAULT Realm Causes Huge And Service Failures III 32920547 19.16 High 'row cache lock' waits started in 19.11 I 31457334 19.15 In-memory population request waiting on row cache lock II 31792465 19.10 Hang on RAC Waiting on Row Cache Lock III 30931981 19.10 Open Reset Logs Hangs With 'row cache lock' and 'cursor: pin s wait for x' Waits - 31304573 19.10 Provision Multitenant Database jobs killed due to 'rdbms ipc message'<='row cache lock'<='enq: FD - Marker generation' III 30489582 12.2.0.1.DBRU:210420, 18.14, 18.18, 19.10, 20.1 Row Cache Lock Blocker/Deadlock Detection Does Not Work in RAC I 29626154 12.2.0.1.DBRU:200114, 18.9, 19.5, 20.1 Poor Performance On Tablespace DDL Operations With Row Cache And Library Cache Wait Events III 27834569 12.2.0.1.DBRU:191015, 12.2.0.1.DBRU:200114, 18.18, 18.8, 19.1 Hang In RAC CDB Showing 'row cache lock' And 'reliable message' Wait Events II 27314697 12.1.0.2.200414, 12.2.0.1.DBRU:200414, 18.10, 18.18, 19.1 RAC: Slight Performance Overhead in case of Atomics and IMC II 25906117 12.1.0.2.220118, 12.2.0.1.DBRU:180717, 18.1 Wait for 'row cache lock' when creating an mview concurrently with another mview creation III 26658759 12.1.0.2.180417, 12.2.0.1, 12.2.0.1.DBRU:171121, 18.1 deadlock while executing utlrp.sql under Edition-Based-redefition enabled II 26581167 18.1 Deadlock On Row Cache Lock By 2 Sessions Connecting From Different RAC Instance III 23084142 12.2.0.1 deadlock while executing utlrp.sql under Edition-Based-redefition enabled III 21153142 12.2.0.1 Row cache lock self deadlock accessing seed PDB III 21097827 12.2.0.1 "ORA-04021: timeout occurred while waiting to lock object" Error in RAC While Creating Tables II 21091431 12.1.0.2.160419, 12.1.0.2.DBBP13, 12.2.0.1 row cache lock during trigger creation using editions III 19907473 12.2.0.1 GEN0 process causing database hang III 15850031 11.2.0.4, 12.1.0.2, 12.2.0.1 Rare instance hang: deadlock between 'row cache lock' and 'cursor: pin S wait for X' II 13869467 12.1.0.2, 12.2.0.1 Many waits for 'row cache lock' in RAC while creating many tables with constraints II 13622515 11.2.0.4, 12.1.0.2, 12.2.0.1 library cache <-> row cache deadlock / hang when altering constraint with MV involved - superseded 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 II 13502860 11.2.0.4, 12.1.0.1 "row cache lock" contention on SYS_PLSQL_xx objects using PIPELINED functions I 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 II 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 II 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 IIII 7715339 11.2.0.1 Logon failures causes "row cache lock" waits - Allow disable of logon delay - 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 II 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 I 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" I 6051177 10.2.0.4.1, 10.2.0.5, 11.1.0.6 Hang / deadlock between coalesce and DBMS_STATS.gather_table_stats III 6004916 10.2.0.5, 11.1.0.6 Hang involving row cache enqueues in RAC (ORA-4021) - 5983020 10.2.0.4, 11.1.0.6 MMON deadlock with user session executing ALTER USER 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 10.2.0.2, 11.1.0.6, 9.2.0.8 Deadlock on dc_rollback_segments from concurrent parallel load and undo segment creation - 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 10.1.0.3, 10.2.0.1, 9.2.0.6 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
Related:
Document:1628089.1 AWR Report Interpretation Checklist for Diagnosing Database Performance Issues
Document:1359094.1 How to Use AWR Reports to Diagnose Database Performance Issues
Document:1274511.1 General SQL_TRACE / 10046 trace Gathering Examples
扫一扫
3280
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
