Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Excessive SQL*Net message from client waits

Re: Excessive SQL*Net message from client waits

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 13 Mar 2003 13:44:00 -0800
Message-ID: <F001.00569933.20030313134400@fatcity.com>

I am not suggesting that sessions are waiting for each other, or reporting each others' wait times. I am simply assuming that if the application design was daft enough to spawn multiple sessions, it probably was clever enough to have the parallel, independent threads of execution making all those sessions work concurrently at the client.

Consequently, if the client 'spawns a session which then connects, does something and
disconnects', I am assuming that the initial client is waiting for that session to complete and so (from Oracle's perspective) the initial client's session is waiting on (its own, and no-one else's) "SQL*Net message from client".

So if the initial client has spawned 9 other sessions, I would (perhaps unfairly)
assume that at any one instant only one
of them is actually doing anything - which is why on average I would not be surprised to see a 90% SQL*Net wait.

Moving away from the SQL*Net bit though, my impression of the other stats was that the user could quite possibly see a significant turn around time between hitting a key and seeing a response. Given a limited number of messages (2,750 I think it was) to and from the client, the volume of direct reads and writes was high, and the number of
log file sync waits was very high - with a surprising max wait on log file sync.

The application seems to be committing
over-enthusiastically - which stresses the log writer and log buffer latching anyway - but there is also a lot of stress on the I/O system from (probably) sorts or
hash joins. Perhaps this site has a
different data distribution, or set of
indexes, that is making some execution
paths very expensive, and bring into
sharp relief an underlying problem
with commit rates.
.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now available One-day tutorials:
  Cost Based Optimisation
  Trouble-shooting and Tuning
  Indexing Strategies

(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______April 8th
____UK_______April 22nd

____Denmark May 21-23rd

____USA_(FL)_May 2nd

Next dates for the 3-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____UK_(Manchester)_May
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

> Jonathon et al, is it really true that every session is waiting on
the
> others if as each session is spawned, it does its thing (i.e. issues
some
> set of queries) and then disconnects? There are never two sessions
doing
> something simultaneously really. The user logs in and only sees and
works
> with one screen at a time. A session is spawned to do some
initialization
> stuff...this one sticks around and may see a bit more activity
before the
> logout...so I can see how this one would have the waits. But the
other
> spawned sessions connect, do something and disconnect. These
spawned
> sessions come from various controls on the screen...not different
app
> occurrences or windows within the app.
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Mar 13 2003 - 15:44:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US