Resolving Issues Where 'Row Cache Lock' Waits are Occurring
Resolving Issues Where 'Row Cache Lock' Waits are Occurring (文件 ID 1476670.1)
Applies to:
Oracle Database - Standard Edition - Version 10.2.0.1 and laterOracle 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
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:
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:
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 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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 等待事件之Row Cache Lock事件
- LIBRARY CACHE LOCK WAITS AND NO BLOCKER FOUNDAIBloC
- hanganalyze解決row cache lock(ZT)
- WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!AIENQ
- [ORACLE 11G]ROW CACHE LOCK 等待Oracle
- 轉)用hanganalyze解決row cache lock
- (轉)用hanganalyze解決row cache lock
- 故障排除:"WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! "AIENQ
- WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!的分析AIENQ
- 轉貼_用hanganalyze解決row cache lock
- 用hanganalyze解決row cache lock(轉貼)
- WAITEVENT: "row cache lock" Reference Note (文件 ID 34609.1)AI
- 一次WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCKAIENQ
- 由row cache lock等待事件引起的效能問題事件
- Metlink:Troubleshooting:WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!AIENQ
- 一次Row Cache Lock問題處理過程
- Rac 環境中分割槽表建立index hang(row cache lock)Index
- WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! 與 dc_tablespcesAIENQ
- WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK-[ID 278316.1]AIENQ
- How to Match a Row Cache Object Child Latch to its Row CacheObject
- hang了,嚴重的row cache lock 等待事件--就因大sql文字事件SQL
- Metlink:How to Match a Row Cache Object Child Latch to its Row CacheObject
- 翻譯metalink關於WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK文章AIENQ
- 記一次row cache lock引起的效能問題分析處理
- 當刪除oracle資料庫user時發生row cache lock 等待事件Oracle資料庫事件
- oracle??邏輯DG同步卡住,session等待row cache lock的處理過程OracleSession
- 11G 修改使用者密碼導致的row cache lock密碼
- row cache objects latch研究Object
- enq: TX - row lock contentionENQ
- 【MySQL】select for update 的Row Lock 與Table LockMySql
- RAC生產庫出現嚴重row cache lock和log file switch(archiving need)
- Latch: Row Cache Objects (One bug?)Object
- 11.1.0.7 row cache lock 修改使用者名稱密碼 bug 7715339密碼
- 等待事件enq: TX - row lock contention事件ENQ
- 【等待事件】-enq: TX - row lock contention事件ENQ
- row lock contention 阻塞程式查詢
- MySQL:Innodb 讓MDL LOCK和ROW LOCK 記錄到errlogMySql
- Library Cache Pin 及 Library Cache Lock分析