Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Krishnaprasad.

Asked: March 22, 2018 - 5:52 am UTC

Last updated: March 23, 2018 - 3:44 am UTC

Version: 12.1.2.0

Viewed 10K+ times! This question is

You Asked

Hi Team ,


we are facing issue with sql query , which is using dblink in it , most of wait event observed is sql*net message from dblink , from remote database we have figured out the plan , and it seems to good , but we are not able to get that wait event is due to network problem ?

is their any way to find out issue/slowness is related to network ???






and Connor said...

Take a look at the following statistics as well

SQL*Net roundtrips to/from dblink
bytes received via SQL*Net from dblink

which can give you some idea of exactly *what* is being transferred across the network.

"sql*net message from dblink" is not *necessarily* an indicator of a network issue. For example, lets say your query is something as simple as:

select count(*) from t@remote_link

That will probably be run entirely on the remote node (because there is nothing do to locally). If it runs for (say) 10 seconds, then your local session will have 10 seconds of waiting for a message from the db link, because we are doing 10 seconds of work on the remote node.

If the roundtrips/bytes transferred are high - this might be indicative of a poor plan (for this particular network) where you are repeatedly going back and forth on a row by row basis. If they are low, you might be consuming a lot of resource on the remote node - you can look at the SQL running on the remote node to see if any remedial action can be taken.



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database