GeeCON Prague 2014*
The moose likes BEA/Weblogic and the fly likes Need some idea on how to determine the no. of SQL connections that is left open(all across the apln) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Products » BEA/Weblogic
Bookmark "Need some idea on how to determine the no. of SQL connections that is left open(all across the apln)" Watch "Need some idea on how to determine the no. of SQL connections that is left open(all across the apln)" New topic
Author

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

Vinodh Sa
Ranch Hand

Joined: May 06, 2010
Posts: 55
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


Thanks, Vinodh
[Servlet tutorial]
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 16101
    
  21

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.


Customer surveys are for companies who didn't pay proper attention to begin with.
T. Huy Nguyen
Ranch Hand

Joined: Nov 02, 2010
Posts: 57
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.


My material for SCJP (2008), SCWCD (2010), SCBCD (2010). About me
Vinodh Sa
Ranch Hand

Joined: May 06, 2010
Posts: 55
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

Joined: May 06, 2010
Posts: 55
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

Joined: Apr 02, 2008
Posts: 823

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.


Regards, Sud.
SCJP 5 ScjpFAQ JLS
Mohammad Al Hadid
Greenhorn

Joined: Jan 05, 2011
Posts: 10
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.


SCJP, SCWCD
Vinodh Sa
Ranch Hand

Joined: May 06, 2010
Posts: 55
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

Joined: Jan 05, 2011
Posts: 10
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"
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Need some idea on how to determine the no. of SQL connections that is left open(all across the apln)