Re: ORA-00020 too many processes

David Fitzjarrell <oratune_at_aol.com> writes:

> In article <39FEFC2F.6D6C5DA3_at_mssm.edu>,
> Harry Andree <Harry_Andree_at_mssm.edu> wrote:
> > Hello all,
> >
> > We are running Oracle 8.1.5 on Windows NT. We are using an application
> > server uysing JDBC connections to the database. The system ran fine for
> > about a year but now seems to be running out of processes. Nothing has
> > changed that comes to our mind but the use of the system increased. We
> > have upped the number of processes to 1200. The system reached 800
> > processes in a couple of days after restarting the server. Restarting the
> > application server and reinitializing the connections freeed up some
> > processes but not the majority.
> >
> > The question:
> > Is this number of processes normal?
> > If yes what should be a safe number of processes?
> > Is there something that prevents release of processes?

Well that all depends on the application of course. 1,200 certainly sounds like a lot but it's quite possible.

I would suggest starting from the application side to optimize this.

How does the JDBC connection manager tune the number of connections. Sometimes connection managers are set up to assume that there should always be an available idle connection. If your SQL is untuned then the extra response time translates into additional connections. This causes the double whammy of additional I/O or CPU processing the bad SQL plus the additional overhead of excess connections.

Is it leaking connections or are they all actively in use? Is your application leaving lots of open cursors lying about possibly causing a bad JDBC driver to open additional connections?

> > Server: DELL 6300, 512 MBytes RAM, 5 disks, Windows NT 4.0
> > Oracle 8.1.5 enterprice edition.
 

> You mention that the use has increased over the last year, but you
> don't say how much the increase is. If the usage has doubled then it
> is not unreasonable to expect that you would be running out of
> processes. Doubling the initial setting of the init.ora 'processes'
> parameter would not be unrealistic, and 1200 does not appear to me to
> be excessive.

It's all relative. 1,200 sounds like a lot to me especially for the hardware you listed, but if that's what your application needs that may be what you'll have to deal with though.

-- 
greg