WAITEVENT: "SQL*Net message from dblink" Reference Note


"SQL*Net message from dblink" Reference Note

This is a reference note for the wait event "SQL*Net message from dblink" which includes the following subsections:

See Note:61998.1 for an introduction to Wait Events.

Definition:

  • Versions: 7.3 - 11.2
    Documentation:10.1 10.2 11.1 11.2
  • The Oracle shadow process is waiting for a message over a database link from a remote process. Note that this wait is also used when waiting for data from "extproc" or from a remote gateway process.

Individual Waits:

  Parameters:

In Oracle8i onwards P1RAW can be decoded into ASCII characters to give a clue as to which Net driver is used.
Eg: P1RAW=0x62657100 = 'beq\0' , P1RAW=0x54435000 = 'TCP\0' etc.

In earlier releases the value here is the value of the disconnect function of the Net driver being used (which is not much use).

  • bytes# (number of bytes received)

The number of bytes we need to receive. This figure may be misleading it is often a "guess" of how many bytes might be sent in the next packet as opposed to the real number of bytes expected. (eg: It may be just 1 even though the expected packet will be much larger, or it may be a large number even if only a few bytes are needed)

  Wait Time:

This wait blocks until a message is received from the remote connection (or until an abnormal end of file condition occurs on the underlying Net transport layer). There is no Oracle timeout on the wait.

  Finding Blockers:

The blocker is the network plus the remote process. If the remote process is another database instance (accessed via a database link) then the information in <<View:V$SESSION>> on the REMOTE database can be used to help find the remote session for the database link connection. You should look at that remote session and determine where is is spending time.

Systemwide Waits:

If systemwide waits for this event are significant it is best to determine where the remote connections are to and switch attention to the remote instance / instances to determine where they are spending time. One can also look at:

·         Sessions with high values in <<View:V$SESSTAT>> for:

o    SQL*Net roundtrips to/from dblink

o    bytes sent via SQL*Net to dblink

o    bytes received via SQL*Net from dblink

·         The Network between the local and remote systems (problems are usually related to time spent ON the remote instance rather than in the network but it is worth checking if the network between the instances is slow / not)

Reducing Waits / Wait times:

It is normally best to see what propertion of the time is actually spent on the remote instance as the most common cause of long waits over a DB link is that the time is actually all spent doing work on the remote instance (either waiting or working).

·         Trace the local session and the remote session to see what work a user "transaction" actually consists of

·         Check the execution plans for any distributed queries

·         See if frequently accessed remote data can be kept in a local snapshot (Materialized View).

Known Issues / Bugs:

You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button:

 

NB

Prob

Bug

Fixed

Description

 
 

-

21949743

12.2.0.0

Drop materialized view execution hang after base table deleted

V11020001 V11020002 V11020003 V11020004 V12010001 V12010002

 

II

13728397

11.2.0.4, 12.1.0.1

Drop materialized view hangs

V11020001 V11020002 V11020003

 

I

10096945

11.2.0.4, 12.1.0.1

Client dump (ttcfopr) if fix for bug 6039623 installed / reduced dblink round trips

V11020001 V11020002 V11020003

 

II

9119194

10.2.0.4.4, 10.2.0.5, 11.2.0.1.2, 11.2.0.1.BP06, 11.2.0.2, 12.1.0.1

Excessive remote roundtrips with fix for Bug 5354469

V10020004 V11010006 V11010007 V11020001

 

IIII

8477973

11.2.0.2, 12.1.0.1

Multiple open DB links / ORA-2020 / distributed deadlock / ORA-600 possible using DB Links

V10010005 V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 V11020001

 

I

6039114

11.2.0.1

Select over database link from AUTONOMOUS_TRANSACTION procedure hangs

V10020002 V10020003 V10020004 V10020005 V11010006 V11010007

 

-

8617060

10.2.0.5

Excessive remote roundtrips for INSERT-SELECT

V10020002 V10020003 V10020004

·         '*' indicates that an alert exists for that issue.

·         '+' indicates a particularly notable issue / bug.

·         See Note:1944526.1 for details of other symbols used



Related:

Tracing User sessions Note:62160.1

 


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值