1  SQL*Net break/reset to client介绍

      SQL*Net break/reset to client从字面上来看，好像与网络有关，但是，如果查一下官方文档，就会发现这个事件基本上和网络没有什么关系，官方文档对这个事件的描述如下：

SQL*Net break/reset to client (%)
Description
  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. 

       可以看到，导致这个事件的原因有5个，两个与游标有关，一个时查询一个已经关闭的游标，一个是查询时游标没有数据返回。另三个原因与会话运行的sql有关，当会话查询了不存在的表，向唯一索引的表内插入重复值或者执行的sql有无效的语法时也会导致数据库服务器充值或者断开与会话的连接。

       SQL*Net break/reset to client事件一般历时较短，不容易排查，常常在AWR报告里可以看到这个事件大量发生，在ash报告里却找不到这个事件的详细信息。官网上也说，如果需要准确定位导致这个事件的原因或者是sql语句，往往需要对会话进行跟踪。

2  跟踪验证SQL*Net break/reset to client事件

    导致SQL*Net break/reset to client的5个原因中，比较容易复现和验证的是涉及sql语句的三个原因，这里演示一下由于重复值而引起的这个事件。由于SQL*Net break/reset to client事件一般历时较短，在v$active_session_history视图中往往捕获不到，这个通过对会话进行跟踪来验证这个事件。打开Dbeaver连接到数据库，查询会话信息，发现有三个Dbeaver相关事件。

SQL> /

       SID    SERIAL# PROGRAM
---------- ---------- ------------------------------------------------
       100      23303 DBeaver 21?3?4 ? Main
       101      60430 DBeaver 21?3?4 ? Metadata
       102      39527 DBeaver 21?3?4 ? SQLEditor ?Script?sql?

      对SQLEditor的会话进行跟踪，使用dbms_monitor包

 exec dbms_monitor.session_trace_enable(session_id=>102,serial_num=>39527,waits=>true)

在dbms中运行一条insert语句

这条语句的执行报了违反唯一约束条件的错误，查询一下被跟踪会话的trace文件，里面包含下面的信息

 =====================
PARSING IN CURSOR #140369239891672 len=29 dep=0 uid=106 oct=2 lid=106 tim=895423229 hv=1597193070 ad='7923cad8' sqlid='c
awawc5gm6fvf'
INSERT INTO t_test VALUES (4)
END OF STMT
PARSE #140369239891672:c=21548,e=41077,p=4,cr=64,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=895423229
WAIT #140369239891672: nam='db file sequential read' ela= 6445 file#=7 block#=345 blocks=1 obj#=73270 tim=895429801
WAIT #140369239891672: nam='db file sequential read' ela= 1780 file#=7 block#=344 blocks=1 obj#=73270 tim=895431670
WAIT #140369239891672: nam='db file sequential read' ela= 1261 file#=7 block#=349 blocks=1 obj#=73270 tim=895433014
WAIT #140369239891672: nam='db file sequential read' ela= 1531 file#=7 block#=355 blocks=1 obj#=73271 tim=895434676
=====================
PARSING IN CURSOR #140369239223184 len=131 dep=1 uid=0 oct=3 lid=0 tim=895435151 hv=1635361899 ad='79211ff8' sqlid='2jfq
zrxhrm93b'
select /*+ rule */ c.name, u.name from con$ c, cdef$ cd, user$ u  where c.con# = cd.con# and cd.enabled = :1 and c.owner
# = u.user#
END OF STMT
PARSE #140369239223184:c=415,e=415,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=3,plh=0,tim=895435150
EXEC #140369239223184:c=0,e=639,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=3,plh=1027684349,tim=895435844
WAIT #140369239223184: nam='db file sequential read' ela= 10 file#=1 block#=497 blocks=1 obj#=56 tim=895435901
WAIT #140369239223184: nam='db file sequential read' ela= 5 file#=1 block#=22770 blocks=1 obj#=56 tim=895435932
WAIT #140369239223184: nam='db file sequential read' ela= 5 file#=1 block#=465 blocks=1 obj#=52 tim=895435964
WAIT #140369239223184: nam='db file sequential read' ela= 3747 file#=1 block#=21038 blocks=1 obj#=52 tim=895439727
WAIT #140369239223184: nam='db file sequential read' ela= 2060 file#=1 block#=95691 blocks=1 obj#=28 tim=895441875
FETCH #140369239223184:c=1442,e=6101,p=5,cr=8,cu=0,mis=0,r=1,dep=1,og=3,plh=1027684349,tim=895441973
STAT #140369239223184 id=1 cnt=1 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=8 pr=5 pw=0 str=1 time=6101 us)'
STAT #140369239223184 id=2 cnt=1 pid=1 pos=1 obj=0 op='NESTED LOOPS  (cr=6 pr=5 pw=0 str=1 time=6083 us)'
STAT #140369239223184 id=3 cnt=1 pid=2 pos=1 obj=31 op='TABLE ACCESS BY INDEX ROWID CDEF$ (cr=3 pr=2 pw=0 str=1 time=78
us)'
STAT #140369239223184 id=4 cnt=1 pid=3 pos=1 obj=56 op='INDEX RANGE SCAN I_CDEF4 (cr=2 pr=2 pw=0 str=1 time=72 us)'
STAT #140369239223184 id=5 cnt=1 pid=2 pos=2 obj=28 op='TABLE ACCESS BY INDEX ROWID CON$ (cr=3 pr=3 pw=0 str=1 time=6004
 us)'
STAT #140369239223184 id=6 cnt=1 pid=5 pos=1 obj=52 op='INDEX UNIQUE SCAN I_CON2 (cr=2 pr=2 pw=0 str=1 time=3846 us)'
STAT #140369239223184 id=7 cnt=1 pid=1 pos=2 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=2 pr=0 pw=0 str=1 time=17 us)'
STAT #140369239223184 id=8 cnt=1 pid=7 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 str=1 time=9 us)'
CLOSE #140369239223184:c=194,e=194,dep=1,type=0,tim=895442200
EXEC #140369239891672:c=6962,e=19094,p=9,cr=9,cu=11,mis=0,r=0,dep=0,og=1,plh=0,tim=895442361
ERROR #140369239891672:err=1 tim=895442373
STAT #140369239891672 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  T_TEST (cr=0 pr=0 pw=0 str=1 time=6 us)'
WAIT #140369239891672: nam='SQL*Net break/reset to client' ela= 6 driver id=1413697536 break?=1 p3=0 obj#=73271 tim=8954
42448
WAIT #140369239891672: nam='SQL*Net break/reset to client' ela= 2850 driver id=1413697536 break?=0 p3=0 obj#=73271 tim=8
95445311
WAIT #140369239891672: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=73271 tim=89544549
5
WAIT #140369239891672: nam='SQL*Net message from client' ela= 66897 driver id=1413697536 #bytes=1 p3=0 obj#=73271 tim=89
5512645
CLOSE #140369239891672:c=7,e=7,dep=0,type=0,tim=895512725

    可以看到在语句的执行时遇到了SQL*Net break/reset to client事件。