Re: Materialized views

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: