RE: Tuning Over a DB Link (redux)

From: <mvshelton_at_chartermi.net>
Date: Wed, 19 Feb 2014 14:07:20 -0500 (EST)
Message-ID: <755ef445.8f6d3.1444b8b8c13.Webtop.43_at_chartermi.net>


A typical performance problem I have seen with query's using database links is nested loop joins between 2 large tables. Just something to keep in mind when tuning with remote objects.

Thanks, Matt

On Wed, Feb 19, 2014 at 1:48 PM, Jonathan Lewis wrote:

As far as the remote database is concerned, your local database is its client, so "message to/from client" is what you should see.

A clue that you're looking at the right SQL is that the SQL coming into the remote database will be capitalised, double-quoted, with aliases like "Annn" for tables.

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: 19 February 2014 18:33
To: oracle-l_at_freelists.org
Cc: Uzzell, Stephan
Subject: Tuning Over a DB Link (redux)

I’ve asked bits about this before, but I feel like I finally have made some headway… And I think it points at the DB Link as the culprit in our performance issue. We managed to trace one of our problem queries today, and we managed to find the session it spawned in the remote database and trace that too.

Locally:

   SQL*Net message from dblink 1013412 0.51 807.41

   SQL*Net more data from dblink                3586        0.19 
13.56

That’s pretty clear I think.

One the remote side:

   SQL*Net message to client                  983709        0.00 
2.13
   SQL*Net message from client                983709       23.26 
479.04

My assumption here is that the local is waiting on the remote, but the remote is also waiting on the local? If that’s the case, if the db link in and of itself is a big part of our problem we will move the local schema into the remote database.

I guess my question is – am I interpreting the SQL*Net message from client on the remote side correctly? If the remote waits said “dblink” I’d consider that case closed. As it is, I still think the remote waits are from the incoming connection over the dblink – but not certain.

Can anyone confirm that? Or set me straight?

Thanks!

Stephan Uzzell

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Feb 19 2014 - 20:07:20 CET

Original text of this message