Resolving Issues Where 'Row Cache Lock' Waits are Occurring

mosdoc發表於2017-01-13

Resolving Issues Where 'Row Cache Lock' Waits are Occurring (文件 ID 1476670.1)

Applies to:

Oracle Database - Standard Edition - Version 10.2.0.1 and later
Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Oracle Database - Personal Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.

Purpose

Disclaimer: This note is written in the context that the Performance Guided Resolution tool was used and recommended this article. It may not make as much sense if read standalone or by other audiences.

 

Troubleshooting Steps

 Brief Definition:

The shared pool contains a cache of rows from the data dictionary that helps reduce physical I/O on the data dictionary tables. The row cache lock is used primarily to serialize changes to the data dictionary and is waited on when a lock on a data dictionary cache is required. Waits on this event usually indicate some form of DDL occuring, or possibly recursive operations such as storage management and incrementing sequence numbers.

Problem Confirmation:

  • Significant wait for latch: row cache objects
  • Slow overall performance with row cache lock
  • High CPU usage

Row Cache Lock

When DDLs execute, they must acquire locks on the row cache in order to access and change the Data Dictionary information. Once the locks are taken then they can be allowed to modify individual rows in the data dictionary. 

Reducing Waits

1. The data dictionary resides in the shared pool. If the shared pool is not sized correctly then the data dictionary might not be fully cached. This should be handled automatically with the automatic shared memory tuning feature. The following documents provide more details:

Document 257643.1 Oracle Database Automated SGA Memory Tuning
Document 270935.1 Shared pool sizing

 2. Find which cache is being waited for:

SQL> select p1text,p1,p2text,p2,p3text,p3 from v$session where event='row cache lock';

P1TEXT P1 P2TEXT P2 P3TEXT P3

cache id 8 mode 0 request 3

SQL> select parameter,count,gets,getmisses,modifications from v$rowcache where cache#=8;

PARAMETER COUNT GETS GETMISSES MODIFICATIONS

DC_SEQUENCES    869    76843 508432   4500

In this example the cache is the "DC_SEQUENCES" cache.

3. Take cache dependent actions:

DC_SEQUENCES
For DC_SEQUENCES, consider caching sequences using the cache option.

DC_OBJECTS
Look for any object compilation activity which might require an exclusive lock, blocking other activities

DC_SEGMENTS
Contention here is most likely to be due to segment allocation.  Investigate what segments are being created at the time.

DC_USERS
This may occur if a session issues a GRANT to a user and that user is in the process of logging on to the database. Investigate why grants are being made while the users are active.

DC_TABLESPACES
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.

4. For further information on row cache issues, review the following:

Document 278316.1 Troubleshooting "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!"

Measuring Success

Once you have applied the changes to resolve the issues you have found, compare the latest AWR to the AWR that led you here via Guided Resolution (that AWR becomes your baseline). Look at the percentage decrease total wait time for this event. If there are still issues, re- evaluate those and address them according to the specific symptom.

 

Known Issues

Document 1417373.1 Row Cache Latch Contention for DC_USERS While Using VPD
Document 2372926.8 "row cache objects" latch contention on DC_USERS row cache

Document 1466896.1 High Waits on 'Row Cache Lock' with Pipelined Functions After Upgrade to 11.2.0.3
Document 13502860.8 Bug 13502860 - "row cache lock" contention on SYS_PLSQL_xx objects using PIPELINED functions

 

 


References


NOTE:34609.1 - WAITEVENT: "row cache lock" Reference Note

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31393455/viewspace-2132398/,如需轉載,請註明出處,否則將追究法律責任。

相關文章