Re: Rule of thumb for number of Maximum Connections

On 3 Nov 2004 14:03:42 -0800, ytalreja_at_comcast.net (Y) wrote:

>Folks,
>
> We are running 6 instances of a proprietary application server
>against an Oracle8 instance with a connection pool of 16 each (for a
>total of 96 connections). This is running on a dual proc Sun server
>with 1 GB of memory As our volumes have ramped up, we need to increase
>the number of connections in the pool and the related parameters in
>Oracle's config files.

Why do you *assume* you need to increase the number of connections? You have a connection pool, remember?
With 96 connections you typically don't need any pool at all!

 All users to the database go through these App
>Servers/connection pools.
>
> I believe I would need to increase the queuesize in listener.ora'

Frankly I have NEVER used that parameter, and I am running with hundreds of simultaneous connections.

>file and the SESSIONS/PROCESSES parameters in init.ora file. Are there
>any other parameters I need to change?

SEMMNI and SEMMNS on Unix come to mind. The number of semaphores should equal (per database!) 2 * #processes + 10.

>
> I would like to know if there is a formula to determine a "safe"
>limit for bumping these up; i.e. if there is a rule of thumb for
>correlating maximum number of these parameters with the # of CPUs/
>memory size. I do realize that if I bump these up too high, I will run
>into the Unix limit of max. # of processes, but that typically exceeds
>1024.

Typically you shouldn't indiscriminately 'bump' any parameters at all without being capable to demonstrate lack of resources and/or error messages.
'Bumping up' usually results in relocating the problem to the O/S

>
>Yash

--
Sybrand Bakker, Senior Oracle DBA