Alex Rivera | Logout

Relation between Oracle session and connection pool

Asked 2009-06-24T17:00:09.243
23

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 ?

oracle-database oracle10g connection websphere
Edit
Report

1 Answer

2

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

  1. Your SGA may be smaller or larger
  2. You may be on 11g and letting Oracle allocate PGA and SGA out a single pool
  3. 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.
  4. You may have some memory hungry processes that chew up stuff

PS. Does the 2GB mean you are on Windows ?

answered 2009-06-25T00:13:08.010

Your Answer