重庆思庄Oracle、Redhat认证学习论坛

标题: 'SQL*Net message from dblink' Waits On ALTER SESSION Commands [打印本页]

作者: 刘泽宇 时间: 2024-5-26 13:06
标题: 'SQL*Net message from dblink' Waits On ALTER SESSION Commands
现象：
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...

原因：
There is an open database link within the current user session.

Every ALTER SESSION executed locally will be executed at remote site as well if dblink is being used and has not been closed.

处理方法：
Explicit close the database link. You can close an open dblink in two different ways,

i. Using alter session command:

ALTER SESSION CLOSE DATABASE LINK [dblink name];

or

ii. Calling PL/SQL procedure:

  DBMS_SESSION.CLOSE_DATABASE_LINK ( dblink_name )

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)