FAQ
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 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
--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • Rajesh.kella_at_gmail.com at Nov 4, 2010 at 5:09 pm
    I would say, measure the throughput of data movement between source and destination database..
    Sent from my Verizon Wireless BlackBerry

    -----Original Message-----
    From: Bradd Piontek
    Date: Thu, 4 Nov 2010 11:03:41
    To:
    Cc:
    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 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
    --
    http://www.freelists.org/webpage/oracle-l
  • Slim Dave at Nov 5, 2010 at 6:54 am
    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_at_gmail.com"
    To: Bradd Piontek
    Cc: oracle-l_at_freelists.org
    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
    Date: Thu, 4 Nov 2010 11:03:41 -0500
    To:
    Cc:
    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 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
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 4, '10 at 4:03p
activeNov 5, '10 at 6:54a
posts3
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase