一次逻辑从库应用日志缓慢的问题的定位及解决

这台逻辑从库自从建立之后,就没怎么管,因为应用也没有迁移的紧迫性,也不急着迁移过来.这段时间主要还是看看在日志应用的过程中是不是会遭遇bug而导致日志应用不下去的问题.所以只是跳过了比如mlog$这样一些肯定要跳过去的对象,没有仔细的针对应用看看还需要跳过哪些对象.监控上也主要是看看是不是会遭遇bug而导致应用不下去的情况,所以只是每2个小时检查一下
SELECT * FROM V$LOGSTDBY_PROGRESS p where nvl(p.LATEST_TIME-p.APPLIED_TIME,10)>1/12;
看看是不是会返回数据行.每天凌晨2:15的时候,都会收到告警的邮件,说日志应用延迟超过了2个小时,之后包括4:15就不再收到邮件了,说明只是那段时间日志应用缓慢,之后就跟上来了,而不是遭遇bug导致lsp进程终止了,而且其它监控包括告警日志文件的监控也没有收到错误信息,所以也就没有太关注这个逻辑从库.
这段时间,闲了下来,就决定看看到底是什么导致那个时间段日志应用缓慢了.就简单的写了个shell脚本,包括下面的内容:
insert into zsj_logstdby_progress
select sysdate gather_time,p.applied_scn,p.APPLIED_TIME,p.LATEST_SCN,p.LATEST_TIME,p.MINING_SCN,p.MINING_TIME from v$logstdby_progress p;
每5分钟从os端调用执行一次.
其实我原来认为那段时间日志应用缓慢很可能是因为晚上12点开始执行的一系列统计更新的job造成的呢,因为这台数据库主库的一个特点就是一般晚上要比白天负载高,因为晚上从12点开始要有一系列的统计更新的job执行,而且很多的静态发布是在晚上执行的.但查看zsj_logstdby_progress这个表数据的时候,发现不是这样的,应该不是因为晚上12点开始执行的一系列job造成的(其实细想也是这样的,那一系列job要统计信息也要更新表,是从12点开始执行的,有12点开始执行的,也有1点,2点,3点,4点,5点执行的,而前面检测日志应用缓慢的脚本是每2个小时执行一次的,它2:15的时候发现有日志应用延迟超过2小时的,那么这个缓慢是12点之前就开始的可能性是很大的),从主库时间23:43:16开始applied_time很长时间都不进行下去了,而这个时间是我的一个package差不多要执行完的时间.我查看了一下我的package执行的日志记录,从时间序列上来看,确定很可能是这样的代码引起的:
         dbms_application_info.set_module(module_name => null,action_name => null);

         EXECUTE IMMEDIATE 'truncate table queryproductresult';
         INSERT INTO queryproductresult(CATALOGID,BRANDID,SERIESID,PRICEID,SCATALOGITEMS,PRODUCTCOUNT,COUNTTIME,ID,PAGEURL,NEWPAGEURL)
                                 SELECT CATALOGID,BRANDID,SERIESID,PRICEID,SCATALOGITEMS,PRODUCTCOUNT,COUNTTIME,ID,PAGEURL,NEWPAGEURL
                                   FROM queryproductresult_app2;
         COMMIT; --把数据填充进正式表里去

         mark_del_newpageurl();
         error_alert(v_publish_start_date);

         INSERT INTO publish_log(id,message) VALUES(seq_publish_log_id.NEXTVAL,'pack_publish.main end');

         COMMIT;
很可能是因为这里的insert into queryproductresult引起的,虽然说我也有些怀疑,因为它这里只是插入数据呀,而且queryproductresult这个表主键id,业务上的唯一索引都有的呀,按说不应该是因为它呀,但我当时也没有细想,还是决定对它做些什么:
这个表只是我这里truncate之后一次性的插入数据,然后,静态发布的一些应用读取这个表数据,也就是说只是我这里对它进行dml操作的,所以我决定从逻辑从库端skip过它:
ALTER DATABASE STOP LOGICAL STANDBY APPLY;
execute dbms_logstdby.skip (stmt => 'DML',schema_name => 'PRODUCTUSER', object_name => 'QUERYPRODUCTRESULT');
execute dbms_logstdby.skip (stmt => 'SCHEMA_DDL',schema_name => 'PRODUCTUSER', object_name => 'QUERYPRODUCTRESULT');
ALTER DATABASE ST