Low performance or ORA-1220 for query on V$ARCHIVE_GAP on 11.2.0.4 or later release-CSDN博客

本文链接：https://blog.csdn.net/Smasegain/article/details/46653161

一.背景说明:

2014-04-19为某客户实施完成ADDRPROD数据库的迁移及升级后，配置本地standby。客户环境基础信息如下:

OS:AIX7.1

DB:Oracle 11.2.0.4.1

DG:11.2.0.4.1 RAC-->AIX 7.1+11.2.0.4.1Single Instance

数据保护模式:最大性能模式，arch async

2014-08-15客户要求为其搭建2Mbps到异地的AIX 5.3+11.2.0.4.1 Single instance的灾备时客户反应查询v$archive_gap时一直处于挂起执行状态无返回结果(实际可以返回查询结果:3分钟左右)。

二.问题分析及解决

原因分析:通过session定位到系统进程后发现在查询v$archive_gap的时候系统进程消耗资源很高。通过访问MOS后发现是因为Bug 18411339 - Low performance or ORA-1220 for query on V$ARCHIVE_GAP on 11.2.0.4 or later release (文档 ID 18411339.8)

永久解决办法:数据库升级到11.2.0.4.3之后通过patch 18411339进行修复，此补丁包含在12.2中

临时解决办法:

select USERENV('Instance'), high.thread#, low.lsq, high.hsq
from
  (select a.thread#, rcvsq, min(a.sequence#)-1 hsq
   from v$archived_log a,
        (select lh.thread#, lh.resetlogs_change#, max(lh.sequence#) rcvsq
           from v$log_history lh, v$database_incarnation di
          where lh.resetlogs_time = di.resetlogs_time
            and lh.resetlogs_change# = di.resetlogs_change#
            and di.status = 'CURRENT'
            and lh.thread# is not null
            and lh.resetlogs_change# is not null
            and lh.resetlogs_time is not null
         group by lh.thread#, lh.resetlogs_change#
        ) b
   where a.thread# = b.thread#
     and a.resetlogs_change# = b.resetlogs_change#
     and a.sequence# > rcvsq
   group by a.thread#, rcvsq) high,
(select srl_lsq.thread#, nvl(lh_lsq.lsq, srl_lsq.lsq) lsq
   from
     (select thread#, min(sequence#)+1 lsq
      from
        v$log_history lh, x$kccfe fe, v$database_incarnation di
      where to_number(fe.fecps) <= lh.next_change#
        and to_number(fe.fecps) >= lh.first_change#
        and fe.fedup!=0 and bitand(fe.festa, 12) = 12
        and di.resetlogs_time = lh.resetlogs_time
        and lh.resetlogs_change# = di.resetlogs_change#
        and di.status = 'CURRENT'
      group by thread#) lh_lsq,
     (select thread#, max(sequence#)+1 lsq
      from
        v$log_history
      where (select min( to_number(fe.fecps))
             from x$kccfe fe
             where fe.fedup!=0 and bitand(fe.festa, 12) = 12)
      >= next_change#
      group by thread#) srl_lsq
   where srl_lsq.thread# = lh_lsq.thread#(+)
  ) low
where low.thread# = high.thread#
and lsq < = hsq
and hsq > rcvsq;