RE: Tuning Over a DB Link (redux)
Date: Wed, 19 Feb 2014 19:26:22 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DDE0D5_at_exmbx05.thus.corp>
Allowing for the fact that 1M round-trips in 500 to 800 reported seconds leaves plenty of scope for rounding errors, your comment is correct.
Broadly speaking, the local database spends 800 seconds waiting for the remote database to supply 1M fragments of data, and spends 500 seconds doing something between fragments before asking for the next fragment. Moving the schema to the remote machine could eliminate most of the 800 seconds (all other things being equal).
Have you posted execution plans to show your best plan so far - optimising over a database link basically tends to mean finding the best compromise between volume of traffic and number of messages, with a strong bias to reducing number of messages. Choosing the correct driving site and join order is very important.
If you use dbms_xplan.display_cursor() on the local database it should report the SQL sent to the remote so you can cross-check that you've got the right session.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: Uzzell, Stephan [SUzzell_at_MICROS.COM] Sent: 19 February 2014 19:07
To: Jonathan Lewis; oracle-l_at_freelists.org Cc: Uzzell, Stephan
Subject: RE: Tuning Over a DB Link (redux)
Thank you, Jonathan.
The session we traced on the remote side is capitalized and double-quoted, so I think that confirms we found the right one.
So if local is waiting for remote, and remote is waiting for local, I take it that means the db link is in-and-of-itself a significant contributor to the problem? If so, moving the local schema into the remote database ought to do wonders for this particular part of the application…
Stephan Uzzell
From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk] Sent: Wednesday, 19 February, 2014 13:49 To: Uzzell, Stephan; oracle-l_at_freelists.org Subject: RE: Tuning Over a DB Link (redux)
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<mailto: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<mailto: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-lReceived on Wed Feb 19 2014 - 20:26:22 CET