| How to monitor refresh rate of MV? [message #145595] |
Fri, 04 November 2005 01:30  |
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
|
Senior Member |
|
|
HI all
My scenario:
3 DB servers running on 3 different machines on 3 different networks.
DB: DX
Server :SX
DB: DY
Server: SY
DB: DZ
Server: SZ
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:
DX->DY->DZ
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
select
o.owner "Owner",
o.object_name "Mat View",
s.username "Username",
s.sid "Sid",
s.serial# "Serial#"
from
v$lock l,
dba_objects o,
v$session s
where
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.
--Girish
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
