How to monitor the progress of refresh of Materialized views



Last updated on November 23rd, 2015 at 07:22 am

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

  1. It could be done using refresh group
  2. 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 utlization should also be checked

 


Leave a Reply