Wednesday, October 24, 2012

ResourceLimitException : No resources currently available in pool to allocate to applications – Oracle SOA 11g

ResourceLimitException : No resources currently available in pool to allocate to applications – Oracle SOA 11g

You could have observed the below exception frequently in the weblogic server log file, weblogic server throws the below exception when there is not enough database connections in the pool to serve the incoming requests reporting increasing the connection pool count. This will affect the overall system  performance

java.sql.SQLException: Internal error: Cannot obtain XAConnection weblogic.common.resourcepool.ResourceLimitException: No resources currently available in pool SOADataSource to allocate to applications, please increase the size of the pool and retry..
at weblogic.common.resourcepool.ResourcePoolImpl.reserveResourceInternal(ResourcePoolImpl.java:577)
at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:342)
at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:329)
at weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:417)
at weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:324)


The above exception is due to there is not enough connections available in the pool to service the requests.

To identify which datasource / connection pool requires more connection do the following.

  • Go to Weblogic Admin console
  • Go to Environment -> Servers
  • Select the server on which the connection pool needs to be monitored
  • Go to Monitoring -> JDBC
  • Customize the table to add the below two properties
This page will list all the datasources/ connection pools targeted to that particular server and how many connections are waiting to get the connection. If more number of connections are waiting, that is not a good which will cause performance impact and also cause stuck threads on the server if the wait time is going to be beyond the thread stuck time (By default it's 10 mins).

(This screen shot taken by reducing the max capacity to 5 for blogging purpose, unfortunately I did not captured the actual screen shot)


Above screenshot shows SOADataSource state is "Overloaded" and also the current wait is 19. There are 19 resources waiting for connection . The above setup is definitely a problem which will result database calls performance issue.

If the connection pool count is not increased with respect to the invoker thread count, then it will cause this kind of contention cause lot of calls to be waiting for connection.


Solution to the above problem is to increase the number of connections in the connection pool.

Steps to increase the  number of connections in the connection pool

  • Go to WLS Console
  • Select Services -> Datasources -> select the datasource you want to change the pool size
  • Go to Configuration -> connection pool
  • Change the Maximum Capacity to the count which is required for your environment.



10 comments:

  1. Hi Albin,

    Great post as always. Can you explain more on BPEL Engine properties or point to some documentation/ other blogs which explain.

    Thanks,
    Sai

    ReplyDelete
    Replies
    1. Refer the following document - http://docs.oracle.com/cd/E15586_01/integration.1111/e10226/bp_config.htm

      Thanks
      Albin
      www.albinsblog.com

      Delete
  2. What if the Pool size is already the max value, and still I am getting the exception:-
    Caused by: weblogic.jdbc.extensions.PoolLimitSQLException: weblogic.common.resourcepool.ResourceLimitException: No resources currently available in pool OracleDS to allocate to applications, please increase the size of the pool and retry..

    ReplyDelete
    Replies
    1. Hi ,
      did you get this solve?

      Delete
    2. Maximum capacity upper limit is 2147483647. did you cross this number as well?

      Delete
  3. Hi Bhavna,

    Were you able to resolve the issue. Kindly assist

    ReplyDelete
  4. Thanks for the explication. You explain everything about the error and the solution.

    ReplyDelete
  5. Hi Bhavna,
    did you resolve your issue?

    ReplyDelete
  6. Bhavna, how long more you'll be "Bhaving" :) Please answer did you get the issue resolve and if so how?

    ReplyDelete
  7. Hi All, The best way is first we have close all DB connection once we use it..in finaly block. Else these type of error will come no mater how much you increase connection pool size

    ReplyDelete