Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ORA-00020: maximum number of processes (500) exceeded" Java Connection Pooling

Posted on 2013-05-28
2
Medium Priority
?
1,371 Views
Last Modified: 2013-06-06
Hi Gurus,

I have java web based application running against 11gr2 RHEL 5.5 and uses connection pooling to connect to DB.I want to understand how exactly the mapping is with connections to sessions.

500 is the value for processes parameter which would also include background processes(bg) along with app processes or user processes,Does this mean db could spwan 400 + sessions for the app or user process(excluding the bg process)?

In web based application there would be thousands of users connecting to the db then how would just 500 processes  would sufficent?

I ran out of processes in Production db so wanted to understand the reason for it and got the error ORA-00020: maximum number of processes (500) exceeded"

Please help me understand how the connection pool works.

Regards
0
Comment
Question by:monto1
2 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1050 total points
ID: 39201560
>>Please help me understand how the connection pool works.

In a nut shell:  Connections remain active in the database so that when a request comes in, no new processes are created.  The connection request is attached to an existing connection object in the pool.  If there are no available connections, a new one is created and added to the pool.

>>Does this mean db could spwan 400 + sessions for the app or user process(excluding the bg process)?

Yes.

>>In web based application there would be thousands of users connecting to the db then how would just 500 processes  would sufficent?

It's all in how the application works.  Will all the users of the website need database access at the same time?  It's doubtful but you know the app and how it works.

>>ORA-00020: maximum number of processes (500) exceeded" Java Connection Pooling

It's very possible that the app isn't properly closing/disposing of the connections.

It's also possible that the app just needs more than 500 connections.

We really cannot help with deciding which one of these is the reason.
0
 
LVL 5

Assisted Solution

by:Sanjeev Labh
Sanjeev Labh earned 450 total points
ID: 39210425
A very simple way to get the actual usage of number of processes is by querying the v$resource_limit view. This will show what max process is defined and what limit it has reached.

A simple way also to understand this would be the number of managed servers being used per apps server. A multiple of this plus some additional buffer should be able to cater to your number of processes.

However, number of processes should not be mixed with number of sessions. Sessions would be related to the connection pooling. If for each managed server a minimum of 50 connections has been defined then at least a minimum of 50 Oracle sessions will be opened by the apps server per connection pool. The maximum would depend on the maximum figure as defined. Total minimum sessions will be min sessions multiply with number of connection pool. Therefore if 20 connection has been defined then it will 20*50=1000. Sessions would relate to connection pool and processes would relate to managed servers.

However the best way to check this out would be to query the v$resource_limit view. This should give you a fair idea what your process and session parameters may be defined to.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question