RE: "sql * net wait event"

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <kylelf@xxxxxxxxx>, <dba.vanitasharma@xxxxxxxxx>
  • Date: Tue, 14 Oct 2014 15:31:05 -0400

+1 on the article kyle.


There is also a way to strategically reduce the need for line turn-arounds as a 
result of multiple statements issued from a client to the database.


Whenever sets of statements form a logical unit of work that is always 
transacted together, if you can bundle and store those statements in the 
database as a pl/sql procedure and invoke the procedure with the appropriate 
parameters instead of sending the statements individually to be executed, you 
reduce the sql wait pairs by the one less than the number of statements. As a 
percentage of elapsed time this eliminates this is most effective when the 
subject of the statements is relatively small numbers of rows (as often happens 
in on line transaction systems where it might be a one item order affecting one 
bank account, one inventory item reservation, one this, one that, and one other 


Often folks who construct sql dialogs from a java (or similar) client tool 
think that rules out using a stored procedure, but an examination of the logic 
of the client application (and/or usage patterns) often reveals a small number 
of logical units of work that usually result. Those can be matched to an 
identified procedure as appropriate and a parameterized call can be substituted 
for the line by line submission otherwise done.


Even in the case of a one statement logical unit of work the sheer bulk 
reduction of the call rather than shipping the sql text may be significant, and 
for dml rather than read only queries there is the commit or rollback to 


When the bulk of data being returned from a read only query is the issue the 
answer is not so clear: If it is a pagination report that may be interactively 
terminated that probably mitigates in favor of sqlnet wait line turn-arounds 
(which are not really an annoyance to the user in that case because it IS think 
time). When the full report is always delivered (and especially if the report 
is large) dumping it into a server or close to the server network location and 
returning only something like a retrieval url is often superior. (Also remember 
that a read query that contains a select for update is not really a read only 
query and the page at a time approach persists that lock.)




From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of kyle Hailey
Sent: Tuesday, October 14, 2014 2:24 PM
To: dba.vanitasharma@xxxxxxxxx
Cc: Oracle L
Subject: Re: "sql * net wait event"



here is a start



- Kyle Hailey




On Tue, Oct 14, 2014 at 10:34 AM, vanita sharma <dba.vanitasharma@xxxxxxxxx> 


Below the wait event  


sql * net message to  client

sql * net message from   client

sql*net more data from client






On 14 October 2014 19:44, Paul Drake <bdbafh@xxxxxxxxx> wrote:

Best way to reduce idle wait events?
Block access to the public Internet and social media apps.

On Oct 14, 2014 9:13 AM, "vanita sharma" <dba.vanitasharma@xxxxxxxxx> wrote:

Hi Experts , 


I  am seeing  high number of  "sql * net wait event"  in my database . 

What is best the way to diagnoses/reduce  such wait events.  






Other related posts: