Re: Re: Question about Connection Pooling...

From: Ryan <ryan.gaffuri_at_cox.net>
Date: Sat, 20 Mar 2004 20:37:12 -0500
Message-ID: <354c01c40ee5$08974ed0$51a36244@ryan2le36ofjce>

Here is the solution I use for tracing....

Create a user called 'trace' which has all the privileges of the account used by the application that goes through the middle tier.
Attach a logon trigger that turns on a 10046 trace and inserts the full path of the trace file into a table.
Have your middle tier team code one user that connects to trade.
When there is a performance problem ask your users to log in with the trace user.
write a korn shell script that loops through all the traces in the your trace log table, runs tkprof and concatenates them together.

seems like the simplest solution. Hardest part is getting the middle tier guys to help.
----- Original Message -----
From: "Tanel Poder" <tanel.poder.003_at_mail.ee> To: <oracle-l_at_freelists.org>
Sent: Saturday, March 20, 2004 8:34 PM
Subject: Re: Re: Question about Connection Pooling...

> Hi!
>
> Well, you could set the client_identifier to application user or session's
id, before each call to database. You can do it with OCI_ATTR_CLIENT_IDENTIFIER parameter in OCIAttrSet function or thick JDBC.
> An alternative is to call dbms_session.set_identifier before every
database call, but this would require additional network roundtrip and dbcall, while OCI and thick JDBC don't, when implemented properly.
>
> Then you can use trcsess utility (from 10g distribution) to consolidate
all trace from your client_id into one trace file.
> I can't test now whether trcsess is able to "eat" trace from earlier
versions, it should be able as long as the "*** CLIENT ID:" line is written to trace by Oracle server upon client_id change.
>
> Alternatively, you could use module and action parameters for tracing a
specific operation, not application user..
>
> Tanel.
>
> ---
> Saatja: "Gudmundur Josepsson" <gbj_at_index.is>
> Kuupäev: 20.03.2004 12:28:46
> ---
> > Tanel,
> >
> > Thanks for the explanation about the difference between connection
> > pooling
> > and session pooling. We always talk about connection pooling but
> > according
> > to your definition we're using session pooling. The sessions are
> > opened
> > when the app server starts and are kept open continuously. The apps
> > then
> > come in on the first free session and might re-appear in a different
> > session
> > on their next call. Makes it a bit tough when it comes to 10046
> > tracing,
> > for example.
> >
> > Gummi
> >
> > ----- Original Message -----
> > From: "Tanel Poder" <tanel.poder.003_at_mail.ee>
> > To: <oracle-l_at_freelists.org>
> > Sent: Saturday, March 20, 2004 4:13 AM
> > Subject: Re: Question about Connection Pooling...
> >
> >
> > > There is a difference between connection pooling and session
> > pooling in
> > Oracle. You might have a pre-allocated pool of connections, but every
> > app
> > server request to database still creates a new session using one of
> > the
> > connections in connection pool.
> > >
> > > If you want even better scalability, go with session pooling (done
> > either
> > by app server code or OCI), but if you got need for stateful
> > sessions, then
> > you can't use PL/SQL package variables for saving the states of a
> > session,
> > global application contexts and client_id should be used instead.
> > >
> > > OCI supports both connection and session pooling, I don't know
> > about IIS.
> > >
> > > Tanel.
> > >
> > > > We are using Microsoft IIS for our middle tier. I
> > > > did some tests by using a logon trigger and recording the Sid. I
> > > > found that
> > > > every query results in a new connection. Our software engineers
> > tell
> > > > me that the
> > > > middle tier is reusing the same connections. I'm not seeing that.
> > > > What are your
> > > > experiences with connection pooling?
> > > >
> > > >
> > > >
> > >
> >
> > ----------------------------------------------------------------
> > 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 Sat Mar 20 2004 - 19:35:35 CST