Oracle Row Cache Lock Wait Event in RAC environment


In order for any DDL (Data Definition Language) to execute in oracle database, 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.

The row cache enqueue lock is caused by DDL locking portions of the data dictionary as you create a table/index of other DDL command.  The row cache lock escalates into the row cache enqueue lock when multiple DDL statements are waiting to execute.  The row cache enqueue locks are allocated within the shared_pool_size region of the SGA.

If the lock wait exceed a predefined threshold, the DDL will abort and cause a trace file to be generated.  This trace file will contain the string “WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK”.

Note:  Long-running DDL (alter table xxx move” is far more likely to generate a row cache enqueue lock than a fast-running DDL statement (e.g. truncate table).

The solution to excessive row cache enqueue lock waits is:

1 – Serialize the DDL running on a small number of concurrent DDL tasks.

2 – If the row cache enqueue locks are for dc_sequences, consider using DBMS_SHARED_POOL.KEEP to pin your sequences.

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.

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 ROW CACHE LOCK EVENT ARE GLOBAL IN RAC ENVIRONMENT—causing the row cache lock wait to be more pronounced.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s