Need some idea on how to determine the no. of SQL connections that is left open(all across the apln)

[Vinodh Sa]

Hi,

In our project, we frequently face a problem of open cursors exceeded. Even though we use close connection in finally block, sometime our programmers forget to call them in the finally block, which leads to a leakage connection. This leakage connection is not found till the code goes to production, since in production only the number of open connections exceed the specified limit, whereas in testing environment the max number of open cursors will never be reached, since the number of people testing is abt 10 only.

So, it will be helpful if it is possible to find the number of connections that have been opened and not closed (all across the application) in java side.

Can someone help ?

FYI : We are using JSF 1.1 and BEA Web Logic Server 9.2 and Oracle 10g

[Tim Holloway]

This is one of the main reasons why I like the Spring Framework. Spring can wrap that system and eliminate the tiresome and (as you have seen) error-prone process of obtaining and releasing connections.

If you were using Tomcat, I'd recommend that you look at the Apache DBCP settings. DBCP is the plug-in that comes with Tomcat for database connection pooling and it has several configuration options that help find and manage connection leaks.

I've never had to go that deep into WebLogic, however. I'm not sure what they're currently using for connection pool management, so I suggest that you check their documentation and/or ask in the WebLogic forum.

In fact, I'll try and move this question over there, since there's nothing JSF-specific in this question.

[T. Huy Nguyen]

I haven't used WLS 9 before, but there should be a Connection leak profiling option in WLS's online console. If a connection leak is detected, a .tsf file will be appended with stack trace of the java code that first asked for the db connection.

Monitoring open and total connections is also possible on the online console.

Of course, you need to use WLS connection pool in order to benefit from the above.

[Vinodh Sa]

Tim Holloway wrote:thanks Tim. I found the same in Web logic console. Actually i was not sure abt how to add the needed parameters in the table that is available under monitoring - JDBC tab, but later i found in one of the site, that we need to use the customize table option and add the needed attributes from a list of available attributes.

[Vinodh Sa]

T. Huy Nguyen wrote:quote] Thanks Huy Nguyen. I found the same in Web logic console. Actually i was not sure abt how to add the needed parameters in the table that is available under monitoring - JDBC tab, but later i found in one of the site, that we need to use the customize table option and add the needed attributes from a list of available attributes.

[sudipto shekhar]

The total number of connections exceeding the specified max. number of connection; is a definite bug in WLS 9.2 - MP2 which was supposed to be fixed in MP3, where we still see the exception.

You should contact the WLS Support team for a resolution.

[Mohammad Al Hadid]

Dear Vinodh Sa,

I have faced this issue and I think that you can do the following:-

open the datasource
go to "Configuration" then "Connection Pool", then "Advanced"
change the "Inactive Connection Timeout:" to 120
Note:- this is the recommended from Oracle but you can make it for example 10 but sure not 0

This is Done from your side (Recommended)

And you can ask the DBA to increase the connection count from his side (According to the project)

I hope that helps, and please let me know if its done by these steps.

[Vinodh Sa]

Mohammad Al Hadid wrote:Dear Vinodh Sa,

I have faced this issue and I think that you can do the following:-

open the datasource
go to "Configuration" then "Connection Pool", then "Advanced"
change the "Inactive Connection Timeout:" to 120
Note:- this is the recommended from Oracle but you can make it for example 10 but sure not 0

This is Done from your side (Recommended)

And you can ask the DBA to increase the connection count from his side (According to the project)

I hope that helps, and please let me know if its done by these steps.

Thanks Mohammad !!! I will try to follow your steps

[Mohammad Al Hadid]

By the way, you can know the number of connections by the following steps:-

Open the administration console
from the left side of the page select
Environment --> Servers (You will find the list of the servers)
Select one server (lets say MyServer)
Go to "Monitoring" tab then "JDBC" sub tab
Then you will see the list of the DataSources that are used by this server and the
Active Connections Average Count
Active Connections Current Count
Active Connections High Count

I think you care about only the "Active Connections Current Count"