一般情况下无法从动态视图(v$session/v$session_wait)看到这个等待事件，因为它十分短暂。其本质从字面意思上来解释的话，是一种网络等待(network issue)；
举例而言，如果运行的代码中包含某种可能的错误，且在调用中触发了的话，服务器端本地的服务进程有义务对远程客户端告知该信息，这个告知的过程中服务进程就处于SQL*Net break/reset to client等待中，直到客户端收到问题信息为止。与一般意义上的Sever-client模式一样，使用dblink时也可能出现该种等待事件。
下面我们来尝试演示该种等待事件：
SQL> create table tv (t1 int unique); Table created. SQL> insert into tv values(1); 1 row created. SQL> commit; Commit complete. SQL> oradebug setmypid; Statement processed. SQL> oradebug event 10046 trace name context forever,level 8; Statement processed. SQL> insert into tv values(1); insert into tv values(1) * ERROR at line 1: ORA-00001: unique constraint (SYS.SYS_C009343) violated SQL> oradebug event 10046 trace name context off; Statement processed. SQL> select event, total_waits from v$session_event where event like '%break%' and sid =(select distinct sid from v$mystat); EVENT TOTAL_WAITS ---------------------------------------------------------------- ----------- SQL*Net break/reset to client 2
可以看到出现了2次SQL*Net break/reset to client ,进一步分析trace文件可以发现以下记录:
EXEC #2:c=8000,e=9020,p=0,cr=10,cu=9,mis=0,r=0,dep=0,og=1,plh=0,tim=1279117896316991 ERROR #2:err=1 tim=1279117896317039 STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL (cr=0 pr=0 pw=0 time=0 us)' WAIT #2: nam='SQL*Net break/reset to client' ela= 33 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=1279117896317323 WAIT #2: nam='SQL*Net break/reset to client' ela= 521 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=1279117896317915 WAIT #2: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1279117896317964
2次SQL*Net break/reset to client分别为33us和521us; 设计不良的应用程序或者程序中存在较多未安置的exception都可能导致SQL*Net break/reset to client等待事件。
Process Hanging When Running SQL Block/Procedure Across Dblink From 9i DB to 8i DB
Applies to:
Oracle Net Services – Version: 8.1.7.4.0
Oracle Net Services – Version: 9.0.1.4.0 to 9.2.0.8.0
This problem can occur on any platform.
Symptoms
Running a distributed INSERT query from within a PL/SQL block/procedure hangs across dblink from Oracle 9i to Oracle 8i.
The procedure is run every half hour. It runs fine for the first few hours, and then it hangs without errors.
Pstack shows both sides are in a read; system states show that the remote side is wait event ‘sqlnet break/reset to client’ and local session wait event is ‘null event’;
Stack backtrace: sou2o opidrv opiodr opiodr opiino opitsk opikndf2 nioqrc nsdo nsrdr nsprecv nttrd snttread _read sigacthandler sspuser ksdxcb ksdxfdmp ksedmp
Changes
– You changed the bind type and run INSERT as SELECT.
(or)
– Cursor is aged out for some reason and you go for rebind the cursor.
Cause
Issue in Oracle 8i, different designs between Oracle 9i and 8i platforms. Bug manifests in these cases:
– If you change the bind type and run insert as select.
– If cursor is aged out for some reason and if you go for rebind the cursor.
Bug 2722040 — DISTRIBUTED INSERT HANGS ON BREAK/RESET AND NULL EVENT
Solution
There is no fix for the Oracle 8i platform. Please choose from one of the following workarounds:
(1) Create a synonym at the remote site for the local view and use that in the SQL, eg:
REMOTE: CREATE SYNONYM TSPD9_v_mat_fx07 FOR v_mat_fx07@tspd9;
(using a relevant tns alias for tspd9)
LOCAL: INSERT INTO tgdst.Aktion_Produkt@gdst …
SELECT … FROM TSPD9_v_mat_fx07@gdst …
That should cause a separate link to be opened from the remote database back to the local end rather than using the existing link bi-directionally.
(2) Use literals in place of binds (not good for OLTP but if only run a few times might be simplest)
(3) Code as a PL/SQL loop inserting a row at a time (not good for large row counts)
(4) Upgrade the remote dblink end from 8i to 9.2 .
(5) Push all the data from the local view (with predicates) to a temporary table on the remote site and issue the insert using the remote temporary copy of the data.
Hdr: 5599476 10.1.0.4 RDBMS 10.1.0.4 PRG INTERFACE PRODID-5 PORTID-215 4402255
Abstract: UPDATE THROUGH DBLINK HANGS WAITING FOR SQLNET BREAK/RESET TO DBLINK/CLIENT EACH
*** 10/12/06 06:48 pm ***
TAR:
—-
PROBLEM:
——–
Following DML via DBLINK hangs.
WAIT EVENTs during the hang are as the following.
————————————————————-
Local Site : last wait for ‘SQL*Net break/reset to dblink’
Remote Site : last wait for ‘SQL*Net break/reset to client’
————————————————————-
– The both DB are 10.1.0.4.0 32bit for Windows Server 2003.
– The above DML is issued from SID kojizaimu to SID wf.
DIAGNOSTIC ANALYSIS:
——————–
It looks similar to the Bug:4402255, but the Remote Site call stack
does not contain opifcs, OCIServerversion/OCIPing/kpuping .
– Net Server Trace of Remote shows that
after receiving UPDATE statemnt, NSPTMK packet is
sent back to Local DB.
– Net Server Trace of Local shows that
after receiving NSPTMK packet, sends NSPTMK packet
to Remote DB.
Those situation looks the sessions are trying to
dissconnect. ERRORSTACK of the Remote process also shows
ocibre(=OCIBreak).
– ERRORSTACK of the remote during the hang is as following.
ntdll!KiFastSystemCallRet
ntdll!NtWaitForSingleObject+0xc
mswsock!SockWaitForSingleObject+0x19d
mswsock!WSPRecv+0x203
ws2_32!WSARecv+0x77
WARNING: Stack unwind information not available. Following frames may be
wrong.
orantcp10!snttclose+0x309
orantcp10!nttini+0x1964
oran10!nsprecv+0x827
oran10!nsrdr+0x174
oran10!nsdo+0xf04
oran10!nsbrecv+0x1e
oran10!nioqrc+0x340
oracle+0x60666d
oracommon10!ttcdrv+0x1760
oran10!nioqwa+0x2d
oraclient10!xaolog+0x3b1c4
oraclient10!xaolog+0x3aca9
oraclient10!upirtr+0x31
oraclient10!kpurcs+0x25
oraclient10!ocibre+0x142
– ERRORSTACK of the local during the hang was not taken by the Customer.
It may take time to reproduce this issu at the ct’s site, but
I will ask them to get the ERRORSTACK of the local if it is mandatory
to diagnose this issue.
WORKAROUND:
———–
Not found.
RELATED BUGS:
————-
Bug 4402255
REPRODUCIBILITY:
—————-
This is reproducible at the ct’s site. It reproces
most of the time when they run UPDATE from a client application.
TEST CASE:
———-
N/A
STACK TRACE:
————
I will upload “stacktrace.tar.gz” includes below files with README.TXT
– SYSTEM STATE
* Local Site (kojizaimu)
koujizaimu_ora_564.trc : at Wed Sep 20 14:17:25 2006
koujizaimu_ora_3820.trc : at Wed Sep 20 14:22:13 2006
koujizaimu_ora_2876.trc : at Wed Sep 20 14:44:48 2006
* Remote Site (wf)
wf_ora_3044.trc : at Wed Sep 20 14:17:28 2006
wf_ora_1068.trc : at Wed Sep 20 14:22:11 2006
wf_ora_4084.trc : at Wed Sep 20 14:44:48 2006
– Alert Log
* Local Site (kojizaimu)
alert_koujizaimu.log
* Remote Site (wf)
alert_wf.log
– v$view
* Local Site (kojizaimu)
lockchk_koujizaimu1.log : at 2006-09-20 14:16:04
lockchk_koujizaimu2.log : at 2006-09-20 14:19:07
lockchk_koujizaimu3.log : at 2006-09-20 14:43:02
* Remote Site (wf)
lockchk_wf1.log : at 2006-09-20 14:16:14
lockchk_wf2.log : at 2006-09-20 14:19:09
lockchk_wf3.log : at 2006-09-20 14:43:07
– Net Server Trace
* Local Site (kojizaimu)
svr_3920.trc : from 9-20 14:04 to 14:13
* Remote Site (wf)
svr_1212.trc : from 9-20 14:04 to 14:13
Hung session Information:
Local Remote
——— ———
v$process.PID 12 15
v$process.SPID 3920 1212
v$session.SID 136 142
v$session.PROCESS 2096:2100 1952:3920
Applies to:
Oracle Net Services
Information in this document applies to any platform.
Purpose
To provide a clearer understanding of the idle wait events with the name SQL*Net
Questions and Answers
What is a Wait Event ?
During the normal functioning of a database, an Oracle process will occasionally have to wait for something. These waits are recorded the RDBMS performance tables (V$views) where they are categorized into wait events.
There are two types of wait events, idle waits and non-idle waits. Non-idle waits indicate that a process is waiting for a resource that is temporarily unavailable, while idle waits simply mean that process has no work to do.
What is the meaning of SQL*Net Idle events ?
There are two type of SQL*Net events, SQL*Net client events and SQL*Net dblink events.
Often SQL*Net idle events are raised as a possible problem, due the large values seen for the events in database performance reports.
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————–Waited ———- ————
SQL*Net message to client 10000 0.00 0.00
SQL*Net message from client 10000 0.01 4.91
SQL*Net client
Generally, the SQL*Net client events represent idle events. That is when the client is infact waiting / doing no work and can be useful in indicating what is not the bottleneck. An example of this type of event is the most commonly encountered idle wait-event ‘SQL*Net message from client’.
One possible exception to this is the SQL*Net break/reset to client event.
SQL*Net break/reset to client
The server is sending a break or reset message to the client. The session running on the server is waiting for a reply from the client.These waits are caused by an application attempting to:
* Select from a closed cursor
* Select on a cursor after the last row has already been fetched and no data has been returned
* Select on a non-existent table
* Insert a duplicate row into a uniquely indexed table
* Issuing a query with invalid syntax
* If the value, v$session_wait.p2, for this parameter equals 0, it means a reset was sent to the client. A non-zero value means that the break was sent to the client.
SQL*Net message from dblink
This event signifies that the session has sent a message to the remote node and is waiting for a response from the database link. This time could go up because of the following:
* Network bottleneck, For information, see “SQL*Net message from client”.
* Time taken to execute the SQL on the remote node
It is useful to see the SQL being run on the remote node. Login to the remote database, find the session created by the database link, and examine the SQL statement being run by it.
* Number of round trip messages
Each message between the session and the remote node adds latency time and processing overhead. To reduce the number of messages exchanged, use array fetches and array inserts.
Oracle Net server tracing can also assist in confirming what is happening for the dblink.
Applies to:
Oracle Net Services – Version: 9.2.0.1.0 to 11.2.0.1 – Release: 9.2 to 11.2
Information in this document applies to any platform.
Checked for relevance on 18-JUN-2010
Goal
How to enable Oracle Net tracing for database links.
Solution
Database links use Oracle Net server code, thus Oracle Net server tracing needs to be enabled for tracing of database links. Add to the SQLNET.ORA file the following : eg $ORACLE_HOME/network/trace
TRACE_LEVEL_SERVER = 16
TRACE_DIRECTORY_SERVER =
TRACE_TIMESTAMP_SERVER = ON
DIAG_ADR_ENABLED=OFF < Required for 11g and newer This needs to be added to the location where the database link is created. If tracing is require for both ends of the database link, then Oracle Net server tracing needs to be enabled both ends of the database link. If the database link is using dedicate connection model, then tracing will start straight away.(For all connections using the database.) Shared server connection model, will require the dispatchers to be stopped and restarted. See Note 1005259.6 Shared Server (MTS) Diagnostics for further information on Shared Server. To locate the Oracle Net server trace file generated by the database link, search on the dblink name an/or error code generated when using the database link, Example: sqlplus scott/tiger SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 3 12:33:39 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning and Data Mining options SQL> select sysdate from dual@test;
SYSDATE
———
03-NOV-08
SQL>
cd to TRACE_DIRECTORY_SERVER
cd $ORACLE_HOME/network/trace
Grep for the database link name, sql used, error code, etc.
grep date *.trc
svr_3229.trc:[03-NOV-2008 12:33:48:476] nsprecv: 64 61 74 65 20 66 72 6F |date.fro|
Server trace svr_3229.trc is the file generated when the session used the
database link.This local server trace will show two connection ID values.
First value is the inital connection to the local database.
Second value is the connection to the remote database.
grep Connection ID svr_3229.trc
[03-NOV-2008 12:33:39:915] nas_scn: Connection ID: 00c9c89d59c3
[03-NOV-2008 12:33:49:093] nas_ccn: Connection ID: 00c9d89d59f9d
Take the second value and move to the remote database
cd to TRACE_DIRECTORY_SERVER
cd $ORACLE_HOME/network/trace
Grep the connection ID
grep 00c9d89d59f9d *.trc
svr_3243.trc:[03-NOV-2008 12:33:49:170] nas_scn: Connection ID: 00c9d89d59f9d
Thus the matching traces for this session where called svr_3229.trc and svr_3243.trc.
hi,你好，我在statspack里面也发生了这个事件，而且比例很大，我想知道如何进一步查看trace文件，去确定发生问题的具体位置