RE: "sql * net wait event"

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 thing.)  

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 consider.  

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.)  

mwf  

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

here is a start  

http://www.oraclerealworld.com/oracle-sqlnet-wait-events/    

• Kyle Hailey

http://kylehailey.com      

On Tue, Oct 14, 2014 at 10:34 AM, vanita sharma <dba.vanitasharma_at_gmail.com> wrote:  

Below the wait event  

sql * net message to client

sql * net message from client

sql*net more data from client    

---

Vanita  

On 14 October 2014 19:44, Paul Drake <bdbafh_at_gmail.com> 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_at_gmail.com> 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.  

---

Vanita    

--
http://www.freelists.org/webpage/oracle-l