SQL*Net break/reset to client

By Brian Fitzgerald

Introduction

This is a demonstration of event SQL*Net break/reset to client. This demo will assist understanding the origin of SQL*Net break/reset to client events on your database. The application details leading to SQL*Net break/reset to client may not be directly accessible to the DBA.

A session waits on SQL*Net break/reset to client when an error happens and the Oracle instance is trying to notify the client. Until the client acknowledges, the session waits on event SQL*Net break/reset to client. SQL*Net break/reset to client waits appear in the OEM display under the Application wait class.

To simulate the scenario, I run a sqlplus script that will sleep and eventually get an error. Before the error is reached, I suspend the client, so that it cannot receive any message from the instance. As a result, the session waits on SQL*Net break/reset to client.

Event SQL*Net break/reset to client is similar to SQL*Net message to client and SQL*Net more data to client in the sense that that Oracle has some information to communicate to the client. The waiting will persist if the client is preoccupied. Whereas “break/reset” is in the Application wait class, “message” and “more data” waits are in the Network wait class.

In summary, SQL*Net break/reset to client means that an error occurred and Oracle is trying to notify the client.

Demo

For this demonstration, I will open three windows:

  1. Run a PL/SQL script
  2. Suspend the batch, sleep, and resume the batch
  3. Check the event in v$session

Script

Here is the script used for the demo.

$ cat sqlnet.break.reset.sql
@ conn.pdba.u.sql
set verify off

declare
 l_rslt number;
begin
 dbms_lock.sleep(&&1);
 l_rslt := 0/0;
end;
/
quit

Oracle will sleep, reach division by zero, and try to notify the client about the error.

Window 1:

$ sqlplus /nolog @ sqlnet.break.reset.sql 20 & fg
[1] 30878
sqlplus /nolog @ sqlnet.break.reset.sql 20

SQL*Plus: Release 12.2.0.1.0 Production on Sat Nov 4 16:23:32 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected.

Note that the process id is 30878.

Window 2:

Here we want to simulate an client process that should be waiting for the oracle server, but is preoccupied with something else. We do that by suspending sqlplus for 120 seconds.

$ kill -STOP 30878 ; sleep 120 ; kill -CONT 30878

Window 1:

sqlplus responds

[1]+ Stopped sqlplus /nolog @ sqlnet.break.reset.sql 20

Window 3:

In fact, Oracle has tried to notify the application (sqlplus), and is waiting on a response that will not come until the client wakes up. I.e. the session is waiting on SQL*Net break/reset to client.

SQL> select sid, sql_id, event, p2, p2text 
from v$session 
where service_name = 'u' 
and sid != sys_context('userenv','sid');

 SID SQL_ID        EVENT                           P2 P2TEXT
---- ------------- ------------------------------ --- ------
 63                SQL*Net break/reset to client    0 break?

The text for p2, in this case, is “break?”. Nonzero means break and 0 means reset, so this case is a reset, which is the more common case.

sql_id is null. The session is a running statement not now, but in the past. An error occurred, and Oracle is trying to notify the client. In most cases of SQL*Net break/reset to client, sql_id will be null.

At this point, I have staged a scenario with SQL*Net break/reset to client.

Window 1:

After 120 seconds, window 2 issues kill -CONT 30878. sqlplus wakes up. The output appears:

$ declare
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 5

Disconnected from Oracle Database 12c Enterprise Edition 
Release 12.2.0.1.0 - 64bit Production

Monitoring

Event SQL*Net break/reset to client appears in OEM in the Application class. Refer to the bright red peak, at right.

active

The bright red region is a hyperlink. You can drill in to the Application class by clicking on it. The display shows the wait breakdown, which shows one session waiting on SQL*Net break/reset to client.

appl

Notice that the Top SQL section is empty. No statement is active. The session appears as a hyperlink, so you can drill down by clicking on Session ID 49.

ses

Impact

One session waiting on SQL*Net break/reset to client will probably have no adverse impact on the instance, or on other sessions. If the session is in a critical batch, then resolving this wait issue will be a high priority. In some cases, SQL*Net break/reset to client appears as a result of manual operation of various tools.

The Application class

We often associate the Application class with blocking, such as the row-level locking that leads to, enq: TX – row lock contention, or the enq: TM – contention waits associated with select for update or missing foreign key indexes. Those waits are associated with statements, and with tables or indexes. SQL*Net break/reset to client, although it is in the Application class, is not associated with a SQL statement or with locking. However, the name “Application” class is a clue that resolving this wait will require coordination between the DBA and others who develop, manage, or use the application.

Root Causes in Production

In a serious production application, waits on SQL*Net break/reset to client could be a symptom of an application design issue. It could be that the application launches a query and goes off down some other code path while the query is running. When an error appears in the Oracle session, the application is not attending to it right away. In other words, there is a lack of coordination between the client and the Oracle instance. The reason for this could reside in the application design and the database driver, including version and patch status.

Note also that the name SQL*Net break/reset to client does not necessarily mean that there is a network problem. As you can see, resolution of this issue did not require assistance from the network support team, or the operating system administrator.

When investigating issues such as this one, it is helpful to talk to other people who know about other aspects of the application, and engage in a sort of “brainstorming session”.

If you understand the SQL*Net break/reset to client event, you might not be able to resolve it on your own, but you will have an opportunity to lead an investigation that will eventually yield a solution.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s