RE: Tuning Over a DBLINK?

Thanks for the response. I used dbms_xplan to display the cursor, but that just opens so many more questions (or reveals just how little I know)... Or maybe Oracle is just not displaying the plan most clearly:

| 14 | NESTED LOOPS | | 1 | 246 | 241 (0)| 00:00:03 | | |
| 15 | REMOTE | GUEST_CHECK_LINE_ITEM_HIST | 4 | 748 | 233 (0)| 00:00:03 | MMHMS | R->S |
| 16 | TABLE ACCESS BY INDEX ROWID| MST_STORE_TAB | 1 | 59 | 2 (0)| 00:00:01 | | |
|* 17 | INDEX RANGE SCAN | MST_STORE_TAB_PK | 1 | | 1 (0)| 00:00:01 | | | To me, the fact that 17 is the furthest indented suggests that is where Oracle starts - finding the right row in MST_STORE_TAB via PK, and then uses that to find the relevant rows from GCLIH...

But if I look at the query from the display_cursor, it seems that isn't what's happening... it seems to be using the rest of the local where clause to drive what it queries across the DB link:

SELECT "ORGANIZATIONID","LOCATIONID","REVENUECENTERID","BUSINESSDATE","TRANSDATETIME","GUESTCHECKID","DETAILTYPE","RECORDID","UWSID","CHECKEMPLOYEEID","MANAGEREMPLOYEEID","VOIDFLAG","REPORTLINETOTAL", "DONOTSHOW","MEALEMPLOYEEID" FROM "LOCATION_ACTIVITY_DB"."GUEST_CHECK_LINE_ITEM_HIST" "SYS_ALIAS_5" WHERE "MEALEMPLOYEEID"="CHECKEMPLOYEEID" AND "DETAILTYPE"=2 AND "BUSINESSDATE"<=:1 AND "BUSINESSDATE">=:2 AND ("DONOTSHOW"=0 OR "DONOTSHOW" IS NULL)

So how do I identify what Oracle is actually doing first - where is it starting, and what's flowing into what?

Thanks!

Stephan Uzzell

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman Sent: Tuesday, 21 January, 2014 19:10
To: oracle-l_at_freelists.org
Subject: Re: Tuning Over a DBLINK?

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

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