SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql
SQL>@$ORACLE_HOME/rdbms/admin/addmrpt.sql
SQL>@$ORACLE_HOME/rdbms/admin/ashrpt.sql

@ The cache_id for the holder can be determined using the following query:
@
@ set pages 999 lines 132
@
@ column name format a30
@ column held_mode format 990
@ column requent_mode format 990
@
@ select KQRFPCNM Name, KQRFPSES SID, KQRFPMOD Mode, KQRFPREQ Request
@ from X$KQRFP
@ where KQRFPCID=&Cache_id ;
@
@ On each node then, this will show you the waiters and holders for the
@ cache_id. Check the cache_id's using:
@
@ select distinct KQRFPCNM ,KQRFPCID from X$KQRFP ;

How to interpret the information obtained

Systemstate dump

Often the Row cache enqueue is a result of a chain of events and the lock being held is a symptom of another issue where a process holding the requested row cache enqueue is being blocked by other processes.
Systemstate dump can help to find which row cache is being requested and may help find the blocking process.

Example 1:
The header of the trace shows the Oracle process number (pid) of the process waiting for the row cache enqueue lock as well as the mode needed for the row cache enqueue.
In the example above Process 77 is waiting for a row cache needed in shared mode (request: S) 

The systemstate contains process state information for every process in the database. Above we see that PROCESS 77 that dumped the systemstate is requesting row cache dc_users in shared mode.
The process state will also contain a stack useful to Oracle Support to determine what part of Oracle code is being performed, wait information and sql currently being run.


Using the address for the row cache object (in this case 0x1dc9a5d30). we see that there is request by PROCESS 218 in exclusive mode for the particular row cache. A request in Exclusive mode will result in any requests in shared mode to wait behind it until it is granted the request in exclusive mode and has released it.

Process 164 is holding the row cache enqueue in Shared mode (mode=S) and thus preventing process 218 from obtaining the row cache enqueue. Furthermore we see that the process 163 is on CPU (the systemstate shows last wait for 'SQL*Net message from client' and not waiting for 'SQL*Net message from client'). To further diagnose support would need to check the stack calls included in the dump.
For each enqueue type, there are a limited number of operations that require each enqueue. 

Example 2:
In this example we see that PROCESS 18 (MMON) is waiting for row cache type dc_awr_control in SHARED mode. 
The row cache lock is being held by PROCESS 269 in exclusive mode. The process is waiting for 'SGA: allocation forcing component growth' . Thus the root cause in this case is the resizing of SGA and the wait on row cache is secondary result of this

AWR Report

Top 5 Timed Events                                         Avg %Total               
~~~~~~~~~~~~~~~~~~                                        wait   Call               
Event                                 Waits    Time (s)   (ms)   Time Wait Class    
------------------------------ ------------ ----------- ------ ------ ----------    
SGA: allocation forcing compon   42,067,317      38,469      1    7.6      Other    
CPU time                                          2,796           0.6               
db file sequential read             132,906         929      7    0.2   User I/O    
latch free                        4,282,858         704      0    0.1      Other    
log file switch (checkpoint in          904         560    620    0.1 Configurat    
          -------------------------------------------------------------       
      

As can be seen clearly in the AWR report Top 5 Timed Events, the major issue is the 'SGA: allocation forcing component growth". This is the root cause where one of the processes as seen in the systemstate dump is holding the row cache while waiting on the above. The top 5 events do not even show waits on 'row cache' as this is a cause and not the actual root issue.

Potential reasons for "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!"

DC_TABLESPACES

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

DC_SEQUENCES

Check for appropriate caching of sequences for the application requirements. 



DC_USERS

Deadlock and resulting "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" can occur if a session issues a GRANT to a user, and that user is in the process of logging on to the database.




DC_OBJECT_IDS




DC_SEGMENTS

This is likely to be down to segment allocation. Identify what the session holding the enqueue is doing and use errorstacks to diagnose.

DB_ROLLBACK_SEGMENTS

This is due to rollback segment allocation. Just like dc_segments,identify what is holding the enqueue and also generate errorstacks. Remember that on a multi-node system (RAC) the holder may be on another node and so multiple systemstates from each node will be required.

DC_TABLE_SCNS




RAC Specific Bugs

References

NOTE:468334.1 - How to Match a Row Cache Object Child Latch to its Row Cache

相关内容 产品 Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Standard Edition Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition 关键字 LOCK; ROW CACHE LOCK; WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK 错误 ORA-4021

返回页首