My refresh materialized view is not refreshing – Part 1
Posted by decipherinfosys on September 29, 2008
We will be posting some blog posts for what to monitor and look for if your refresh materialized view is not refreshing as expected. Part 1 i.e. this blog post will discuss checking the job scheduler for the materialized view refresh.
Refresh materialized views, for most, are something we don’t ever think about once implemented unless something goes wrong. One of the typical issues we may expect to see in a refresh materialized view is 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). The first question we should ask ourselves, is what is the general health of the materialized view.
We have several major parts to the materialized views:
1) The job that is scheduled to run the materialized view.
2) The materialized view table, also known as the mlog table.
3) The master table. (AKA Source)
4) The materialized view. (AKA Target)
In this post we are checking the scheduled job for refreshing the materialized view. This is a good place to start, as it is at a high level and is easy to check and interpret. There are a few things we will want to check to see if the materialized view is in a healthy functioning state. We will want to see when the last refresh was run, when it is scheduled to run next, and if the job is broken. The below query will allow you to see all of this (written for 10gR2). The query should be run on the location of the target as sysdba.
SELECT /*+ RULE */
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,
DECODE(BROKEN,’Y’,’YES’,’N’,’NO’,’ ‘) IS_BROKEN,
FROM DBA_JOBS A
LEFT OUTER JOIN (SELECT /*+ RULE */
FROM DBA_JOBS_RUNNING ) B
ON A.JOB = B.JOB
ORDER BY SCHEMA_USER, MVIEW_NAME;
The real meat of the data are the LAST_RUN_DATE, NEXT_SCHED_RUN_DATE, IS_BROKEN, and FAILURES. These four columns will indicate if the job has been running, when the job will run next, if the job is in a working status, and how many failures (if any) that occurred since the last successful run, respectively.
The other columns in the query will help us to determine which materialized view we are looking at. Some other information of interest is to see if the job is currently running, represented by IS_RUNNING and what is the SID value of the job. Please keep in mind that these are the values from the target location, not the source location.
Tips on interpreting the data
If the LAST_RUN_DATE is from 5 hours ago and your refresh job is scheduled for every 15 minutes, this would indicate a lack in the ability for the materialized view to refresh itself. The LAST_RUN_DATE does not get updated until the materialized view refresh has completed (i.e. until the job has completed). The NEXT_SCHED_RUN_DATE operates in the same fashion. It will not update until the job is finished.
The IS_BROKEN column will indicate if the job is broken. This is important, because if the job is broken it is not running. If the job is not running, then the materialized view is not refreshing.
In the next post of this series, we will check the mlog table for the materialized view. The mlog table is the table on the source that records the changes (from the master table) to be applied on the materialized view on the target.