The first thing that sprang to my mind was to measure the DML activity on the materialised view's table by turning on monitoring for it, which may work out the same as querying X$KNSTMVR but would be done from the schema account not sysdba. After each refresh you flush the monitoring statistics and log them away, and reset them by gathering statistics on the table. > >From: "rajesh.kella@xxxxxxxxx" <rajesh.kella@xxxxxxxxx> >To: Bradd Piontek <piontekdd@xxxxxxxxx> >Cc: oracle-l@xxxxxxxxxxxxx >Sent: Thu, 4 November, 2010 17:09:56 >Subject: Re: Materialized views > > I would say, measure the throughput of data movement between source and >destination database.. >Sent from my Verizon Wireless BlackBerry ________________________________ >From: Bradd Piontek <piontekdd@xxxxxxxxx> >Date: Thu, 4 Nov 2010 11:03:41 -0500 >To: <rajesh.kella@xxxxxxxxx> >Cc: <oracle-l@xxxxxxxxxxxxx> >Subject: Re: Materialized views > >What type of monitoring are you looking for? > >You can monitor 'throughput' based on taking snapshots of a table on the >destination side, while the refresh is running. This must be run as a sysdba >account: > >select systimestamp,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 > 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); > > > > >Bradd Piontek > > > >On Mon, Nov 1, 2010 at 4:37 PM, Rajesh Kella <rajesh.kella@xxxxxxxxx> wrote: > >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 >> >> >