关于高级复制引起log file sync等待的探讨

最新推荐文章于 2025-12-04 15:25:15 发布

转载最新推荐文章于 2025-12-04 15:25:15 发布 · 132 阅读

文章标签：

本文探讨了Oracle数据库在使用高级复制过程中遇到的logfilesync等待问题，并提供了相关SQL语句实例。logfilesync等待可能会影响数据同步效率。

我们这里的配置：一台主站点，3台物化视图站点，在主站点v$session_wait经常有log file sync等待，抓到相关session的SQL,语句基本上是跟高级复制有关的SQL
select decode(bitand(s.flags,1),0,0,1), NVL(max(s.scn), 0)  from sumpartlog$ s  where s.bo# = :1 and nvl(s.scn,0) <= :2  group by decode(bitand(s.flags,1),0,0,1)
update sys.mlog$ set oldest = :1, oldest_pk = :2, oldest_oid = :3,  oldest_new = :4, youngest = :5  where master = :6 and mowner = :7
update sys.mlog$ set oldest = :1, oldest_pk = :2, oldest_oid = :3,  oldest_new = :4, youngest = :5  where master = :6 and mowner = :7
select decode(bitand(s.flags,1),0,0,1), NVL(max(s.scn), 0)  from sumpartlog$ s  where s.bo# = :1 and nvl(s.scn,0) <= :2  group by decode(bitand(s.flags,1),0,0,1)
select decode(bitand(s.flags,1),0,0,1), NVL(max(s.scn), 0)  from sumpartlog$ s  where s.bo# = :1 and nvl(s.scn,0) <= :2  group by decode(bitand(s.flags,1),0,0,1)
update sys.mlog$ set oldest = :1, oldest_pk = :2, oldest_oid = :3,  oldest_new = :4, youngest = :5  where master = :6 and mowner = :7
update sys.mlog$ set oldest = :1, oldest_pk = :2, oldest_oid = :3,  oldest_new = :4, youngest = :5  where master = :6 and mowner = :7
update sys.mlog$ set oldest = :1, oldest_pk = :2, oldest_oid = :3,  oldest_new = :4, youngest = :5  where master = :6 and mowner = :7
select decode(bitand(s.flags,1),0,0,1), NVL(max(s.scn), 0)  from sumpartlog$ s  where s.bo# = :1 and nvl(s.scn,0) <= :2  group by decode(bitand(s.flags,1),0,0,1)
update sys.mlog$ set oldest = :1, oldest_pk = :2, oldest_oid = :3,  oldest_new = :4, youngest = :5  where master = :6 and mowner = :7
环境：linux as 4 oracle 9.2.0.4
从这里看出，高级复制会产生log file sync等待，应该说数据同步高级复制算是一种成熟的方法，难道ORACLE对这个技术没有考虑过会有log file sync等待吗，就像单次提交和批量提交一样，我想ORACLE公司应该能考虑到这个问题！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/751051/viewspace-731749/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/751051/viewspace-731749/