2 Replies Latest reply: Mar 27, 2012 3:01 AM by POegerli RSS

    MV replication to 2500 clients

    POegerli
      Hi all

      I have to review a solution concept which is based on a materialized view replication of about 30 tables to 2500 local databases on notebooks (read only). The total size of these tables is about 100MB and the solution provider plans to use MV logs and the FORCE option for the refresh.

      Questions:
      - are there any restricions on how many replications can be configured for a specific table?
      - does anybody know a similar solution and has any experiences with some thousands of replications?
      - what is the "general opinion" on this solution concept?

      Thank you very much for your inputs.

      Pascal

      Edited by: POegerli on Mar 27, 2012 8:44 AM
        • 1. Re: MV replication to 2500 clients
          Hemant K Chitale
          2500 notebook databases ?
          If the solution is to use MV Logs, I guess that they plan to implement FAST refresh's.
          However, the MV Log can grow very rapidly if even a single one of those 2500 "remote" databases does not do a Refresh. Oracle will keep maintaining the MV Log "waiting" for that one lone ranger to come back -- and this means that the MV Log will grow and refresh's by the other 2,499 databases may, after some time, perform poorly.

          See
          http://hemantoracledba.blogspot.com/2007/01/large-growing-snapshot-logs-indicate.html
          and
          http://hemantoracledba.blogspot.com/2012/01/growing-materialized-view-snapshot-logs.html
          and Oracle Support Note #1031924.6


          Hemant K Chitale
          • 2. Re: MV replication to 2500 clients
            POegerli
            Thank you very much for your answer.

            According to the solution provider, the mvews will be configured with refresh force and the mview logs on the server side will be cleaned on a daily basis, so that entries older than 30 days are removed. A client that hasn't updated for more than 30 days will perform a full refresh.

            Bu I have no idea nor a reference with such numbers of clients doing mviews replications. Therefore it's quite difficult to preview the impact on the times used for fast and full refreshes...

            Regards

            Pascal