ORACLE Active dataguard 一个latch: row cache objects BUG

在Active dataguard遇到latch: row cache objects 查询如下语句
select a.SAMPLE_TIME,a.SQL_ID,a.EVENT,a.P1TEXT,a.P1,a.P2TEXT,a.P2,a.P3TEXT,a.P3,
 b.f2
  from v$active_session_history a,  
  (select max(b.SQL_TEXT) f2,sql_id from  v$sql b group by sql_id ) b
 where a.sql_id = b.sql_id
   and a.SAMPLE_TIME < sysdate - 1
   and a.SAMPLE_TIME >
       to_date('2017-07-04 11:04:30', 'yyyy-mm-dd hh24:mi:ss')
   and a.sql_id is not null
   and a.event is not null
 order by a.sample_time

如下很多latch: row cache objects：




通过metalink找到：

• P1 = Latch address
• P2 = Latch number
• P3 = Tries

查询：
地址换算为0X16进制就是下面的
SQL>  SELECT name, 'Child '||child#, gets, misses, sleeps
  2      FROM v$latch_children
  3     WHERE addr='000000073692E9B0'
  4    ;
NAME                                                             'CHILD'||CHILD#                                      GETS     MISSES     SLEEPS
---------------------------------------------------------------- ---------------------------------------------- ---------- ---------- ----------
row cache objects                                                Child 1                                        2944545138  584527074   70118020
SQL>     SELECT "WHERE", SLEEP_COUNT, WTR_SLP_COUNT, LONGHOLD_COUNT
  2      FROM v$latch_misses
  3     WHERE parent_name='row cache objects'
  4     and SLEEP_COUNT <>0
  5     ORDER BY SLEEP_COUNT;
WHERE                                                                            SLEEP_COUNT WTR_SLP_COUNT LONGHOLD_COUNT
-------------------------------------------------------------------------------- ----------- ------------- --------------
kqreqd                                                                              18475287      12578418              0
kqreqd: reget                                                                       23620027      15993065              0
kqrpre: find obj                                                                    27962864      41519129              0
SQL>    SELECT
  2          kqrsttxt PARAMETER,
  3   -- kqrstcid CACHE#,
  4          kqrstcln "Lchild#",
  5          kqrstgrq "DCGets",
  6          l.gets   "LGets",
  7          l.misses "Misses"
  8    FROM X$KQRST, V$LATCH_CHILDREN l
  9   WHERE l.addr='000000073692E9B0'
 10     and l.child#=KQRSTCLN
 11   ORDER BY 1,2
SQL>  SELECT
  2          kqrsttxt PARAMETER,
  3          kqrstcln "Lchild#",
  4          kqrstgrq "DCGets"
  5     FROM X$KQRST
  6    WHERE KQRSTCLN = 1
  7    ;
PARAMETER                           Lchild#     DCGets
-------------------------------- ---------- ----------
dc_rollback_segments                      1 2413168646

而这是一个bug

Bug 17608518  "row cache objects" latch contention on dc_rollback_segments

 This note gives a brief overview of bug 17608518. 
 The content was last updated on: 29-NOV-2016
  Click here for details of each of the sections below.

Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	Versions BELOW 12.1
Versions confirmed as being affected	11.2.0.4 11.2.0.3 11.2.0.2
Platforms affected	Generic (all / most platforms affected)

Description

High Row Cache contention on dc_rollback_segments in Data Guard Active Standby (ADG)
manifested with wait events "latch: row cache objects" or "Child row cache objects".
 
This fix provides a latchless enhancement for alleviating "row cache objects" latch contention
on dc_rollback_segments. It is primarily intended for read-only environments such as Active 
Data Guard since row cache lookups for other purposes such as writes will cause the 
latchless lookups to fail and fall back to the latching mechanism, increasing overhead. 
 
Therefore, this enhancement needs to be used with care in a read-write environments.
 
After applying patch the fix must be enabled by setting event 17608518 at level 3.
 
If using a pfile add following line:
 
  event='17608518 trace name context forever, level 3'
 
If using spfile, add event like:
 
 SQL> alter system set event = '17608518 trace name context forever, level 3' scope=spfile sid='*';
 
Restart the standby for event to be in effect.
 

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.

参考文档：
WAITEVENT: "latch: row cache objects" Reference Note (文档 ID 1550722.1)

记录于此。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-2141616/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7728585/viewspace-2141616/