http://surachartopun.com/2009/11/investigate-row-cache-lock.html
Investigate ROW CACHE LOCK
My Oracle RAC had the problem, so I investigated... I found:
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------- ------------ ----------- ------ ------ ----------
row cache lock 509,761 1,259,315 2470 71.8 Concurrenc
ROW CACHE LOCK (DDL statements require "row cache lock" and session will wait "row cache lock" to lock the data dictionary)
And then check V$SESSION
SQL> select p1text,p1,p2text,p2,p3text,p3 from v$session where event = 'row cache lock';
P1TEXT P1 P2TEXT P2 P3TEXT P3
-------------- --------- -------------- --------- -------------- ----------
cache id 13 mode 0 request 5
cache id 13 mode 0 request 5
cache id 13 mode 0 request 5
cache id 13 mode 0 request 5
cache id 13 mode 0 request 5
cache id 13 mode 0 request 5
cache id 13 mode 0 request 5
What I see? I need "cache id" to find on V$ROWCACHE (to check enqueue type)
Example: Enqueue Type
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.
To check on V$ROWCACHE
After check at v$rowcache, my point to SEQUENCE (DC_SEQUENCES). then I check ALL_SEQUENCES and then investigate how to solve...SQL> select PARAMETER ,COUNT ,GETS ,GETMISSES ,MODIFICATIONS from v$rowcache where cache#=13;
PARAMETER COUNT GETS GETMISSES MODIFICATIONS
--------------------------- ---------- ---------- ---------- -------------
dc_sequences 13 746449 210320 746449
SQL> column pct_succ_gets format 999.9
SQL> column updates format 999,999,999
SQL> SELECT parameter, sum(gets), sum(getmisses), 100*sum(gets - getmisses) / sum(gets) pct_succ_gets, sum(modifications) updates FROM V$ROWCACHE WHERE gets > 0 GROUP BY parameter;
PARAMETER SUM(GETS) SUM(GETMISSES) PCT_SUCC_GETS UPDATES
---------------- ---------- -------------- ------------- ------------
dc_constraints 4288 1455 66.1 4,288
dc_object_ids 8161040 118929 98.5 5,482
outstanding_alerts 2737095 2722712 .5 200
dc_awr_control 31108 526 98.3 457
dc_objects 21160173 145159 99.3 266,627
dc_usernames 9387743 1349 100.0 0
dc_table_scns 4658 4658 .0 0
dc_users 94113064 2174 100.0 16
dc_histogram_defs 7702201 783888 89.8 97,472
kqlsubheap_object 153 36 76.5 0
dc_profiles 1266752 13 100.0 0
dc_object_grants 44530796 7547 100.0 0
dc_histogram_data 2431665 377265 84.5 147,793
dc_segments 1658801 347470 79.1 17,402
dc_files 67249 6477 90.4 0
dc_sequences 748386 210954 71.8 748,386
dc_database_links 2552640 89 100.0 0
dc_global_oids 850330 3091 99.6 0
global database name 175908 174 99.9 0
dc_tablespaces 42863352 137296 99.7 0
dc_tablespace_quotas 14299 5277 63.1 576
dc_rollback_segments 149604805 2673 100.0 949
SQL> select * from all_sequences order by last_number;
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------ ---------- ---------- -------- ------------ - - ------- ---------
APPUSER SEQ_XYZ 1 1.0000E+27 1 N N 0 34015364
This case, I solved it by "alter sequence ... cache ..."