Row Cache Lock Wait

 

Definition

In order for DDL to execute, it must acquire a row cache lock to lock the Data Dictionary information.   The shared pool contains a cache of rows from the data dictionary that helps reduce physical I/O to the data dictionary tables and allows locking of individual data dictionary rows. The locks on the data dictionary rows are called row cache enqueue locks. The enqueue lock structures are allocated from the shared pool as needed but when these requests wait and time out is when we see the row cache lock wait.

 

Analysis

Each row cache lock will be on a specific data dictionary object.  This is called the enqueue type and can be found in the v$rowcache view. In this sample select from v$rowcache you can find the enqueue types and the type of activity being performed within the dictionary cache. 

 

PARAMETER             COUNT         GETS       GETMISSES           MODIFICATIONS
--------------------- ----- ------------ ---------- ------------- 
dc_free_extents              0            0          0             0
dc_used_extents            0            0           0             0
dc_segments               5927     131379921    4142831        693734  
dc_tablespaces           22     188609668       2436             0
dc_tablespace_quotas     12       22779303       3843             0
dc_files                            0         165961      22493                21
dc_users                       19      145681559       2078            21
dc_rollback_segments  67        3906307         66           232
dc_objects                      1927       70725250    2247804             74803
dc_sequences                   4         142714       1599            142714     

 

Common Enqueue Types

The tuning of the row cache lock wait is dependant upon the activity for each of the enqueue types. Of these, the most common are:

 

• DC_SEQUENCES, this row cache lock wait may occur during the use of sequences. Tune by checking sequences to see if they have the cache option specified and if that cache value is reflective of the anticipated simultaneous inserts by the application.
• DC_USED_EXTENTS and DC_FREE_EXTENTS, this row cache lock wait may occur during space management operations where tablespaces are fragmented or have inadequate extent sizes. Tune by checking whether tablespaces are fragmented, extent sizes are too small, or tablespaces are managed manually.
• DC_TABLESPACES, this row cache lock wait may occur during the allocation of new extents. If extent sizes are set too low the application may frequently request new extents which could cause contention.  Tune by checking for rapidly increasing number of extents.
• DC_OBJECTS, this row cache lock wait may occur during the recompilation of objects.  If object compiles are occurring this can require an exclusive lock which will block other activity.  Tune by examining invalid objects and dependencies.

Conclusion:

The row cache lock wait event is associated with a specific enqueue type on a data dictionary row. Checking activity within the v$rowcache view is a good place to start for understanding this relationship as tuning can only be accomplished with analysis of the enqueue type. If a trace file is available you may also see the following error of “>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<”. Also realize that the row cache lock wait event may appear more frequently when using RAC.  This is because the library cache and the row cache are global in RAC—causing the row cache lock wait to be more pronounced.