Re: "sql * net wait event"

  • From: Cary Millsap <cary.millsap@xxxxxxxxxxxx>
  • To: dba.vanitasharma@xxxxxxxxx
  • Date: Wed, 15 Oct 2014 16:40:49 -0500

Vanita,

If you have access to Jeff Holt's and my *Optimizing Oracle Performance
<http://amzn.to/OM0q75>* (O'Reilly 2003), then pp311–315 is a good place to
begin. Note, however, that the pseudocode at the top of p313 is wrong for
'SQL*Net message to client', for reasons that are explained on pp75–76 of *The
Method R Guide to Mastering Oracle Trace Data <http://amzn.to/173bpzg>*.


Cary Millsap
Method R Corporation
Author of *Optimizing Oracle Performance <http://amzn.to/OM0q75>* and *The
Method R Guide to Mastering Oracle Trace Data <http://amzn.to/173bpzg>*


On Tue, Oct 14, 2014 at 2:31 PM, Mark W. Farnham <mwf@xxxxxxxx> wrote:

> +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: