I have a webapp deployed using the following:

glassfish 2.1
spring 2.5.6
hibernate 3.3.2 ga
Oracle 10.2

At some point in time our app gets into a state where LocalDataSourceConnectionProvider.closeConnection() and LocalDataSourceConnectionProvider.getConnection() is thrashing. By this I mean jvisualvm shows it's taking collectively around 40% of the self time. Once we restart the container database operations are fast again.

There are those that believe this is a problem in the connection pool in glassfish. We are using dataguard on Oracle and we've also been told that there is a bug in our current version with closing connections correctly. Also that there is a problem with Oracle and data guard on this version.

When this thrashing happens we can see on the Oracle side that connections start increasing. Normal average is around 10, but with thrashing this goes to 30+.

Here is the full jvisualvm image:

http://tinypic.com/r/2w7352d/7

This is another jvisualvm snapshot that shows the times broken down by operations like doQuery(), prepareQueryStatement(), getConnection():

http://tinypic.com/r/p93b9/7

It looks like doQuery() takes 41991ms, broken down further another doQuery() as part of that overall time takes 23861 ms, and the com.sun.gjc.spi.base.DataSource.getConnection() takes 16876 ms during the profiling.

Obviously one thing to do is upgrade Oracle to 11 and patch. However working for a big company things move slowly. We will get there hopefully soon. I'm wondering if we can do something in the meantime to reduce this issue.