AskTom

Monitoring Refreshes
=======================
The following queries can be used to monitor mview refreshes.
This is part of metelink note 258252.1 MATERIALIZED VIEW REFRESH: Locking, Performance, Monitoring

When an mview was last successfully refreshed
———————————————–
The following query will indicate when an mview was last successfully
refreshed and what type of refresh was done.

column last_refresh_type format a18
column owner format a7
column mview_name format a12
select owner, mview_name, last_refresh_type, last_refresh_date
from dba_mviews;

OWNER   MVIEW_NAME   LAST_REFRESH_TYPE  LAST_REFRESH_DATE
——- ———— —————— ——————
SCOTT   CREF_DEPT    COMPLETE           24-JAN-03 22:17:25
SCOTT   MYDEPT       FAST               23-JAN-03 15:44:24
SCOTT   MY_EMP       FAST               27-JAN-03 21:40:13
SCOTT   UPSNP_DEPT   COMPLETE           24-JAN-03 14:35:37

Status of a Refresh Group Using the Job Queue
———————————————
The following query shows all the jobs scheduled in the job queue to perform
refreshes.  It shows when the last refresh was run, the total amount of time
spent by the system running the job since the instance started, if the job is
broken or has failures, and when the next refresh will run.

column broken format a6
alter session set nls_date_format=’DD-MON-YY hh24:MI:SS’;

select job, last_date last_refresh,
next_date next_refresh, total_time,
broken, failures, what
from dba_jobs
where what like ‘%dbms_refresh%’;

JOB LAST_REFRESH       NEXT_REFRESH       TOTAL_TIME BROKEN   FAILURES
—– —————— —————— ———- —— ———-
WHAT
————————————————————————
1 05-FEB-03 16:37:57 05-FEB-03 16:47:57          2 N               0
dbms_refresh.refresh(’”SYS”.”REFRESHG1″‘);

Note: TOTAL_TIME - you can determine how long (in seconds) a refresh job takes
to run by querying total_time for the job before and after the job runs,
and calculating the difference.  This is helpful in determining what
interval to use to stager refreshes.

Refreshes Currently Running
——————————–
The following query shows all refresh jobs that are currently running, when
they started, and if any have failed.

select r.job, r.this_date, r.failures
from all_jobs_running r, all_jobs j
where j.job = r.job
and j.what like ‘%dbms_refresh%’;

JOB  THIS_DATE            FAILURES
———- —————— ———-
1 05-FEB-03 16:37:57          0

If an mview belongs to a refresh group, you can also use the ALL_REFRESH
and ALL_REFRESH_CHILDREN to obtain information about the mview refresh

select r.rowner, r.rname, r.job, c.name,
r.next_date next_refresh, r.broken
from all_refresh r, all_refresh_children c
where r.job = c.job;

ROWNER     RNAME             JOB NAME       NEXT_REFRESH       BROKEN
———- ————— —– ———- —————— ——
SYS        REFRESHG1           1 MYDEPT     05-FEB-03 16:47:57 N

Regards,

Tom

Hi,

my replication job doesn’t run. I found in alert.log this error:

Errors in file /opt/app/oracle/admin/replica3/bdump/replica3_j000_22900.trc:
ORA-12012: error on auto execute of job 363
ORA-12034: materialized view log on “TOM”.”TESTTABLE” younger than last refresh
ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2254
ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2460
ORA-06512: at “SYS.DBMS_IREFRESH”, line 683
ORA-06512: at “SYS.DBMS_REFRESH”, line 195
ORA-06512: at line 1
Tue Aug 25 19:19:29 2009
Errors in file /opt/app/oracle/admin/replica3/bdump/replica3_j000_22900.trc:
ORA-12012: error on auto execute of job 303
ORA-12048: error encountered while refreshing materialized view “TALMASTER”.”TBLBMC”
ORA-02049: timeout: distributed transaction waiting for lock

Solution:
ther is useful Note:204127.1 Diagnosing ORA-12034 Materialized View Log Younger than Last Refresh
But in my case help when I re-created the MV.

Second error

ORA-02049: timeout: distributed transaction waiting for lock

Metalink note: 1018919.1 789517.1 and look at adjusting your distributed_lock_timeout value and distributed_recovery_connection_hold_time.

Cause: The time to wait on a lock in a distributed transaction has been exceeded. This time is specified in the initialization parameter DISTRIBUTED_LOCK_TIMEOUT.
Action: This situation is treated as a deadlock and the statement was rolled back. To set the time-out interval to a longer interval, adjust the initialization parameter DISTRIBUTED_LOCK_TIMEOUT, then shut down and restart the instance.
From the docs:
The connection remains open until you end your local session or until the number of database links for your session exceeds the value of the initialization parameter OPEN_LINKS. If you want to reduce the network overhead associated with keeping the link open, then use this clause to close the link explicitly if you do not plan to use it again in your session.

Regards,

Tom