Relationship between JDBC sessions and Oracle processes

Question

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

Two questions:

• What's the relationship between an Oracle process and a JDBC connection? Is one Oracle process created for each session? Is one created for every JDBC statement? No relationship at all?

• Did you ever face this scenario, where you are creating more processes than JDBC connections?

Any comment would be really appreciated.

Answer 1

There is one session per connection. This sounds like you have a connection leak, somewhere you're opening a new connection and not closing properly. One possibility is that you open, use and close a connection inside a try block and are handling an exception in a catch, or returning early for someother reason. If so you need to make sure the connection close is done in finally or it may not happen, leaving the connection (and thus session) hanging. Opening two connections in the same scope without an explicit close in between can also do this.

I'm not familiar with C3PO so don't know how connections are handled, or where and how your 1100 limit is imposed; if it (or you) have a connection pool and the 1100 you refer to is the maximm pool size, then this doesn't sound like the issue as you'd hit the pool cap before the session cap.

You can look in v$session to confirm that all the sessions are coming from JDBC, and there isn't something else connecting.