Relation between Oracle session and connection pool
Solution 1
Check out this book on google books. It explains the differences between connections and sessions.
Solution 2
Metalink gives the following advice about the SESSIONS parameter:
Recursive sessions are an essential part of the normal functioning of the RDBMS. It is impossible to identify every circumstance that would require such sessions, but in general, if a user-initiated operation requires the manipulation of data dictionary objects, then recursive sessions may get created. To take a simple example, say you create a table while logged in as some ordinary user. Behind the scenes this has to insert rows into obj$, tab$ etc which are owned by the SYS user. Since a normal user would have no privilege to insert into these objects, a recursive session is created which logs in as SYS.
Solution:
Increase the SESSIONS parameter.
Recommendation is to preserve 50% of the SESSIONS value for recursive sessions. So, for example if it is expected to have 30 client sessions open, then set the SESSIONS parameter to 60.
So, depending on what websphere and your user process are doing this could partially explain what you're seeing.
Solution 3
My v$session contains 30 entries, 4 of which have a username (one of which is a background job).
If you've got background processes (eg batch jobs), they could be chewing up sessions.
But it could be that you are simply running out of memory. 2GB seems a bit low for a conneection pool of 50 sessions. Assuming Oracle 10g, you're RAM is divided into shared (SGA) and process (PGA). Say you've got 1.5GB for SGA, that leaves 500MB for all the sessions. If sessions grab 10MB each, you'll hit your limit around 50 sessions.
In reality, 1. You'll have some other 'stuff' running on the box, so won't have a full 2GB available to Oracle
- Your SGA may be smaller or larger
- You may be on 11g and letting Oracle allocate PGA and SGA out a single pool
- You may be using PGA_AGGREGATE_TARGET (letting Oracle guess at the PGA settings based on the number of sessions) or setting memory limits yourself.
- You may have some memory hungry processes that chew up stuff
PS. Does the 2GB mean you are on Windows ?
Solution 4
Are all your connections using the same user account? If so, you might want to check to see if you have a per-user session limit for that user account.
Also, are you licensed for more than 40 connections? (Check if you have LICENSE_MAX_SESSION set in your parameter file)
Solution 5
The session pool is client-side driven. It doesn't know (or control) how many sessions the database will allow.
You should look on the server to determine the actual number of connections that are allowed and set the session pool number based on what the server will allow.
Your connection pool should not use all of the connections allowed. This will let other IDs connect. If you have an application using USER_1, you'd set the connection pool to use some amount of the allowed connections, but leave enough connections for... Oh, say DBA to log in.
-- Edit --
Processes are probably runing out before your connection pool maxes out.
SQL> show parameter processes
NAME TYPE VALUE
--------------------------------------------------------------------------------
processes integer 40
This is the total # of processes allowed Now see how many are already used - many of them are background procs, you'd never think of.
SQL> select count(*) from v$process;
Related videos on Youtube
Sathya
Updated on July 09, 2022Comments
-
Sathya almost 2 years
Let me explain the set up first.
We have an oracle server running on a 2GB RAM machine. The Db instance has the init parameter "sessions" set to 160.
We have the application deployed on Websphere 6.1. The connection pool settings is Min 50 and Max 150.
When we run Load test on 40 Users (concurrent, using jMeter), everything goes fine. But when we increase the concurent users to Beyond 60, Oracle throws and exception that it is out of sessions.
We checked the application for any connection leaks but could not find any.
So does it mean that the concurrency of 40 is what this setup can take ? Is increasing the Oracle sessions/process the only way to obtain higher concurrency ?
How exactly are the DB sessions and Connection in the Connection pool related ? In my understanding, the connections cannot exceed the sessions and so setting the Max Connection pool to more than sessions may not really matter. Is that correct ?
-
Eric Petroelje almost 15 yearsYou might want to consider putting this on serverfault.
-
user3443701 almost 15 yearsThis is totally programming related unfortunately.
-
Eric Petroelje almost 15 years@Terry - yes, but an Oracle DBA would probably have more insight about this than your average programmer.
-
user3443701 almost 15 yearsI'm married to an Oracle DBA. I'm a Java developer. We still haven't found the answer and this is a question I've asked her many times.
-
Sathya almost 15 yearsERROR code is ORA-00018: maximum number of sessions exceeded
-
bytefish about 5 yearsStill confused why people use connections pool but not session pool
-
-
Eric Petroelje almost 15 yearsI think he's set there - with a max pool size of 150 and a max of 160 sessions, he should have ~10 left over for other user connections.
-
Sathya almost 15 yearsthe parameter license_max_sessions is set to zero. sessions = 170 sessions_max_open_files = 10 shared_server_sessions - not set to any value.
-
Sathya almost 15 yearsIt is a windows box. 2GB RAM is the total RAM avbl. We are on Oracle 10g R2. Max SGA Size is 652 MB Aggregate PGA Target is 24 MB Current PGA Allocated is 72 MB Max PGA ever allocated is 247 MB
-
Dan Nissenbaum about 12 years(Since no comments have been added - I might as well point out that the link is worth looking at. It jumps straight to a useful chapter of interest in a book about Oracle.)
-
Johann about 10 yearsA more useful answer would paraphrase what the book describes. That link was informative, but I'm still left wondering about the performance issues the OP described.
-
João Paladini over 7 yearsAlso, Google Books is blocking some of the relevant important pages, so summarization would be much better here.