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

PlumRain

2020-03-09

某客户通过监控发现某一套Oracle DB在一个时间段内的，异常指标！需要进行分析

使用如下SQL进行诊断，发现TOP EVENT 是SQL*Net break/reset to clientSQL>select instance_number,event,count(*) from dba_hist_active_sess_historywhere SAMPLE_TIME between to_date(‘2020-02-25 06‘,‘yyyy-mm-dd hh24‘) and  to_date(‘2020-02-25 09‘,‘yyyy-mm-dd hh24‘) and having(count(*))>200 group by instance_number,event order by 2;

查询11g官方文档获取该等待事件解释说明，基本上与应用执行的程序=SQL有关系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 replyfrom 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 可以查询到执行SQL的用户ID=DBA_USER USER_ID=>USERNAME   以及SQL_idSQL>select event,USER_ID,sql_id,count(*) from dba_hist_active_sess_history where SAMPLE_TIME between to_date(‘2020-02-25 06‘,‘yyyy-mm-dd hh24‘) and  to_date(‘2020-02-25 09‘,‘yyyy-mm-dd hh24‘) and instance_number=1  and event=‘SQL*Net break/reset to client‘ group by USER_ID,event,sql_id order by 2;数据库并未找到SQL文本，但是该库存在监控，通过监控输入SQL_ID找到SQL_TEXT 发现是查询视图！由于本次问题，开发人员自己处理了，因此并未造成影响。开发也并未反馈是什么原因。如下进行测试模拟参考文档！https://tanelpoder.com/2008/04/10/sqlnet-breakreset-to-client/

如下是测试内容SQL> select event, total_waits from v$session_event where event like ‘%reset%‘ and sid = (select sid from v$mystat where rownum = 1);
SQL> begin
     execute immediate ‘drop table non_existent‘;
  exception
     when others then null;
  end;
  /
SQL> select event, total_waits from v$session_event where event like ‘%reset%‘ and sid = (select sid from v$mystat where rownum = 1);
null上述可以发现，当查询一个不存在的表，但是PLSQL存在异常处理部分，并不会出现本次测试的等待事件！
SQL> begin
     execute immediate ‘drop table non_existent‘;
  end;
  /
SQL> select event, total_waits from v$session_event where event like ‘%reset%‘ and sid = (select sid from v$mystat where rownum = 1);
EVENT                          TOTAL_WAITS
------------------------------ -----------
SQL*Net break/reset to client            2
上述可以发现，当查询一个不存在的表，但是PLSQL不存在异常处理部分，该等待事件出现！查询一个不存在的表！
SQL> select * from non_existent;
select * from non_existent
              *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select event, total_waits from v$session_event where event like ‘%reset%‘ and sid = (select sid from v$mystat where rownum = 1);
EVENT                          TOTAL_WAITS
------------------------------ -----------
SQL*Net break/reset to client            4

创建一个表，后置查询一个不存在的表！
SQL> create table a as select * from employees;
create table a as select * from employees
                                *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select event, total_waits from v$session_event where event like ‘%reset%‘ and sid = (select sid from v$mystat where rownum = 1);
EVENT                          TOTAL_WAITS
------------------------------ -----------
SQL*Net break/reset to client            6


查询一个视图，视图对应的表被删除
SQL> create table a as select * from hr.employees;
SQL> create view a_t as select EMPLOYEE_ID,FIRST_NAME from a;
SQL> drop table a;
SQL> select event, total_waits from v$session_event where event like ‘%reset%‘ and sid = (select sid from v$mystat where rownum = 1);
EVENT                          TOTAL_WAITS
------------------------------ -----------
SQL*Net break/reset to client            6

SQL> select * from a_t;
select * from a_t
              *
ERROR at line 1:
ORA-04063: view "SYS.A_T" has errors
SQL> select event, total_waits from v$session_event where event like ‘%reset%‘ and sid = (select sid from v$mystat where rownum = 1);

EVENT                          TOTAL_WAITS
------------------------------ -----------
SQL*Net break/reset to client            8

reset event sql数据库