Skip to Main Content
  • Questions
  • Sessions waiting forever on SQL*Net message from db link but no session in link target db

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Martin.

Asked: July 31, 2017 - 11:56 pm UTC

Last updated: January 25, 2024 - 5:49 am UTC

Version: 11.2.0.4

Viewed 10K+ times! This question is

You Asked

Dear AskTom-Team,

one of my customers has multiple Standard edition database locations worldwide (different continents) and data is transferred via database links in a PL/SQL fashion via job scheduler framework. There is one "management db", which is used as scheduler and this db has links to all databases. It then performs DML statements like: INSERT INTO table@target_db SELECT ... FROM table@source_db WHERE ... ;

It turns out that every couple of weeks, a session is hung waiting on "SQL*Net message from db link" with SECONDS_IN_WAIT increasing but SEQ# staying. When I check the sessions on the database, where the link is pointing to, I do not see a session. Both databases (Windows) have SQLNET.EXPIRE_TIME set to 10. SQL_ID only shows the top level PL/SQL scheduler call, but not the actual statement.

I am having a hard time troubleshooting this any further. Enabling SQL*Net tracing would be one option, but the issue only happens sporadically.

1) Do you recommend to adapt TCP send/receive windows in tnsnames/listener.ora´s?
2) Is there sample code on how to handle exceptions regarding distributed transactions? Is there a way to let the PL/SQL code break out from the SQL*Net message from db link wait?
3) Would it be useful to check db link response with a dummy query (select null from dual@<target|source>_db) and only perform the DML statmeent if this was successful?
4) Should we proactively close the db link after use and if yes, after every query or at the end of the job?

Best regards,
Martin



and Connor said...

If you're absolutely sure there is no session on the remote end, then yeah, things are more complicated.

Some things to try:

1) Add session auditing ("audit session") to the username on the remote database, so you can track when the db link connection comes in, and if it terminates abnormally versus you getting a 'logoff' record in the audit trail. Because it seems very strange to have a session "disappear" (as opposed to normally exit) without any record of it on the remote node.

2) Keep an eye on the following stats for the initiating session:

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

to see if there is any pattern to the volume/frequency of calls.

3) Regarding your '3','4' points above, I've used both techniques in the past when talking to heterogeneous services, where you're at the whim of a number of layers in the conversation (eg, dblink => HS => ODBC => something else etc etc)

4) Have you tried *without* any dead connection detection set ? (I'm just just wondering if that's interfering with normal processing in an unexpeted way)

5) Upgrade :-)

Things have been revamped a little in 12c as per the whitepaper below, but I'll concede that's a fairly large step :-)

https://www.oracle.com/docs/tech/database/oraclenetdcd.pdf

Hope this helps.

Rating

  (1 rating)

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

Comments

reference is dead

Aree, January 24, 2024 - 6:43 pm UTC

Could not find pdf link. answer is outdated because of dead reference.
Connor McDonald
January 25, 2024 - 5:49 am UTC

linked updated.

Thanks for letting us know