[ACCEPTED]-Relation between Oracle session and connection pool-websphere

Accepted answer
Score: 15

Check out this book on google books. It explains 2 the differences between connections and 1 sessions.

Score: 4

Metalink gives the following advice about 22 the SESSIONS parameter:

Recursive sessions 21 are an essential part of the normal functioning 20 of the RDBMS. It is impossible to identify every 19 circumstance that would require such sessions, but 18 in general, if a user-initiated operation 17 requires the manipulation of data dictionary objects, then 16 recursive sessions may get created. To 15 take a simple example, say you create 14 a table while logged in as some ordinary 13 user. Behind the scenes this has to insert 12 rows into obj$, tab$ etc which are owned 11 by the SYS user. Since a normal user would have 10 no privilege to insert into these objects, a 9 recursive session is created which logs 8 in as SYS.

Solution:

Increase the SESSIONS parameter.

Recommendation 7 is to preserve 50% of the SESSIONS value 6 for recursive sessions. So, for example 5 if it is expected to have 30 client sessions open, then 4 set the SESSIONS parameter to 60.

So, depending 3 on what websphere and your user process 2 are doing this could partially explain what 1 you're seeing.

Score: 2

My v$session contains 30 entries, 4 of which 14 have a username (one of which is a background 13 job).

If you've got background processes 12 (eg batch jobs), they could be chewing up 11 sessions.

But it could be that you are simply 10 running out of memory. 2GB seems a bit low 9 for a conneection pool of 50 sessions. Assuming 8 Oracle 10g, you're RAM is divided into shared 7 (SGA) and process (PGA). Say you've got 6 1.5GB for SGA, that leaves 500MB for all 5 the sessions. If sessions grab 10MB each, you'll 4 hit your limit around 50 sessions.

In reality, 1. You'll 3 have some other 'stuff' running on the box, so 2 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 1 the 2GB mean you are on Windows ?

Score: 1

Are all your connections using the same 6 user account? If so, you might want to 5 check to see if you have a per-user session 4 limit for that user account.

Also, are 3 you licensed for more than 40 connections? (Check 2 if you have LICENSE_MAX_SESSION set in your 1 parameter file)

Score: 1

The session pool is client-side driven. It 17 doesn't know (or control) how many sessions 16 the database will allow.

You should look 15 on the server to determine the actual number 14 of connections that are allowed and set 13 the session pool number based on what the 12 server will allow.

Your connection pool should 11 not use all of the connections allowed. This 10 will let other IDs connect. If you have 9 an application using USER_1, you'd set the 8 connection pool to use some amount of the 7 allowed connections, but leave enough connections 6 for... Oh, say DBA to log in.

-- Edit --
Processes 5 are probably runing out before your connection 4 pool maxes out.

SQL> show parameter processes

NAME                                 TYPE        VALUE
--------------------------------------------------------------------------------
processes                            integer     40

This is the total # of processes 3 allowed Now see how many are already used 2 - many of them are background procs, you'd 1 never think of.

SQL> select count(*) from v$process;

More Related questions