Re: Materialized views

From: Kellyn Pedersen <kjped1313_at_yahoo.com>
Date: Mon, 7 Mar 2011 09:12:36 -0800 (PST)
Message-ID: <834770.25520.qm_at_web120207.mail.ne1.yahoo.com>

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_at_david-aldridge.com> To: rajesh.kella_at_gmail.com; oracle-l_at_freelists.org 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_at_gmail.com> To: oracle-l_at_freelists.org
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


      
--
http://www.freelists.org/webpage/oracle-l

Received on Mon Mar 07 2011 - 11:12:36 CST