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: Brief definition Individual wait details (eg: For waits seen in <<View:V$SESSION_WAIT>>) Systemwide wait details (eg: For waits seen in <<View:V$SYSTEM_EVENT>>) Reducing waits / wait times Known Issues / Bugs 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: P1 = driver id P2 = bytes# P3 = Not used driver id 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