Re: How to determine the sessions parameter in the pfile

From: Mark Bole <makbo_at_pacbell.net>
Date: Wed, 28 Jan 2004 03:58:59 GMT
Message-ID: <7sGRb.18700$kp7.15768@newssvr25.news.prodigy.com>

Douglas Hawthorne wrote:

> Walt,
> 
> Are you using dedicated or shared servers on the Oracle instance?
> 
>[...]
> 
> "Walt" <walt_at_boatnerd.com.invalid> wrote in message
> news:4016F475.FD20601D_at_boatnerd.com.invalid...
> 

>>We just put an instance of Oracle 9.2 on W23k on line. So far, so
>>good. It's connected to a webserver farm which uses connection pooling
>>to increase or decrease the number of connections based on load.
>>Unfortunately, at some peak load times the maximum number of sessions is
>>reached and users get the "ORA-00020 : Maximum processes exceeeded" .
>>During those periods, the oracle server doesn't seem to be stressed (low
>>CPU usage, etc.) so I think the problem is just that the processes
>>parameter is artificially low.
>>
>>So, how does one determine a good value for the processes parameter?
[...]

Your guess as to the problem is a good one.

For connection pooling that I'm familiar with, you'd want dedicated servers, tuned accordingly, since you have a stable and predictable environment in terms of login sessions (your pool should have some kind of min and max setting). For a different type of environment where there are individual users, whose numbers might surge at certain times, I'd go with shared servers to minimize the impact of thousands of concurrent sessions to a single instance.

As previously mentioned, the Oracle defaults are a good place to start in a test environment. You might want to capture the number of active versus inactive Oracle sessions over time as a way of estimating the percent of your pool capacity that is actually used. The pooling software itself might also log a high water mark for active pool connections, check for that number too.

I don't remember the exact details, but there are certain advanced Oracle features that are not be supported under shared server (MTS) so be sure to test first before changing...

--Mark Bole Received on Tue Jan 27 2004 - 21:58:59 CST