In order for DDL (Data Definition Language) 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, we see the row cache lock wait event.
A closer look at the row cache lock wait event
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 row cache enqueue lock types
The tuning of the row cache lock wait is dependent 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.
Tuning for the row cache lock wait event
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:
>> 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.