Re: Tuning Over a DBLINK?

Try to obtain the execution plan information using the procedure DISPLAY_CURSOR from the DBMS_XPLAN package (which it looks like you might be using already). The "remote information" section of that report will contain the text of the query that is executed on the remote side, so you can copy/paste the text directly into a connection on the remote database and tune it there. If that doesn't work for some reason, move over to the remote site and attempt to find the SQL in question from there.

Don't waste time guessing and trying various hints. Tune the SQL itself.

Good luck!

-Tim

On 1/21/2014 3:20 PM, Uzzell, Stephan wrote:
>
> 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