How to monitor the progress of refresh of Materialized views

What is materialized view:A materialized view in Oracle is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table’s data. Materialized views, which store data based on remote tables are also, know as snapshots.
We have already explained how to create materialized view and materialized view log
Suppose  it is already created in the database and you want to query the defination.
The below sql  will help in that
select query from dba_mviews where mview_name=’TEST_PARTY_ID_EMP_V’;
How to monitor the progress of refresh of Materialized views:
Many times it happens that materialized view is not refreshing from the master table(s) or the refresh is just not able to keep up with the changes occurring on the master table(s).
In these cases, we should look at below  things
1)The job that is scheduled to run the materialized view.
2) The materialized view log in case of fast refresh
3) The Source table
4) The target materialized view
First  we will need to check at the job which is scheduled to run the materialized view
It could be done using refresh group
It could be manually refresh using some cronjob or some other scheduling
For refresh group, the below queries gives the information about group
select * from dba_refresh;
select * from dba_refresh_children;
select * from sys.v_$mvrefresh;
Then below query to find the status of job.
SELECT  /*+ RULE */
A.JOB JOB#,
SCHEMA_USER MVIEW_OWNER,
DECODE(SUBSTR(WHAT,INSTR(WHAT,’.’,1,2)+2,INSTR(WHAT,’”‘,1,4)-4-INSTR(WHAT,’.’,1,2)+2),NULL,SUBSTR(WHAT,1,40), SUBSTR(WHAT,INSTR(WHAT,’.’,1,2)+2,INSTR(WHAT,’”‘,1,4)-4-INSTR(WHAT,’.’,1,2)+2)) MVIEW_NAME,
LAST_DATE LAST_RUN_DATE,
NEXT_DATE NEXT_SCHED_RUN_DATE,
DECODE(BROKEN,’Y’,’YES’,’N’,’NO’,’ ‘) IS_BROKEN,
FAILURES,
RUNNING IS_RUNNING,
B.SID SID
FROM DBA_JOBS A
LEFT OUTER JOIN (SELECT /*+ RULE */
JOB,’YES’ RUNNING,SID
FROM DBA_JOBS_RUNNING ) B
ON A.JOB = B.JOB
ORDER BY SCHEMA_USER, MVIEW_NAME;
We can find out if the job is broken. How much time last refresh took.All those detail can be find out
We also have to check if job-queue_processes parameter is adequately setup.
The next thing to check the MVlog table in the source database. We need to check how many changes happening/every hour
select
(select count(*) from <owner>.MLOG$_<MASTER TABLE NAME> WHERE DMLTYPE$$ = ‘U’) UPDATES,
(select count(*) from <owner>.MLOG$_<MASTER TABLE NAME> WHERE DMLTYPE$$ = ‘I’) INSERTS,
(select count(*) from <owner>.MLOG$_<MASTER TABLE NAME> WHERE DMLTYPE$$ = ‘D’) DELETES,
(select count(*) from <owner>.MLOG$_<MASTER TABLE NAME>) TOTAL
from dual
If the changes are high, the refresh will take time. It may be required to increase the frequency of the refresh so as to have less changes in a refresh
The other thing to check the master table. If many changes happening  and many queries running on master table  simultaneously with refresh time,then again it will slow down  the materialized view refresh
The performance of source and target database and network utilization should also be checked
What is Oracle materialized view
A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses “master tables” for consistency. The databases containing the master tables are called the master databases.When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data. You must have the privileges necessary to create these objects.
What is Oracle Materialized View Log
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.
Refresh Group
A refresh group is a collection of one or more materialized views that Oracle refreshes in an atomic transaction, guaranteeing that relationships among the master tables are preserved
Examples:
CREATE MATERIALIZED VIEW mv_test
TABLESPACE test_data
BUILD IMMEDIATE
REFRESH complete ON COMMIT AS
SELECT a.name,b.salary FROM emp@DB_LINK a, compensation@DB_LINK b where a.emp_id=b.emp_id;
CREATE MATERIALIZED VIEW mv_test
TABLESPACE test_data
BUILD IMMEDIATE
REFRESH force ON COMMIT AS
SELECT a.name,b.salary FROM emp@DB_LINK a, compensation@DB_LINK b where a.emp_id=b.emp_id;
CREATE MATERIALIZED VIEW LOG ON emp;
exec DBMS_REFRESH.MAKE(name=>’test_grp’, –
list=>’test_mv1,test_mv2′,’mv_test’ –
next_date => sysdate, –
interval => ‘null’);
exec DBMS_REFRESH.REFRESH(‘test_grp’);
Privileges required to create materialized view
User must have CREATE MATERIALIZED VIEW to create materialize view
General Syntax
CREATE MATERIALIZED VIEW <View Name>
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
[ON PREBUILT TABLE]
AS
<Select statement > ;
Explanation of each term
BUILD [IMMEDIATE | DEFERRED]
We can specify to populate immediately Or we can specify DEFERRED to populate on the first requested refresh.
REFRESH [FAST | COMPLETE | FORCE ]
There are three option here.Each explained below
1) FAST : A fast refresh is attempted. If materialized view logs are not present against the source tables in advance, the creation fails.
2) COMPLETE : The table segment supporting the materialized view is truncated and repopulated completely using the associated query.
3)FORCE : A fast refresh is attempted. If one is not possible a complete refresh is performed.
ON [COMMIT | DEMAND ] We can specify ON COMMIT so that  refresh is triggered by a committed data change in one of the dependent tables Or we can specify ON DEMAND so that   refresh is initiated by a manual request or a scheduled task.
[[ENABLE | DISABLE] QUERY REWRITE] The view is eligible for query rewrite
Difference between Oracle view and Oracle materialized view
As explained above Materialized views are disk based and are updated periodically based upon the query definition.In materialized view,result set is stored in the materialized view table
Views are virtual only and run the query definition each time they are accessed.In view no result set is stored  and it accesses the underlying table each time view is accessed