SQL> col name format a30
SQL> select * from (select SAMPLE_TIME,
SESSION_ID,
NAME,
P1,
P2,
P3,
ash.BLOCKING_SESSION
from v$active_session_history ash, v$event_name enm
where ash.event# = enm.event#
and enm.NAME='row cache lock')
where rownum<10; 2 3 4 5 6 7 8 9 10 11
SAMPLE_TIME SESSION_ID NAME P1 P2 P3
BLOCKING_SESSION
--------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- ---------- -----
-----------
15-APR-14 07.48.08.840 PM 22 row cache lock 13 0 5
15-APR-14 07.45.49.883 PM 1159 row cache lock 13 0 5
15-APR-14 07.44.12.569 PM 25 row cache lock 13 0 5
ROW CACHE LOCK等待事件是一个共享池相关的等待事件。是由于对于字典缓冲的访问造成的。
P1 – Cache Id
P2 – Mode Held
P3 – Mode Requested
SQL查询
--查询row cache lock等待
select * from v$session_wait where wait_class = 'row cache lock';
--查询rowcache 名称
select * from v$rowcache where cache# = &p1;
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_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_AWR_CONTROL
This enqueue is related to control of the Automatic Workload Repository. As such any operation manipulating the repository may hold this so look for
processes blocking these.
SQL> select a.cache#,a.PARAMETER from v$rowcache a where cache# =13;
CACHE# PARAMETER
---------- --------------------------------
13 dc_sequences
