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
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@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
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@xxxxxxxxx>
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@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.
---
Vanita
Other related posts:
