Potential reasons for "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem: Potential reasons for "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!





Solution:



Potential reasons for "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! " ---

When Row cache contention occurs, if the enqueue cannot be obtained within a certain predetermined time period, a trace file will be generated in the user_dump_dest or background_dump_dest depending on whether a user or background process created the trace file. The alert log may be updated accordingly with the warning and the location of the trace file. This may also be accompanied by database hang or slowdown symptoms.

What is happening here is that the database is detecting that a key resource is being held for too long and is flagging this up to the administrator so that this situation can be resolved.

The trace file generated will tend to contain the words:
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<< in it at the start. This trace can provide some useful information for diagnosing the cause of the contention. It is possible to check the row cache enqueue trace to determine which enqueue has the contention: For example: ... >>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<< row cache enqueue: session: 70000001b542d48, mode: N, request: S row cache parent object: address=700000036f27628 cid=0(dc_tablespaces) hash=a6840aa5 typ=9 transaction=0 flags=00008000 ... In this particular example, the resource being waited for is the 'dc_tablespaces' enqueue and so looking at activities that may have affected tablespaces would be prudent. Waits for other enqueues would require looking at the specific area involved. The trace will often contain a systemstate dump. 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. For each enqueue type, there are a limited number of operations that require each enqueue. DC_TABLESPACES Probably the most likely cause is the 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. Note 853652.1 RAC and Sequences Note 395314.1 RAC Hangs due to small cache size on SYS.AUDSES$ - fixed in 10.2.0.3 Note 6027068.8 Bug 6027068 - Contention on ORA_TQ_BASE sequence -fixed in 10.2.0.5 and 11.2.0.1 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. Note 4604972.8 Bug 4604972 - Deadlock on dc_users by Concurrent Grant/Revoke - fixed in 11.1.0.6 Note 6143420.8 Bug 6143420 - Deadlock involving "ROW CACHE LOCK" on dc_users AND "CURSOR: PIN S WAIT ON X"- fixed in 10.2.0.5 and 11.1.0.6 DC_OBJECTS DC_OBJECT_IDS Note 11693365.8 Bug 11693365 - Concurrent Drop table and Select on Reference constraint table hangs (deadlock) - fixed in 12.1 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. DB_ROLLBACK_SEGMENTS This is due to rollback segment allocation. Just like dc_segments,identify what is holding the enqueue and also generate errorstacks. 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. DC_TABLE_SCNS Note 5756769.8 Bug 5756769 - Deadlock between Create MVIEW and DML - fixed in 10.2.0.5 ,11.1.07 and 11.2.0.1 RAC Specific Bugs Note 6004916.8 Bug 6004916 - Hang involving row cache enqueues in RAC (ORA-4021) - fixed in 102.0.5 and 11.1.0.6 Note 8666117.8 Bug 8666117 - High row cache latch contention in RAC - fixed in 11.2.0.2 and 12.1 Note 9866045.8 Bug 9866045 - Long wait on 'wait for master scn' in LCK causing long row cache lock waits - fixed in 12.1


References:



Potential reasons for "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! " [ID 278316.1]

Get Oracle Certifications for all Exams
Free Online Exams.com