Solved

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

Posted on 2013-05-28
2
1,203 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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 350 total points
Comment Utility
>>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 150 total points
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now