RE: Tuning Over a DBLINK?
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 22 Jan 2014 17:39:50 +0000
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
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Uzzell, Stephan [SUzzell@xxxxxxxxxx]
Sent: 22 January 2014 17:06
To: 'tim@xxxxxxxxx'; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Tim Gorman
Sent: Tuesday, 21 January, 2014 19:10
To: oracle-l@xxxxxxxxxxxxx
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
Other related posts:
