My Oracle Support Banner

'SQL*Net message from dblink' Waits On ALTER SESSION Commands (Doc ID 1922699.1)

Last updated on SEPTEMBER 11, 2021

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.5 to 12.1.0.1 [Release 10.2 to 12.1]
Information in this document applies to any platform.

Symptoms

Noticed a high number of ALTER SESSION commands, especially when modifying NLS parameters, waiting on either "SQL*Net message from dblink" or "SQL*Net message to dblink"

For example, when tracing the session (10046 SQL trace), the tkprof output displays several sections like this:

SQL ID: 090ux6wjw3as5 Plan Hash: 0

alter session set NLS_LANGUAGE = 'AMERICAN'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       15      0.00       0.00          0          0          0           0
Execute     15      0.03       6.85          0         15         71           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       30      0.04       6.85          0         15         71           0

Misses in library cache during parse: 0
Parsing user id: 263     (recursive depth: 1)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to dblink                     135        0.00          0.00
  SQL*Net message from dblink                   135        0.51          6.82

 

and

 

SQL ID: 2mwu6j6vvd3js Plan Hash: 0

alter session set NLS_CALENDAR = 'GREGORIAN'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       11      0.00       0.00          0          0          0           0
Execute     11      0.02       5.11          0         11         50           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       22      0.02       5.11          0         11         50           0

Misses in library cache during parse: 0
Parsing user id: 263     (recursive depth: 1)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to dblink                      99        0.00          0.00
  SQL*Net message from dblink                    99        1.08          5.08

 etc...

 

 

 

Changes

 No changes.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Symptoms
Changes
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.