Re: How to determine the sessions parameter in the pfile

"Douglas Hawthorne" <DouglasHawthorne_at_yahoo.com.au> wrote in message news:gmERb.31296$Wa.3563_at_news-server.bigpond.net.au...
> Walt,
>
> Are you using dedicated or shared servers on the Oracle instance?
>
> From the sounds of it, your problem seems to a be a case of using
dedicated
> servers for the connections from your webserver farm. This is indicated
by
> your CPU load being not stressed when you have a high number of
connections
> to the database. Each dedicated server consumes one process per
connection
> even though there is now work going on. With the shared servers option
> active, each shared server process is shared between multiple connections
> depending on the workload.
>
> If that is the case, I would suggest implementing shared servers on your
> Oracle instance.
>
> I would investigate shared servers before trying to estimate number of
> processes to run on an instance. I have found the Oracle default to a
good
> value.
>
> Douglas Hawthorne

Hi

I'm a little surprised at the recommendation to used MTS at the database level, given that we don't know how many concurrent sessions there are here, but we *do* know that the *application* is doing connection pooling. In general I'd want to only pool connections in one location - if the app is really generating hundreds or thousands of 'pooled' connections I'd want to look at the app's connection pooling to see how that is working first.

I'd second Paul

Bump up processes to a relatively high value, see how many sessions you actually need at peak load, and also see if the connection pooling is really working correctly on the web server. In a windows IIS/ASP/ASP.NET environment not closing connection objects on each page is a good way of ensuring that connection pooling doesn't work, also the connection string needs to be identical for each connection (so store the connection string in a variable).

Simultaneously look at setting PGA_AGGREGATE_TARGET and the 9i work area size management features, these are likely a good fit for the type of application you describe.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************