因大表无索引致ogg同步延迟几十小时

系统环境

oracle 11.2.0.4+ogg12.2.0.1.0

延迟现象

GGSCI (cluster-10-176-50-29 as ogg@yjgk2) 169> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
REPLICAT RUNNING REP_PS1 14:27:09 00:00:57
REPLICAT RUNNING REP_PS2 00:00:02 00:00:10
REPLICAT RUNNING REP_PS3 00:00:03 00:00:10
REPLICAT RUNNING REP_PS4 00:00:03 00:00:03
REPLICAT RUNNING REP_PS5 00:00:05 00:00:01
REPLICAT RUNNING REP_PS6 00:00:05 00:00:01
REPLICAT RUNNING REP_PS7 00:00:00 36:55:12
通过send rep_ps7,status查看事务提交的记录非常慢

问题分析与解决

问题分析
1、查看对哪些张业务表执行什么操作慢
如果查看呢？通过以下方式来排查
1、查看复制进程对应的操作系统进程

[oracle@cluster-10-176-50-29 ~]$ ps -ef |grep rep_ps7
oracle   100611 204642  0 Jan11 ?        00:09:19 /home/oracle/ogg_12/replicat PARAMFILE /home/oracle/ogg_12/dirprm/rep_ps7.prm REPORTFILE /home/oracle/ogg_12/dirrpt/REP_PS7.rpt PROCESSID REP_PS7 USESUBDIRS
oracle   193720  58421  0 10:12 pts/5    00:00:00 grep --color=auto rep_ps7
[oracle@cluster-10-176-50-29 ~]$ ps -ef |grep 100611
oracle   100611 204642  0 Jan11 ?        00:09:19 /home/oracle/ogg_12/replicat PARAMFILE /home/oracle/ogg_12/dirprm/rep_ps7.prm REPORTFILE /home/oracle/ogg_12/dirrpt/REP_PS7.rpt PROCESSID REP_PS7 USESUBDIRS
oracle   100629 100611 76 Jan11 ?        1-12:47:22 oracleyjgk2 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   198394  58421  0 10:15 pts/5    00:00:00 grep --color=auto 100611

2、根据操作进程找到对应的会话

select a.spid process_id,b.SID,b.SERIAL#,b.PROGRAM,b.SQL_ADDRESS from v$process a,v$session b where a.ADDR=b.PADDR and a.SPID='100629'

3、根据会话找到执行的相应SQL_ID

select  m.SQL_ID,m.EVENT,m.BLOCKING_SESSION_STATUS,m.CURRENT_OBJ#,count(*)  from v$active_session_history m where m