Home » Oracle » Oracle Database » How to monitor the progress of refresh of Materialized views

How to monitor the progress of refresh of Materialized views

Here in this post, we will find out how to monitor Materialized views refresh

What is a 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, known as snapshots.
We have already explained how to create materialized view and materialized view log
Oracle materialized view and materialized view log

How to find the definition of Materialized View

Suppose it is already created in the database and you want to query the definition.
The below sql  will help in that

set long 2000;
select query from user_mviews where mview_name='MV_TEST'; or select query from user_mviews where mview_name='MV_TEST';
monitor  Materialized views refresh

How to monitor the progress of refresh of Materialized views

Many times it happens that the 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

Schedule

First, we will need to check the job that is scheduled to run the materialized view

  • It could be done using a refresh group
  • It could be manually refreshed using some cronjob or some other scheduling
See also  Step by Step resolution for the problem oacore not coming up in R12.0/R12.1

For refresh group

The below queries give the information about group

select * from dba_refresh;
select * from dba_refresh_children;
select * from sys.v_$mvrefresh;

For Schedule for dba_jobs

The below query is to find the status of the 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 take? All those details can be found out

We also have to check if the job-queue_processes parameter is adequately set up.

sqlplus / as sysdba
show parameter job_queue_processes
alter system set job_queue_processes=20 scope=both;

materialized view log

The next thing to check is the MVlog table in the source database. We need to check how many changes happening/every hour

select
(select count() from .MLOG$_<MASTER TABLE NAME>  WHERE DMLTYPE$$ = ‘U’) UPDATES, 
(select count() from .MLOG$_<MASTER TABLE NAME>  WHERE DMLTYPE$$ = ‘I’) INSERTS,
(select count() from .MLOG$_<MASTER TABLE NAME>  WHERE DMLTYPE$$ = ‘D’) DELETES, 
(select count() from .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 fewer changes in a refresh

The Source table

The other thing to check is the master table. If many changes happen and many queries run on the master table simultaneously with refresh time, then again it will slow down the materialized view refresh
The performance of the source and target database and network utilisation should also be checked

See also  AD Utilities(adadmin, adrelink , adident, ADMRGPCH)

The target materialized view

If the materialized view is being refreshed currently, you can check the progress using

sqlplus / as sysdba
SELECT * FROM V$SESSION_LONGOPS;

If the materialized view refresh is taking time, we can enable trace and find out the explain plan for the execution using below useful articles

how to enable trace in Oracle
Oracle Explain Plan

I hope you like this article on how to monitor Materialized views refresh

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top