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--------------------------------------------------------------------10023303 DBeaver 21?3?4 ? Main 10160430 DBeaver 21?3?4 ? Metadata 10239527 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='cawawc5gm6fvf'INSERT INTO t_test VALUES (4)END OF STMTPARSE #140369239891672:c=21548,e=41077,p=4,cr=64,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=895423229WAIT #140369239891672: nam='db file sequential read' ela= 6445 file#=7 block#=345 blocks=1 obj#=73270 tim=895429801WAIT #140369239891672: nam='db file sequential read' ela= 1780 file#=7 block#=344 blocks=1 obj#=73270 tim=895431670WAIT #140369239891672: nam='db file sequential read' ela= 1261 file#=7 block#=349 blocks=1 obj#=73270 tim=895433014WAIT #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 STMTPARSE #140369239223184:c=415,e=415,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=3,plh=0,tim=895435150EXEC #140369239223184:c=0,e=639,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=3,plh=1027684349,tim=895435844WAIT #140369239223184: nam='db file sequential read' ela= 10 file#=1 block#=497 blocks=1 obj#=56 tim=895435901WAIT #140369239223184: nam='db file sequential read' ela= 5 file#=1 block#=22770 blocks=1 obj#=56 tim=895435932WAIT #140369239223184: nam='db file sequential read' ela= 5 file#=1 block#=465 blocks=1 obj#=52 tim=895435964WAIT #140369239223184: nam='db file sequential read' ela= 3747 file#=1 block#=21038 blocks=1 obj#=52 tim=895439727WAIT #140369239223184: nam='db file sequential read' ela= 2060 file#=1 block#=95691 blocks=1 obj#=28 tim=895441875FETCH #140369239223184:c=1442,e=6101,p=5,cr=8,cu=0,mis=0,r=1,dep=1,og=3,plh=1027684349,tim=895441973STAT #140369239223184 id=1 cnt=1 pid=0 pos=1 obj=0 op='NESTED LOOPS (cr=8pr=5pw=0str=1time=6101 us)'STAT #140369239223184 id=2 cnt=1 pid=1 pos=1 obj=0 op='NESTED LOOPS (cr=6pr=5pw=0str=1time=6083 us)'STAT #140369239223184 id=3 cnt=1 pid=2 pos=1 obj=31 op='TABLE ACCESS BY INDEX ROWID CDEF$ (cr=3pr=2pw=0str=1time=78us)'STAT #140369239223184 id=4 cnt=1 pid=3 pos=1 obj=56 op='INDEX RANGE SCAN I_CDEF4 (cr=2pr=2pw=0str=1time=72 us)'STAT #140369239223184 id=5 cnt=1 pid=2 pos=2 obj=28 op='TABLE ACCESS BY INDEX ROWID CON$ (cr=3pr=3pw=0str=1time=6004 us)'STAT #140369239223184 id=6 cnt=1 pid=5 pos=1 obj=52 op='INDEX UNIQUE SCAN I_CON2 (cr=2pr=2pw=0str=1time=3846 us)'STAT #140369239223184 id=7 cnt=1 pid=1 pos=2 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=2pr=0pw=0str=1time=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=895442200EXEC #140369239891672:c=6962,e=19094,p=9,cr=9,cu=11,mis=0,r=0,dep=0,og=1,plh=0,tim=895442361ERROR #140369239891672:err=1 tim=895442373STAT #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=895442448WAIT #140369239891672: nam='SQL*Net break/reset to client' ela= 2850 driver id=1413697536 break?=0 p3=0 obj#=73271 tim=895445311WAIT #140369239891672: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=73271 tim=895445495WAIT #140369239891672: nam='SQL*Net message from client' ela= 66897 driver id=1413697536 #bytes=1 p3=0 obj#=73271 tim=895512645CLOSE #140369239891672:c=7,e=7,dep=0,type=0,tim=895512725
可以看到在语句的执行时遇到了SQL*Net break/reset to client事件。
