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等待事件。


Posted

in

by

Tags:

Comments

5 responses to “SQL*Net break/reset to client等待事件”

  1. admin Avatar
    admin

    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.

  2. admin Avatar
    admin

    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

  3. admin Avatar
    admin

    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.

  4. admin Avatar
    admin

    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 :
    TRACE_LEVEL_SERVER = 16
    TRACE_DIRECTORY_SERVER = eg $ORACLE_HOME/network/trace
    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.

  5. Taylor Avatar
    Taylor

    hi,你好,我在statspack里面也发生了这个事件,而且比例很大,我想知道如何进一步查看trace文件,去确定发生问题的具体位置

Leave a Reply

Your email address will not be published. Required fields are marked *