ROW CACHE LOCK

WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK:
When Row cache contention occurs, if the enqueue cannot be gotten within a certain time period, a trace file will be generated in the location with some trace details.
The trace file tends to contain the words:
>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! << address="700000036f27628" cid="0(dc_tablespaces)
hash=a6840aa5 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 multi node system (RAC) 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.

DC_TABLESPACES:
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.

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

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.

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

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.

The details are also available at:
http://www.metalink.oracle.com/
Doc ID:
278316.1

Solution to ROW/LIBRARY Cache locks:
Set the below event in database level and restart the database. This will resolve the issue.
event='32333 trace name context forever, level 10'