Wednesday, February 10, 2010

Monitoring Data Source Connection Leaks

When it comes to production, often you can face problems with growing number of Data Source connections. I see this as frequent problem - WebLogic admins complain that ADF application is using 1000 Data Source connections, on other hand ADF developers postpone code quality to be checked after deadline :-) And then they start to blame ADF, no need for this, just monitor your system and double check your code.

Today I will demonstrate how you can monitor  Data Source usage with Oracle Enterprise Manager 11g. I will show Data Source connection leaking case, in my next post I will tell you the reason, why you can have leaked Data Source connections in ADF (it will be development bad practice example).

Among many performance monitoring options available in Oracle Enterprise Manager 11g, you can monitor your application Data Source. I prefer to check 4 parameters:

  1. Connection Pool Size changes
  2. Available Connections changes
  3. Number of Connections in Use
  4. Connection Leaks

You can see that HrDSDS Data Source current Pool Size is 1, there is 1 connection available and 0 connections in use. No connection leaking at the moment as well:

As a next step, I opened my ADF application. It is immediately reflected in Data Source graph - 0 connections available, 1 connection in use:

In order to optimize Data Source usage, you need to enable Inactive Connection Timeout option in WebLogic Console. With this option enabled, WebLogic will try to return inactive reserved connections from Connections in Use pool to Available Connections pool. This will prevent Connections Pool Size grow. Just set positive number of seconds:

In my case, I set it to 30 seconds, it is because I'm monitoring application for testing purpose. You should set it to longer period, may be 600 seconds.

Most important, in that case if developer will forget to close Data Source connection or because ADF development bad practice - WebLogic will report exception when trying to remove leaked connection back to Available Connections pool. Leaked connection will be returned to Available Connections pool forcibly. In my example you can see that after 30 seconds, leaked Data Source connection was reported, but still it was removed from Connections in Use pool and returned back to Available Connections pool - means it will be reused later:

How you can access Data Source monitoring options? Just open Performance Summary screen for that domain, where ADF application is deployed:

From Metric Palette expand Datasource Metrics node, and there you will find monitoring options for your Data Source:

1 comment:

wheatbeer said...

Is there a way to determine what datasource a web application is using at runtime? I was trying to use mbeans to determine this but I'm not sure this information is available at the mbean level.