5 Replies Latest reply: Nov 29, 2010 9:22 PM by 742842 RSS

    Questions on Materialized Views and MV Log tables

      Hello all,

      Have a few questions with regards to Materialized View.

      1) Once the Materialized View reads the records from the MLOG table the MLOG's records get purged. correct? or is it not the case? In some cases I still see (old) records in the MLOG table even after the MV refresh.

      2) How does the MLOG table distinguish between a read that comes from an MV and a read that comes from a user? If I manually execute
      "select * from <MLOG table>" would the MLOG table's record get purged just the same way as it does after an MV refresh?

      3) One of our MV refreshes hangs intermittently. Based on the wait events I noticed that it was doing a "db file sequential read" against the master table. Finally I had to terminate the refresh. I'm not sure why it was doing sequential read on the master table when it should be reading from the MLOG table. Any ideas?

      4) I've seen "db file scattered read" (full table scan) in general against tables but I was surprised to see "db file sequential read" against the table. I thought sequential read normally happens against indexes. Has anyone noticed this behaviour?

      Thanks for your time.
        • 1. Re: Questions on Materialized Views and MV Log tables
          1) Once all registered materialized views have read a particular row from a materialized view log, it is removed, yes. If there are multiple materialized views that depend on the same log, they would all need to refresh before it would be safe to remove the MV log entry. If one of the materialized views does a non-incremental refresh, there may be cases where the log doesn't get purged automatically.

          2) No, your query wouldn't cause anything to be purged (though you wouldn't see anything interesting unless you happen to implement lots of code to parse the change vectors stored in the log). I don't know that the exact mechanism that Oracle uses has been published, though you could go through and trace a session to get an idea of the moving pieces. From a practical standpoint, you just need to know that when you create a fast-refreshable materialized view, it's going to register itself as being interested in particular MV logs.

          3) It would depend on what is stored in the MV log. The refresh process may need to grab particular columns from the table if your log is just storing the fact that data for a particular key changed. You can specify when you create a materialized view log that you want to store particular columns or to include new values (with the INCLUDING NEW VALUES) clause. That may be beneficial (or necessary) to the fast refresh process but it would tend to increase the storage space for the materialized view log and to increase the cost of maintianing the materialized view log.

          4) Sequential reads against a table are perfectly normal-- it just implies that someone is looking at a particular block in the table (i.e. looking up a row in the table by ROWID based on the ROWID in an index or in a materialized view log).

          • 2. Re: Questions on Materialized Views and MV Log tables
            Thanks Justin for the clear explanation. Last time when the MV refresh hung, I terminated the process, dropped and recreated the MV again. Now when I refresh the MV again , would the MV know that it does not have to consider the old change vectors in the MLOG table? When would the old change vectors from the MLOG table get purged in this scenario?

            • 3. Re: Questions on Materialized Views and MV Log tables
              The materialized view knows that it doesn't need older log entries, yes.

              It is theoretically possible that the materialized view would not know that the dropped materialized view was really gone (generally, this only happens when you're using materialized views for replication and the link between systems goes down when you drop and recreate and even then it's an error) in which case you could use the dbms_mview.purge_log or dbms_mview.purge_mview_from_log procedure to purge the old entries if you see a stray entry in DBA_REGISTERED_MVIEWS.

              • 4. Re: Questions on Materialized Views and MV Log tables
                Thanks Justin. Appreciate your help...
                • 5. Re: Questions on Materialized Views and MV Log tables
                  One more question...When we do a fast refresh, Oracle reads the change vectors from the MLOG and applies the changes to the MV...Seems like Oracle does a commit only after applying all the changes This was apparent because whenever I did a count(*) from the MV, the count never increased while the MV refresh was in progress. Only after the MV refresh comepleted I noticed an increase in the record count. Is there a way to track the progress of a fast MV refresh? Currently I have a master table whose MLOG has 300K records (mostly "insert" change vectors) in database A. The MV is in database B. The MV fast refreshes over the database link. The refresh has been running for about 2 hours now but I don't see an increase in the MV record count. I see from the session_wait_history and ASH that the MV refresh is progressing as the wait events keep changing but I'm unable to detremine how many MLOG records it has processed so far and how may records are left...Any ideas?

                  Thanks for your time...