ORACLE Active dataguard 一个latch: row cache objects BUG

简介: 在Active dataguard遇到latch: row cache objects 查询如下语句 select a.SAMPLE_TIME,a.SQL_ID,a.EVENT,a.
在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找到:



查询:
地址换算为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
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)

记录于此。
相关文章
|
SQL 监控 Oracle
Oracle 数据库发生等待事件:enq: TX - row lock contention ,排查思路
Oracle 数据库发生等待事件:enq: TX - row lock contention ,排查思路
Oracle 数据库发生等待事件:enq: TX - row lock contention ,排查思路
|
27天前
|
运维 Oracle 容灾
Oracle dataguard 容灾技术实战(笔记),教你一种更清晰的Linux运维架构
Oracle dataguard 容灾技术实战(笔记),教你一种更清晰的Linux运维架构
|
30天前
|
Oracle 关系型数据库
oracle 19c 搭建dataguard 简要命令
通过service 完成dg 搭建。
66 0
|
7月前
|
Oracle 关系型数据库 数据库
Flink CDC中oracle dataguard模式下,有没有cdc备库的方案?
Flink CDC中oracle dataguard模式下,有没有cdc备库的方案?
89 1
|
SQL 存储 Oracle
Oracle优化03-Latch和等待
Oracle优化03-Latch和等待
119 0
|
Oracle 网络协议 关系型数据库
oracle 11g dataguard 配置步骤
oracle 11g dataguard 配置步骤
190 0
|
Oracle 关系型数据库
|
Oracle 容灾 安全
实战篇:生产库升级,容灾库 Oracle DataGuard 如何升级?
实战篇:生产库升级,容灾库 Oracle DataGuard 如何升级?
实战篇:生产库升级,容灾库 Oracle DataGuard 如何升级?
|
运维 Oracle 关系型数据库
实战篇:Oracle DataGuard 出现 GAP 修复完整步骤
实战篇:Oracle DataGuard 出现 GAP 修复完整步骤
实战篇:Oracle DataGuard 出现 GAP 修复完整步骤