SQL*Net break/reset to client等待事件

一般情况下无法从动态视图(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等待事件。


  1. admin says

    Process Hanging When Running SQL Block/Procedure Across Dblink From 9i DB to 8i DB
    Applies to:
    Oracle Net Services – Version:
    Oracle Net Services – Version: to
    This problem can occur on any platform.
    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
    – You changed the bind type and run INSERT as SELECT.


    – Cursor is aged out for some reason and you go for rebind the cursor.
    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.


    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.

  2. admin says

    Hdr: 5599476 RDBMS PRG INTERFACE PRODID-5 PORTID-215 4402255

    *** 10/12/06 06:48 pm ***

    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 32bit for Windows Server 2003.

    – The above DML is issued from SID kojizaimu to SID wf.

    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

    – ERRORSTACK of the remote during the hang is as following.

    WARNING: Stack unwind information not available. Following frames may be

    – 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.

    Not found.

    Bug 4402255

    This is reproducible at the ct’s site. It reproces
    most of the time when they run UPDATE from a client application.


    I will upload “stacktrace.tar.gz” includes below files with README.TXT


    * 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)

    * Remote Site (wf)

    – 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

  3. admin says

    Applies to:
    Oracle Net Services
    Information in this document applies to any platform.

    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.

  4. admin says

    Applies to:
    Oracle Net Services – Version: to – Release: 9.2 to 11.2
    Information in this document applies to any platform.
    Checked for relevance on 18-JUN-2010
    How to enable Oracle Net tracing for database links.

    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 :

    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 - 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 - 64bit Production With the Partitioning and Data Mining options SQL> select sysdate from dual@test;



    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 $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.

  5. Taylor says





沪公网安备 31010802001379号