Relationship between JDBC Sessions and Oracle Processes

Question

We had a problem with too many Oracle processes being created (over 2000) when connections are limited to 1,100 (using C3P0)

Two questions:

• What is the relationship between the Oracle process and the JDBC connection? Is one Oracle process created for each session? Is it created for every JDBC statement ? No relationship at all?

• Have you ever come across this scenario when you create more processes than JDBC connections?

Any comments would be greatly appreciated.

Answer 1

There is one session for each session. It sounds like you have a connection leak, somewhere you open a new connection and do not close properly. One possibility is that you open, use, and close the connection inside the try block and handle the exception in catch or return early for some reason. If so, you need to make sure that the connection is closed at finally , or may not happen, leaving the connection (and therefore the session) hanging. Opening two compounds in the same area without explicitly closing between them can also do this.

I am not familiar with C3PO, so I don’t know how connections are handled, or where and how the 1100 limit is imposed; if you (or you) have a connection pool, and the 1100 you are referring to is the maximm pool size, then this does not seem to be a problem, since you ended up in the pool cap before closing the session.

You can look in v$session to confirm that all sessions come from JDBC and there is nothing else.

Answer 2

Perhaps you want to check if your server is running in ad hoc or collaborative mode (you probably want to switch it to general mode if you want to reduce the number of active processes).

You can verify this by doing

 select server from v$session 

Additional Process Architecture Information

http://docs.oracle.com/cd/B19306_01/server.102/b14220/process.htm

Shared / Dedicated Server Mode

http://docs.oracle.com/cd/B10501_01/server.920/a96521/manproc.htm