FAQ
Anyone have a good way to determine how much data is being sent across database links on a per-object basis? I'm trying to find our biggest offenders and I'm having a hard time trying to track down actual usage. I wrote this to find some info about what is being called and how many times but the bytes from sql_plan aren't going to be accurate for what I'm trying to track.
Database is 11.1.0.7

col operation for a15;
col object_node for a30;
col object_owner for a15;
col object_name for a30;
col other_tag for a18;

select sp.operation, sp.object_node, sp.object_owner, sp.object_name, sp.other_tag, round(sum(sp.bytes)/(1024*1024),0) Meg, count(*)
from v$sql_plan sp
where sp.operation = 'REMOTE'
group by sp.operation, sp.object_node, sp.object_owner, sp.object_name, sp.other_tag
order by 7, sp.object_name
;

Thanks - Brian


This communication is for informational purposes only. It is not
intended as an offer or solicitation for the purchase or sale of
any financial instrument or as an official confirmation of any
transaction. All market prices, data and other information are not
warranted as to completeness or accuracy and are subject to change
without notice. Any comments or statements made herein do not
necessarily reflect those of JPMorgan Chase & Co., its subsidiaries
and affiliates.

This transmission may contain information that is privileged,
confidential, legally privileged, and/or exempt from disclosure
under applicable law. If you are not the intended recipient, you
are hereby notified that any disclosure, copying, distribution, or
use of the information contained herein (including any reliance
thereon) is STRICTLY PROHIBITED. Although this transmission and any
attachments are believed to be free of any virus or other defect
that might affect any computer system into which it is received and
opened, it is the responsibility of the recipient to ensure that it
is virus free and no responsibility is accepted by JPMorgan Chase &
Co., its subsidiaries and affiliates, as applicable, for any loss
or damage arising in any way from its use. If you received this
transmission in error, please immediately contact the sender and
destroy the material in its entirety, whether in electronic or hard
copy format. Thank you.

Please refer to http://www.jpmorgan.com/pages/disclosures for
disclosures relating to European legal entities.

Search Discussions

  • Karl Arao at Oct 19, 2011 at 2:54 am
    I would use this query..
    http://jkstill.blogspot.com/2010/03/whos-using-database-link.html then use
    snapper http://tech.e2sn.com/oracle-scripts-and-tools/session-snapper to
    profile the session IDs

    -Karl



    On Tue, Oct 18, 2011 at 9:44 PM, Wisniewski, Brian S wrote:

    Anyone have a good way to determine how much data is being sent across
    database links on a per-object basis? I'm trying to find our biggest
    offenders and I'm having a hard time trying to track down actual usage. I
    wrote this to find some info about what is being called and how many times
    but the bytes from sql_plan aren't going to be accurate for what I'm trying
    to track.
    Database is 11.1.0.7

    col operation for a15;
    col object_node for a30;
    col object_owner for a15;
    col object_name for a30;
    col other_tag for a18;

    select sp.operation, sp.object_node, sp.object_owner, sp.object_name,
    sp.other_tag, round(sum(sp.bytes)/(1024*1024),0) Meg, count(*)
    from v$sql_plan sp
    where sp.operation = 'REMOTE'
    group by sp.operation, sp.object_node, sp.object_owner, sp.object_name,
    sp.other_tag
    order by 7, sp.object_name
    ;

    Thanks - Brian


    This communication is for informational purposes only. It is not
    intended as an offer or solicitation for the purchase or sale of
    any financial instrument or as an official confirmation of any
    transaction. All market prices, data and other information are not
    warranted as to completeness or accuracy and are subject to change
    without notice. Any comments or statements made herein do not
    necessarily reflect those of JPMorgan Chase & Co., its subsidiaries
    and affiliates.

    This transmission may contain information that is privileged,
    confidential, legally privileged, and/or exempt from disclosure
    under applicable law. If you are not the intended recipient, you
    are hereby notified that any disclosure, copying, distribution, or
    use of the information contained herein (including any reliance
    thereon) is STRICTLY PROHIBITED. Although this transmission and any
    attachments are believed to be free of any virus or other defect
    that might affect any computer system into which it is received and
    opened, it is the responsibility of the recipient to ensure that it
    is virus free and no responsibility is accepted by JPMorgan Chase &
    Co., its subsidiaries and affiliates, as applicable, for any loss
    or damage arising in any way from its use. If you received this
    transmission in error, please immediately contact the sender and
    destroy the material in its entirety, whether in electronic or hard
    copy format. Thank you.

    Please refer to http://www.jpmorgan.com/pages/disclosures for
    disclosures relating to European legal entities.
    --
    http://www.freelists.org/webpage/oracle-l


    --
    Karl Arao
    karlarao.wordpress.com
    karlarao.tiddlyspot.com


    --
    http://www.freelists.org/webpage/oracle-l
  • Anthony Ballo at Oct 21, 2011 at 5:58 pm
    Does anyone have any clues on what causes these Alert Log errors?

    Errors in file /u05/prod/bdump/prod2_smon_31452.trc:
    ORA-21779: duration not active
    Errors in file /u05/prod/bdump/prod2_smon_31452.trc:
    ORA-21779: duration not active
    Errors in file /u05/prod/bdump/prod2_smon_31452.trc:
    ORA-21779: duration not active
    Errors in file /u05/prod/bdump/prod2_smon_31452.trc:
    ORA-21779: duration not active
    Errors in file /u05/prod/bdump/prod2_smon_31452.trc:
    ORA-21779: duration not active
    Errors in file /u05/prod/bdump/prod2_smon_31452.trc:
    ORA-21779: duration not active
    Errors in file /u05/prod/bdump/prod2_smon_31452.trc:
    ORA-21779: duration not active
    Errors in file /u05/prod/bdump/prod2_smon_31452.trc:
    ORA-21779: duration not active


    From what I was able to gather online, the root cause of the problem is
    SMON that is not clearing up some transient types:


    SELECT *
    FROM dba_types
    WHERE type_name LIKE 'SYS%=='
    AND length(type_name) = 29

    But I don't understand why?

    We have found that flushing the Shared Pool stops the error. It happens on
    occasion - about once a month it seems.

    We are on: OEL, 10.2.0.4 in a 2-node RAC environment. These errors
    appeared on node 2.



    Any other insight would be most helpful - thanks.



    Anthony
  • Grzegorz Goryszewski at Oct 21, 2011 at 6:47 pm

    On 2011-10-21 19:57, Anthony Ballo wrote:
    Does anyone have any clues on what causes these Alert Log errors?

    Errors in file /u05/prod/bdump/prod2_smon_31452.trc:
    ORA-21779: duration not active
    Errors in file /u05/prod/bdump/prod2_smon_31452.trc:
    ORA-21779: duration not active
    Hi,
    You are probably aware of MOS note:
    *SMON: Following Errors Trapped And Ignored ORA-21779 [ID 988663.1]

    Regards
    GG

    *


    ----------------------------------------------------------------------
    Nie wybrałaś jeszcze fryzury na ślub? Zobacz nasze propozycje.
    Sprawdź >>> http://linkint.pl/f2a48
  • Anthony Ballo at Oct 21, 2011 at 7:16 pm
    Thanks - I didn't know they were harmless messages...(other than Alert log
    file filling up). Just wanted to ensure that we weren't doing something
    wrong by flushing the SP and to see if anything evolved from this.



    On 10/21/11 11:46 AM, "Grzegorz Goryszewski" wrote:
    On 2011-10-21 19:57, Anthony Ballo wrote:
    Does anyone have any clues on what causes these Alert Log errors?

    Errors in file /u05/prod/bdump/prod2_smon_31452.trc:
    ORA-21779: duration not active
    Errors in file /u05/prod/bdump/prod2_smon_31452.trc:
    ORA-21779: duration not active
    Hi,
    You are probably aware of MOS note:
    *SMON: Following Errors Trapped And Ignored ORA-21779 [ID 988663.1]

    Regards
    GG

    *


    ----------------------------------------------------------------------
    Nie wybrałaś jeszcze fryzury na ślub? Zobacz nasze propozycje.
    Sprawdź >>> http://linkint.pl/f2a48
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Sriram Kumar at Oct 19, 2011 at 3:28 am
    Hi,
    in my opinion, there is no direct method but we could possibly do one by
    instrumenting some code. You could store the value for "bytes received/sent
    via SQL*Net from/to dblink" from v$SESSTAT and the access the object and
    then check the value again?. I have not tried this myself but should work.
    do let me know if this works or otherwise

    best regards

    sriram kumar


    On Wed, Oct 19, 2011 at 8:14 AM, Wisniewski, Brian S wrote:

    Anyone have a good way to determine how much data is being sent across
    database links on a per-object basis? I'm trying to find our biggest
    offenders and I'm having a hard time trying to track down actual usage. I
    wrote this to find some info about what is being called and how many times
    but the bytes from sql_plan aren't going to be accurate for what I'm trying
    to track.
    Database is 11.1.0.7

    col operation for a15;
    col object_node for a30;
    col object_owner for a15;
    col object_name for a30;
    col other_tag for a18;

    select sp.operation, sp.object_node, sp.object_owner, sp.object_name,
    sp.other_tag, round(sum(sp.bytes)/(1024*1024),0) Meg, count(*)
    from v$sql_plan sp
    where sp.operation = 'REMOTE'
    group by sp.operation, sp.object_node, sp.object_owner, sp.object_name,
    sp.other_tag
    order by 7, sp.object_name
    ;

    Thanks - Brian


    This communication is for informational purposes only. It is not
    intended as an offer or solicitation for the purchase or sale of
    any financial instrument or as an official confirmation of any
    transaction. All market prices, data and other information are not
    warranted as to completeness or accuracy and are subject to change
    without notice. Any comments or statements made herein do not
    necessarily reflect those of JPMorgan Chase & Co., its subsidiaries
    and affiliates.

    This transmission may contain information that is privileged,
    confidential, legally privileged, and/or exempt from disclosure
    under applicable law. If you are not the intended recipient, you
    are hereby notified that any disclosure, copying, distribution, or
    use of the information contained herein (including any reliance
    thereon) is STRICTLY PROHIBITED. Although this transmission and any
    attachments are believed to be free of any virus or other defect
    that might affect any computer system into which it is received and
    opened, it is the responsibility of the recipient to ensure that it
    is virus free and no responsibility is accepted by JPMorgan Chase &
    Co., its subsidiaries and affiliates, as applicable, for any loss
    or damage arising in any way from its use. If you received this
    transmission in error, please immediately contact the sender and
    destroy the material in its entirety, whether in electronic or hard
    copy format. Thank you.

    Please refer to http://www.jpmorgan.com/pages/disclosures for
    disclosures relating to European legal entities.
    --
    http://www.freelists.org/webpage/oracle-l


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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 19, '11 at 2:45a
activeOct 21, '11 at 7:16p
posts6
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase