关于 SQLNET break/reset to client等待事件

APPLIES TO:

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 :

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 :

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 .

APPLIES TO:

SYMPTOMS

 A high number of "SQL*Net break/reset to client" wait events are observed in a production environment.

After performing some tracing using Client Identifiers, the waits are almost all related Advanced Queue (AQ) dequeue calls

In particular these relate to ORA-25228 errors. The ORA-25228 just indicates that there are no more messages in the queue.

CAUSE

The behaviour observed is working as designed.
 
There is an Message Driven Bean (MDB) timeout setting using within the Application Server which is consuming messages from the queues with this database which has a timeout of 30 seconds.

Every 30 seconds the application server will try and consume messages regardless of messages being in the queue or not. 

This is what you observe in the 10046 trace that there is a 30 second gap between each a session waiting for a message and a SQL*Net break/reset to client wait event being reported as per the following example

*** [ Unix process pid: 15242 ]
*** 2014-01-20 13:09:32.610

*** [ Unix process pid: 15242 ]
*** 2014-01-20 13:09:42.613
WAIT #18446744071527990480: nam='Streams AQ: waiting for messages in the queue' ela= 10000123 queue id=264667 process#=30036252528 wait time=30 obj#=-1 tim=16595326291167

** [ Unix process pid: 15242 ]
*** 2014-01-20 13:10:02.615
WAIT #18446744071527990480: nam='Streams AQ: waiting for messages in the queue' ela= 10000137 queue id=264667 process#=30036252528 wait time=30 obj#=-1 tim=16595346291805
EXEC #18446744071527955576:c=0,e=93,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1987110454,tim=16595346292190
FETCH #18446744071527955576:c=0,e=19,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=1,plh=1987110454,tim=16595346292265
EXEC #18446744071527990480:c=10000,e=30002692,p=0,cr=3,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=16595346292568
ERROR #4:err=25228 tim=16595346292615
WAIT #18446744071527990480: nam='SQL*Net break/reset to client' ela= 3 driver id=1952673792 break?=1 p3=0 obj#=-1 tim=16595346292744
WAIT #18446744071527990480: nam='SQL*Net break/reset to client' ela= 628 driver id=1952673792 break?=0 p3=0 obj#=-1 tim=16595346293417
WAIT #18446744071527990480: nam='SQL*Net message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=16595346293486
WAIT #18446744071527990480: nam='SQL*Net message from client' ela= 1000 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=16595346294567
CLOSE #18446744071527990480:c=0,e=34,dep=0,type=3,tim=16595346294691
WAIT #18446744071527955576: nam='SQL*Net message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=16595346294944
WAIT #18446744071527955576: nam='SQL*Net message from client' ela= 786 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=16595346295781
XCTEND rlbk=0, rd_only=1, tim=16595346295871
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=16595346295970
 

SOLUTION

The only way to reduce the 'SQL*Net break/reset to client' wait events would be to set the MDB timeouts to a higher value than 30 seconds.