Re: Webserver connection pooling and sql trace

rgaffuri_at_cox.net (Ryan Gaffuri) wrote:
> vslabs_at_onwe.co.za (Billy Verreynne) wrote in message
> news:<1a75df45.0312232205.4d1963a3_at_posting.google.com>...
> > rgaffuri_at_cox.net (Ryan Gaffuri) wrote
> >
> > > The software engineers set up middle tier connection pooling. This
> > > opens several dedicated processes to the instance and pools user
> > > connections.
> > >
> > > how do I use sql trace with this? statspack is giving me aggregates.
> > > I need to see what individual users are doing. Carrie Milsap has a
> > > good article on hotsos.com called 'Oracle Operational Timing Data'
> > > that warns you to be careful of aggregate data and to use sql trace.
> > >
> > > Not sure if I can turn off connection pooling for test purposes.
> >
> > Why do you want to trace users? In this configuration you are not
> > dealing with users. The app server deals with users. You deal with
> > applications on the app server. Trace and monitor the applications.
> >
> > Client-server is a software configuration. The clients connecting to
> > your database are applications and no longer users.
>
> sorry for the delayed response, Ive been on vacation. we are still in
> development. Its a big scale up. Im doing pre-emptive tuning. So I
> want to monitor testing as we go. Its easier that way.
>
> If you read the article by Carrie Milsap, he states that statspack
> wait events can be misleading because its aggregate data.

And Milsap also says *why* this is the case. I don't think you understand his argument. Aggregate data is not suitable for tuning specific bottlenecks. But it doesn't seem that you are trying to tune specific bottlenecks.

> To find for
> certain what may be causing the bottlenecks you need to run a 10046
> trace.

But you must run 10046 level 8:
1) on the session (and only the session) experiencing the bottleneck. 2) during the time period (and only the time period) that the bottleneck is experienced.

It seems like you are obsessing over 1, but ignoring 2. In other words, you don't want to aggregate over irrelevant sessions, but do want to aggregate over irrelevant time periods.

Until you know where your bottleneck is (or even if you have one), I don't see how you can apply a bottle-neck tuning method at all.

> James morle has a nice article on oaktable.com about doing this
> without using an app server.

Which one?

> development here between software engineers and database people is
> seperate. I was hoping to find some form of method for handling this
> myself without having to go to the software engineers. Might not be
> possible.
>
> Hmm.. there are more and more people using connection pooling. so
> hopefully some methods will come out over the next year or so.

I guess I still don't understand the problem. Are you using pass-through authentication, or does the application server do everything under it's own userid?

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB