RE: SQL*Net message waits
- From: "Niall Litchfield" <n-litchfield@xxxxxxxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 17 Mar 2004 09:41:27 +0000
Hi
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
1. setup a dedicated connection that doesn't use connection pooling and trace
that.
2. 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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
