Optimize and tuning for weblogic data source connection timeout

Data source connectivity is quite important for an application and make sure that all possible features are set and available before executing the application scenarios:

Weblogic Connection Pool Settings

  1. Maximum Capacity: This is calculated based on the number of sessions allocated in database / number of weblogic servers. If total sessions is 200 and 4 weblogic servers , then maximum capacity will be 200 /5 = 40
  2. Initial Capacity:  This includes the number of sessions made available at the time when server starts. It is also said that keep the initial capacity to maximum capacity so that all connections are available but the server will not be able to use all the available connections till it reaches the peak load.
  3. Capacity Increment: Ideal value for this should be less than 10. I have kept 5 only.
  4. Shrinking Frequency: This indicates the number of seconds to wait before shrinking a connection pool that has incrementally increased to meet demand.
  5. Statement cache type:  The most commonly used algorithm is LRU (default).  LRU stands for Least Recently Used. Weblogic Server caches the executed statements to avoid database call for the same statement required to be executed again by some other thread.  Once the cache is full then weblogic server determines which existing statement in the cache was the least recently used and replaces that statement in the cache with the new statement.
  6. Seconds to Trust an Idle Pool Connection: This indicates the number of seconds within a connection use that weblogic server trusts that the connection is still viable and will skip the connection test, either before delivering it to an application or during the periodic connection testing process. Default value is 10.
  7. Connection Timeout: While creating the data source, add the below property in the connection pool tab for adding the increased value for the connection timeout. Oracle.net.CONNECT_TIMEOUT=10000000
  8. Statement cache size:  The number of prepared and callable statements stored in the cache.  The statement cache depends upon  below factors:

–          Number of servers which data source deployed

–          Maximum connection for data source

–          Maximum cursor open allowed on database side

Weblogic statement caches related to database cursor and cache contains too many statements and reaches maximum limit of open cursors can impact the server performance. Try to reduce Statement Cache Size setting value or   increase maximum open cursors allowed for a connection