• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Vinodh Sa
Ranch Hand
Posts: 55
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Saloon Keeper
Pie
Posts: 18212
53
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 55
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 55
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 826
Chrome Eclipse IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 10
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 55
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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"
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic