latch: row cache objects等待事件

This latch comes into play when user processes are attempting to access or update the cached data dictionary values.

Solutions

Problem:

To determine if the row cache is being used efficiently, execute the following SQL. If the ratio is not close to 1 then some tuning required.

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;

Options for tuning row cache are so limited that some resources call it non-tunable. However if the value of the column pct_succ_gets in the above query is not close to “1” or if Ignite for Oracle shows significant waits for the “Row Cache Objects” latch, then consider increasing SHARED_POOL_SIZE.

Another way to adjust the dictionary cache is to increase the library cache. Since the dictionary cache is a part of the library cache, the increase will indirectly increase the dictionary cache. Also, consider the following:

Using Locally Managed tablespaces for application objects, especially indexes, may decrease Row Cache locks.

Review and amend database logical design. For example, try to decrease the number of indexes on tables with frequent inserts.

Posted

August 9, 2011

mac

Tags:

Comments

One response to “latch: row cache objects等待事件”

Ask_Maclean_liu_Oracle

December 9, 2014

Troubleshooting StepsBrief Definition:The row cache lock is used primarily to serialize changes to the data dictionary and to wait for a lock on a data dictionary cache. Waits onthis event usually indicate some form of DDL occuring, or possibly recursive operations such as storage management and sequence numbersincrementing frequently.Problem Confirmation:Significant wait for latch: row cache objectsSlow overall performance with row cache lockHigh CPU usageRow Cache LockWhen DDLs execute, it must acquire a row cache latch to lock the Data Dictionary information. The shared pool contains a cache of rows from the datadictionary that helps reduce physical I/O to the data dictionary tables. This allows locking of individual data dictionary rows.Reducing Waits1. The data dictionary resides in the shared pool. So make sure the shared pool has enough memory so that more data dictionary can be cached:Note:1012046.6 How to Caculate Your Shared Pool Size2. 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 P3cache id 8 mode 0 request 3SQL> select parameter,count,gets,getmisses,modifications from v$rowcache where cache#=8;PARAMETER COUNT GETS GETMISSES MODIFICATIONSDC_SEQUENCES 869 76843 508432 4500For DC_SEQUENCES, consider caching sequences using the cache option.3. Other dictionary locks:DC_OBJECTSLook for any object compilation activity which might require an exclusive lock, blocking other activitiesDC_SEGMENTSThis is most likely due to segment allocation.DC_USERSThis may occur if a session issues a GRANT to a user, and that user is in the process of logging on to the database.DC_TABLESPACESProbably the most likely cause is the allocation of new extents. If extent sizes are set low then the application may constantly be requesting new extentsand 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 withlarge numbers of extents). Check the trace for insert/update activity, check the objects inserted into for number of extents.4. For further information, review following information:Note:278316.1 Troubleshooting “WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!”Measuring SuccessOnce 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 andaddress them according to the specific symptom.Known IssuesNote:1417373.1 Row Cache Latch Contention for DC_USERS While Using VPDNote:2372926.8 “row cache objects” latch contention on DC_USERS row cache

Reply

latch: row cache objects等待事件

Comments

One response to “latch: row cache objects等待事件”

Leave a Reply Cancel reply