Re: SQL*Net message waits

From: Robyn Anderson Sands <robyn_at_iTeamTech.COM>
Date: Wed, 17 Mar 2004 10:08:25 -0500 (EST)
Message-ID: <Pine.GSO.4.05.10403170915160.6970-100000@dizzy.sweettea.org>

I somehow got 'lucky' and am responsible for the application layer as well, so I may be able to do this. The rough numbers have at least caused people to reconsider blaming all the problems on the connections so I'll have resources to investigate.

I'll try this in dev and see what I get ...

Robyn

-----Original Message-----
From: ryan.gaffuri_at_cox.net [mailto:ryan.gaffuri_at_cox.net] Sent: Tuesday, March 16, 2004 6:27 PM
To: oracle-l_at_freelists.org; oracle-l_at_freelists.org Subject: Re: SQL*Net message waits

this is how Im planning on handling tracing with connection pooling. You will need a little help from your middle tier team.

create a new user, I call mine 'trace'. Grant trace all the privileges as the username that your middle uses. Have your middle tier team set up user log in that logs into the database as this user.
Create a logon trigger that enables tracing and captures the full path to the trace file in a table(the query is on ixora.com.au). In some connection pooling methods you will get alot of files since they connect and disconnect. So in that case have a Ksh script read the table, tkprof the output, and concatenate it together.

Connection pooling is a challenge. I hate the idea of having to guess what the users are doing. Can't help you with the rest of it.
>
> From: Robyn Anderson Sands <robyn_at_iTeamTech.COM>
> Date: 2004/03/16 Tue PM 03:39:45 EST
> To: oracle-l_at_freelists.org
> Subject: SQL*Net message waits
>
> Hi all,
>
> Performance concerns have been raised for a particular application. The
> users can't tell me exactly which actions they believe are slow, but it
> has been suggested that the problem may be the connection between the
app
> server and the database. This was a fairly small app, with all
components
> running on the same server. When we upgraded, the database went to a
Unix
> server, and the other app process were split across several Win2003
> servers. App servers are in the same rack, DB is connected with a gb
> line.
>
> I ran separate 10046 traces against the most active sessions on a
typical
> afternoon. Connections are pooled, so several different user sessions
are
> represented in the trace files. The SQL*Net messages from the client
> include a range of wait times, but since the sessions included periods
of
> inactivity, I'm considering this user think time, not wait time. 99.9%
> SQL*Net messages from the database back to the client are sub .00
seconds,
> a very few took .01 second. Total time for all SQL*Net messages in the
> traced sessions in a 30 minute window was .05 seconds. I'd like to be
able
> to use this information to show that the connection between the database
> and the application server is not the performance block. Is this a
valid
> way to use the data or am I reaching here? Can latency be hiding in the
> 'from client' messages that wouldn't appear in the 'to client' messages?
>
> There appear to be too many database calls; still trying to investigate
> that, but it's a vendor app and we have limited ability to change their
> code. Performance issues seem to be related to the client workstation
> size, etc. but I need to prove that the issue is not between the app
> server and the db so we can move on and find the real problem.
>
> TIA ... Robyn
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>

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


----------------------------------------------------------------
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 - 09:18:34 CST