Re: Tuning Over a DBLINK?

From: Juan Carlos Reyes Pacheco <jcdrpllist_at_gmail.com>
Date: Tue, 21 Jan 2014 18:50:12 -0400
Message-ID: <CAGYrQytQgV88gvXHKRpb635FHcj5SrOL6U+4Jd7mMx2Vozhd2A_at_mail.gmail.com>

For remote queries I use two options
1) the hint /*+DRIVING_SITE(table)*/
 2) create a function table in the remote database, to get only the data I want
3) I suppose I never tried, because I have standard one database, but a materialized view could help too

2014/1/21 Uzzell, Stephan <SUzzell_at_micros.com>

> Hi all,
>
>
>
> Hoping someone can point me in the right direction, or give me some basic
> reading material here…
>
>
>
> We have a query that (apparently) used to perform well, but recently runs
> 10+ minutes (long enough that the web-based front end times out). The
> problem, the reason I don’t know how to approach it, is that it is a query
> against a simple table joined to a view. And the view is a join of several
> tables from the other side of a db link.
>
>
>
> I don’t want to dump the whole plan here, but hopefully I’m not stripping
> out too much too relevant:
>
>
>
> | 12 | NESTED LOOPS |
> | 1 | 250 | 11948 (1)| 00:02:24 | | |
>
> | 13 | REMOTE | GUEST_CHECK_LINE_ITEM_HIST
> | 4 | 748 | 11944 (1)| 00:02:24 | MMHMS | R->S |
>
> | 14 | TABLE ACCESS BY INDEX ROWID| MST_STORE_TAB
> | 1 | 63 | 1 (0)| 00:00:01 | | |
>
> | 15 | INDEX UNIQUE SCAN | MST_STORE_TAB_PK
> | 1 | | 0 (0)| | | |
>
>
>
> Unfortunately, I can’t tell from this what the access is for
> GUEST_CHECK_LINE_ITEM_HIST, and that is a significantly large partitioned
> table. Large enough that I’m sure it isn’t doing a FTS, or it would take a
> heck of a lot longer than this…
>
>
>
> How do I tune / investigate across a db link? Is there somewhere to see
> what is getting hidden under the operation “REMOTE”?
>
>
>
> Thanks!
>
>
>
> *Stephan Uzzell*
>
>
>

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

Received on Tue Jan 21 2014 - 23:50:12 CET