RE: SQL*Net message waits

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Wed, 17 Mar 2004 09:41:27 +0000
Message-Id: <s0581d6a.048@bristol21.bristol.ac>

First up we have been having some email issues, for example I only got Mogens post on books after all the replies, so apologies if this has been said before. Comments in-line.

> Hi Mark,
>
> I've asked the users to let me know which processes need to
> be evaluated
> but they haven't been able to tell me thus far. Application
> speed is an
> issue with some users, but not others. The only SLA I've
> been given is
> 99.98 uptime on a very buggy application (sigh).

Horrible position to be in, I bet that you can satisfy the SLA and still have unsatisified users. I guess until folk work this out we will be stuck with this sort of issue.

>
> I normally use 10046 traces on individual, longer running jobs and I
> know this isn't the technically correct way to do this, but the only
> question I'm being asked at the moment is 'Is there a communication
> problem between the app server and the db?'

> * These numbers are from pooled connection traces and there is lots of
> user think time that would be included. That's my real
> question: can I use
> a 10046 trace to evaluate pooled client connections? Since
> the database
> appears to be waiting on a client input, can I ignore the
> 'from client'
> waits or could they be hiding other problems?

I believe there are two answers to this. The most accurate one is No, at least not very reliably, the not so real one is - ah but if you just upgrade to 10g then you can. In order for the trace to be reliable you need to identify the client session across multiple trace files. Now there are techniques to do this but it involves a lot of stitching edited files together in the right order. I think you'd be very brave to guarantee you got it right. So there are 2 alternatives

setup a dedicated connection that doesn't use connection pooling and trace that.
use a single trace file as a proxy for an average session assuming that all sessions are equal.

I tend to go with 2 to start with and stick with it unless it becomes obviously wrong. You do have to recognize that you are only approximating (badly sometimes) the end users experience. Option 1 takes some setup and could also mislead, especially if the connection pooling itself is the issue.

Cary or Jeff can no doubt chime in if the HOTSOS profiler will do the stitching together etc for you.

> If there's a better way to monitor for connection problems
> between an app
> server and the database, please let me know ...

Sure, get the vendor to instrument their code :(

Niall Litchfield
Oracle DBA
Audit Commission
+44 117 975 7805

This email contains information intended for the addressee only. It may be confidential and may be the subject of legal and/or
professional privilege. Any dissemination, distribution, copyright or use of this
communication without prior permission of the sender is strictly prohibited.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Mar 17 2004 - 03:45:35 CST