Re: Database link usage per object?

From: Karl Arao <karlarao_at_gmail.com>
Date: Tue, 18 Oct 2011 21:52:39 -0500
Message-ID: <CACNsJneO7fFYZhKLLrY=AgC2i8o0foxBG6cp2FRME6F-tYp1QA_at_mail.gmail.com>

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 < brian.s.wisniewski_at_jpmchase.com> 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

Received on Tue Oct 18 2011 - 21:52:39 CDT