Re: Materialized views
- From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- To: david@xxxxxxxxxxxxxxxxxx, rajesh.kella@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Mon, 7 Mar 2011 09:12:36 -0800 (PST)
I monitor mview refresh times across networks to remote locations a bit too
often... :) I think I have what you are looking for, please let me know if
this
monitors the type of throughput you are referring to. This would monitor the
refresh as it was occurring, not post, "how did it do" types...
Hope this helps,
set lines 128
set trimout on
column "MVIEW BEING REFRESHED" format a22
column SID format 999999
column ROWS_PROCESSES format 999,999,999
select to_char(sysdate,'hh24:mi:ss') as NOW,
SID_KNST as SID,
CURRMVOWNER_KNSTMVR || '.' || CURRMVNAME_KNSTMVR "MVIEW
BEING REFRESHED",
decode( REFTYPE_KNSTMVR, 1, 'FAST', 2, 'COMPLETE', 'UNKNOWN' ) REFTYPE,
decode(GROUPSTATE_KNSTMVR, 1, 'SETUP', 2, 'INSTANTIATE', 3, 'WRAPUP',
'UNKNOWN' ) STATE,
( TOTAL_INSERTS_KNSTMVR /* INSERTS */ +
TOTAL_UPDATES_KNSTMVR /* UPDATES */ +
TOTAL_DELETES_KNSTMVR /* DELETES */ ) as ROWS_PROCESSED
from X$KNSTMVR X
WHERE type_knst=6
and exists (select 1 from v$session s
where s.sid=x.sid_knst
and s.serial#=x.serial_knst);
Kellyn Pedersen
Multi-Platform Database Administrator
www.pythian.com
http://www.linkedin.com/in/kellynpedersen
http://dbakevlar.com
________________________________
From: David Aldridge <david@xxxxxxxxxxxxxxxxxx>
To: rajesh.kella@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Sent: Mon, March 7, 2011 4:51:17 AM
Subject: Re: Materialized views
I'd guess that you might try turning on monitoring for the MV's base table and
check user_tab_modifications after each refresh (flushing the stats beforehand).
________________________________
From: Rajesh Kella <rajesh.kella@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Sent: Mon, 1 November, 2010 21:37:38
Subject: Materialized views
Does anyone monitor the materialized views for Master-Destination databases or
is there a way to measure the throughput of data being replicated between
databases
ideas and scripts will be appreciated
--
Thanks
Kella Rajesh
Other related posts:
