|How to monitor refresh rate of MV? [message #145595]
||Fri, 04 November 2005 01:30
Registered: April 2005
Location: Delhi (India)
3 DB servers running on 3 different machines on 3 different networks.
All DBs are Oracle 9204 & All servers are Win2k3.
I am having replication configured using MVs.
DX is my production DB & serves as Master site during replication.
Replication is happening in following sequence:
DY is serving as both master & slave, master for DZ & Slave for DX.
I have configured replication to happen every 30 mins on DY using DX as master & every 24 hrs on DZ using DY as master.
I can check which MV is getting refreshed at any moment in database using the query
o.object_name "Mat View",
o.object_id = l.id1 and
l.type ='JI' and
l.lmode = 6 and
s.sid = l.sid and
o.object_type = 'TABLE'
But my issue is many a times, MV takes more time then expected in getting refreshed.
I do check the expected time on basis of "refresh_method, fullrefreshtim,INCREFRESHTIM" fields in "user_mview_analysis"
My query is how can i monitor the rate of refresh for the MV & also ascertain how much more time its going to take for completion of task, even when the MV is getting refreshed.
Thanks in advance.