Re: Tuning Over a DBLINK?
- From: Tim Gorman <tim@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 21 Jan 2014 17:09:33 -0700
Stephen,
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*
Other related posts:
