WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!

This article will explain some reasons for which you may encounter "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" message in the alert log file. When Row cache contention occurs, if the enqueue cannot be obtained within a certain time period, a trace file will be generated in the trace location with some trace details.

The trace file tends to contain the following words:
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<

/opt/oracle/admin/EAIAPP/udump/eaiapp1_ora_23288.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

ORACLE_HOME = /opt/oracle/product/dbs

System name: Linux

Node name: db1-eai.prod.stl.cw.intraisp.com

Release: 2.6.18-128.4.1.el5

Version: #1 SMP Thu Sep 23 19:59:19 EDT 2010

Machine: x86_64

Instance name: EAIAPP1

Redo thread mounted by this instance: 1

Oracle process number: 43

Unix process pid: 23288, image: oracle@db1-eai.prod.stl.cw.intraisp.com



*** 2010-09-25 17:08:05.532

*** ACTION NAME:() 2010-09-25 17:08:05.532

*** MODULE NAME:(OMS) 2010-09-25 17:08:05.532

*** SERVICE NAME:(EAIAPP) 2010-09-25 17:08:05.532

*** SESSION ID:(120.29614) 2010-09-25 17:08:05.532

>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<<
row cache enqueue: session: 70000001b542d78, mode: N, request: S
row cache parent object: address=700000036f27328 cid=0(dc_tablespaces)
hash=a6840ab5 typ=9 transaction=0 flags=00008000


The trace will often contain a systemstate dump, although most useful information is in the header section. Typically a session holding the row cache resource will either be on cpu or blocked by another session. If it is on cpu then errorstacks are likely to be required to diagnose, unless tuning can be done to reduce the enqueue hold time. Remember that on a RAC environment, the holder may be on another node and so multiple systemstates from each node will be required.

For each enqueue type, there are a limited number of operations that require each enqueue.

1) DC_TABLESPACES : This is probably the most likely is allocation of new extents. If extent sizes are set low then the application may constantly be requesting new extents and causing contention. Do you have objects with small extent sizes that are rapidly growing? (You may be able to spot these by looking for objects with large numbers of extents). Check the trace for insert/update activity, check the objects inserted into for number of extents.

2) DC_SEQUENCES : Check for appropriate caching of sequences for the application requirements.

3) DC_USERS : Deadlock and resulting "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" can occur if a session issues a GRANT to a user, and that user is in the process of logging on to the database.

4) DC_OBJECTS : Look for any object compilation activity which might require an exclusive lock and thus block online activity.

5) DC_SEGMENTS : This is likely to be down to segment allocation. Identify what the session holding the enqueue is doing and use errorstacks to diagnose.

6) In many cases no operations are notified in the trace file. Only the session ID is specified. In that case we need to investigate that session id.