RE: Tuning Over a DBLINK?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 22 Jan 2014 17:39:50 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DD7B78_at_exmbx05.thus.corp>

Standard nested loop:

First child first, then for each row returned from the first child execute the second child.

So the remote SQL is executed and rows fetched from the remote site (possibly in batches of 15 to 100, but I'd have to check that). For each row from the remote the local table mstore_tab is probed by primary key.

This part of the plan looks quite reasonable IN PRINCIPLE, but possibly the return set is quite large and the previous plan did a hash join doing a build with the mst_store_tab and probing with the remote result set. The nested loop join would then be doing a very large number of small round trips while the hash join would do a small number of large round trips - and the roundtrip time may be the significant change. Alternatively it's possible that the remote query has simply changed plans to something silly - in which case you may find that it has been captured in the AWR/Statspack repository and you may be able to find the before and after execution plans for it if you're allowed to run awrsqrpt.sql or sprepsql.sql on the remote database.

If you're licensed (and this is 11g) you can use the v$sql_monitor report to see where the time first went. There's an OEM screen if you have access, otherwise a query of this type should work:

set linesize 180
column text_line format a180

select

        dbms_sqltune.report_sql_monitor(
                sql_id                  => '{the SQL ID goes here}',
                start_time_filter       => sysdate - 30/(24 * 60),  -- assuming it happened in the last 30 minutes
                type                    =>'TEXT'
        ) text_line

from dual;

Someone else might like to confirm the query - I'm quoting it from memory.

Regards
Jonathan Lewis

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Uzzell, Stephan [SUzzell_at_MICROS.COM] Sent: 22 January 2014 17:06
To: 'tim_at_evdbt.com'; oracle-l_at_freelists.org Cc: Uzzell, Stephan
Subject: RE: Tuning Over a DBLINK?

Hi Tim,

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

Received on Wed Jan 22 2014 - 18:39:50 CET