Re: Database link usage per object?
Date: Wed, 19 Oct 2011 08:57:43 +0530
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
On Wed, Oct 19, 2011 at 8:14 AM, 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 126.96.36.199
> 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,
> 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.