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/，如需轉載，請註明出處，否則將追究法律責任。