等待事件诊断分析：SQL*Net Break/reset To Client Waits

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.2 and later
Information in this document applies to any platform.

SYMPTOMS

Getting significant application contention on an Update statement which is getting the “SQL*Net break/reset to client” wait.

This causes significant impact when 100 of these sessions are getting this wait. Also, it seems that very few records are actually updated and in some cases none.

This is impacting the overall performance on a production DB server.

CAUSE

SQL*Net break/reset to client represent some error in the SQL statement submitted by the application ,

SQL*Net break/reset happens when an error/unhandled exception is raised during a call (which means that the call executed didn’t complete normally, thus the call state must be reset).

10046 Trace from the Statement/session that is getting the “SQL*Net break/reset to client” wait , shows the following :

EXEC #4577284448:c=0,e=904,p=0,cr=8,cu=10,mis=0,r=0,dep=0,og=1,plh=0,tim=49240680822112
ERROR #3:err=1 tim=49240680822126
WAIT #4577284448: nam='SQL*Net break/reset to client' ela= 4 driver id=1952673792 break?=1 p3=0 obj#=-1 tim=49240680822228
WAIT #4577284448: nam='SQL*Net break/reset to client' ela= 24544 driver id=1952673792 break?=0 p3=0 obj#=-1 tim=49240680846790
WAIT #4577284448: nam='SQL*Net message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=49240680846831
WAIT #4577284448: nam='SQL*Net message from client' ela= 24571 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=49240680871431
复制

which is ORA-00001: unique constraint violated in this case

SOLUTION

1- Enable 10046 Trace to the Statement/session that is getting the “SQL*Net break/reset to client” ,

Please review the following note, for different ways of enabling the 10046 trace event after the session is created :

Note 376442.1 - Recommended Method for Obtaining 10046 trace for Tuning

Then search for keyword ‘err=’

The Number will represent the Oracle error number occurred , ORA-n

You should also be able to Identify the Statement caused this Error and bind variables used , to check and Fix these errors :

=====================
PARSING IN CURSOR #4577284448 len=158 dep=0 uid=236 oct=6 lid=236 tim=49240680785729 hv=2510433232 ad='7000000181663b8' sqlid='1bukxaqau4ayh'
UPDATE IPS_MAC_TO_IP
SET fqdn = null, interface = :p1, last_upd = SYSDATE
WHERE mac = :p2 AND ip = INET_ATON(:p3) AND router_found_loopback = 170543874
END OF STMT
BINDS #4577575528:
 Bind#0
  oacdty=01 mxl=128(72) mxlc=00 mal=00 scl=00 pre=00
  oacflg=05 fl2=1000000 frm=01 csi=873 siz=384 off=0
  kxsbbbfp=110d7c058  bln=128  avl=07  flg=05
  value="Vlan101"
 Bind#1
  oacdty=01 mxl=128(48) mxlc=00 mal=00 scl=00 pre=00
  oacflg=05 fl2=1000000 frm=01 csi=873 siz=0 off=128
  kxsbbbfp=110d7c0d8  bln=128  avl=12  flg=01
  value="005056913a4a"
 Bind#2
  oacdty=01 mxl=128(48) mxlc=00 mal=00 scl=00 pre=00
  oacflg=05 fl2=1000000 frm=01 csi=873 siz=0 off=256
  kxsbbbfp=110d7c158  bln=128  avl=14  flg=01
  value="169.173.200.74"
....
EXEC #4577284448 :c=0,e=904,p=0,cr=8,cu=10,mis=0,r=0,dep=0,og=1,plh=0,tim=49240680822112
ERROR #3:err=1 tim=49240680822126
WAIT #4577284448: nam='SQL*Net break/reset to client' ela= 4 driver id=1952673792 break?=1 p3=0 obj#=-1 tim=49240680822228
复制

Also Please engage the application team/vendor to write the proper exception handling appropriately (ex: OTHERS exception) in the application code .

or

2- If no errors is reported in the 10046 Trace , Please put the following parameters in sqlnet.ora file for both client and server side as a workaround :

BREAK_POLL_SKIP=10000
DISABLE_OOB=on

And then retry the scenario .