Monday, June 25, 2012

Beware of (fast) Materialized Views created with DBLinks

Today I was looking at a particular instance's MLOGs (logs created by Materialized Views with REFRESH FAST ON DEMAND) and noticed that the source MVs were not located on this server at all. These MVs were built on another instance referencing tables on this server by use of DBLinks.


Recap of MLOGs (or snapshot logs):
When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. Without a materialized view log, Oracle Database must re-execute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.
A materialized view log is located in the master database in the same schema as the master table. A master table can have only one materialized view log defined on it. Oracle Database can use this materialized view log to perform fast refreshes for all fast-refreshable materialized views based on the master table.
To fast refresh a materialized join view, you must create a materialized view log for each of the tables referenced by the materialized view.
Source: 
http://www.morganslibrary.org/reference/materialized_views.html
When I used this query to look at the underlying MVs, all of them had not had a refresh since 2009:

Select
a.OWNER,a.MASTER,a.MVIEW_LAST_REFRESH_TIME,m.OWNER,m.NAME,m.MVIEW_SITE
From dba_base_table_mviews a,dba_REGISTERED_MVIEWS m
Where m.MVIEW_ID=a.MVIEW_ID

Since these were never analyzed, I couldn't initially determine how much data they were holding so I analyzed these MLOG tables and they summed to about 80 million rows of data. 

With that information, and the knowledge that these MVs were no longer needed, I dropped the MVs on the remote instance and reanalyzed the MLOGs again. Most all the rows were removed! Great right? Well, now some cleanup on these tables needed to take place to give back this wasted space to the Tablespace.

Being on 10g, the "shrink" commands were used:

alter materialized view log on < table > enable row movement;
alter materialized view log on < table > shrink space compact;
alter materialized view log on < table > shrink space;
alter materialized view log on < table > disable row movement;
alter materialized view log on < table > deallocate unused;

Now onto what remaining MVs are requiring the MLOGs?

After verifying that no MVs existed referencing these Master tables, I dropped the snapshot logs since they were still being written to using:

DROP MATERIALIZED VIEW LOG ON < table >;




No comments:

Post a Comment