Snapshots and SYS.MLOG$

Hello All,

We used to have snapshots on a few tables (@ 12) from a master database to more than one other databases. Recently, we added snapshots on 20+ tables to another database. These new snapshots include all the ones that were snapshoted elsewhere before plus some new ones.

All these are FAST refresh snapshots. We have never used the NEXT clause of the "CREATE SNAPSHOT..." statement to define the refresh frequency but instead, we have defined DBMS_JOBS for every snapshot. So, we have 20+ such jobs defined in the new database. These jobs are scheduled to execute every 10 minutes.

The problem is : Since we added these new snapshots, we have started seeing blocking locks on SYS.MLOG$. The locking session is one of the refresh processes and the blocked one is a process that is trying to refresh snapshot on the same table in other database.

It was argued (without proof) that the DBMS_JOBS mechanism is causing this to happen and if we defined the refresh intervals using the NEXT clause of the "CREATE SNAPSHOT..." DDL itself, it won't happen. I didn't buy that and in fact proved that its nothing to do with this.

But, the problem still exists. Any ideas @ why this might be happening ?

The databases involved are 806 (all master tables) and mix of 806/817 (the snapshot sites).

Thanks.