Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-05-28
2
Medium Priority
?
1,345 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 77

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

730 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