Re: Database link usage per object?

From: Karl Arao <>
Date: Tue, 18 Oct 2011 21:52:39 -0500
Message-ID: <>

I would use this query.. then use snapper to profile the session IDs


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
> 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 for
> disclosures relating to European legal entities.
> --

Karl Arao

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

Original text of this message