RE: Excessive SQL*Net message from client waits

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.

So, what I end up with are 10 separate trace files...one for each session connect period. Doesn't each trace file then only show that specific session's info and big spikes in SQL*Net message waits shouldn't "carry over".

I'll certainly try your idea about using netstat while tracing and see what I find.

I feel as if I'm being thick-headed about this but I do not see this same behavior at every installation. These high SQL*Net message waits are showing up only at this one client site. Other pratically identical sites do not see this behavior. By practically identical I mean that other comparable sites have different network config. This particular site has it's database server 100 miles away from the users running the client application. WAN vs LAN. Just wish I could find a "good reason" why it's so different.....

Thanks so much,
Karen Morton

-----Original Message-----
Lewis
Sent: Thursday, March 13, 2003 12:19 PM
To: Multiple recipients of list ORACLE-L

I'd start by being doubtful about anybody being able to work so fast that the can avoid a high percentage of time in 'sql*net from client' - in fact, it the percentage was low (when the client was a person at a terminal) I would write myself a memo to check whether the client code was executing an extreme number of very small statements behind the scenes (e.g. get all keys for a drop-down, then get each drop down by key one at a time every time the user hit the field).

It's always possible that the many layers of code at the client end are taking a surprising

However, assuming you have a truly
unreasonable loss of time on waiting for client - I would try and isolate the problem by using netstat and top. This can be hard in typical environments, though.

Start up the client session -

Start netstat running on the server with minimum snapshot time (usually one
second).

Start top (or similar) running in real time or minimum snapshot time.

Start up event 10046 at the session.

Then get the client to do something,
and watch for:

1. the peak in netstat as the request reaches the server.
2. the burst from the server as the request is serviced
3. the peak in netstat as the reply gets sent
4. the delay before it appears on the client screen.

It's crude, but simple-minded, and if the client is causing the problem it may prove it quite convincingly.

Back it up with the trace file - which will record timestamps of a query coming in and results going out.

The biggest problem, usually, is that it simply isn't realistic to get a system so quiet that you can get just one client
running all by itself with nothing else going on.

In your particular case, I have to sya that I have noticed that Java can use a surprising amount of CPU sometimes.

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

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: 13 March 2003 15:54

> Good point, but what if each user only has a single session?
>
> Not that I've noticed this exact same situation here on one of our
> Engineering support databases whose clients are Java, and I'm not
wondering
> if it has something to do with the application or if I can possibly
speed it
> up with tweaks to SDU/TDU. I'm just wondering... ;)
>

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


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Karen Morton
  INET: oracledba_at_morton-consulting.com

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