Skip Headers
Oracle® Fusion Middleware Configuring and Managing JDBC Data Sources for Oracle WebLogic Server
12c Release 1 (12.1.1)

Part Number E24367-05
Go to Documentation Home
Home
Go to Table of Contents
Contents
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

10 Tuning Data Source Connection Pools

This chapter provides information on how to properly tune the connection pool attributes in JDBC data sources in your WebLogic Server domain to improve application and system performance.

The following sections include information about tuning options for the connection pool in a JDBC data source:

Increasing Performance with the Statement Cache

When you use a prepared statement or callable statement in an application or EJB, there is considerable processing overhead for the communication between the application server and the database server and on the database server itself. To minimize the processing costs, WebLogic Server can cache prepared and callable statements used in your applications. When an application or EJB calls any of the statements stored in the cache, WebLogic Server reuses the statement stored in the cache. Reusing prepared and callable statements reduces CPU usage on the database server, improving performance for the current statement and leaving CPU cycles for other tasks.

Each connection in a data source has its own individual cache of prepared and callable statements used on the connection. However, you configure statement cache options per data source. That is, the statement cache for each connection in a data source uses the statement cache options specified for the data source, but each connection caches it's own statements. Statement cache configuration options include:

You can use the Administration Console to set statement cache options for a data source. See "Configure the statement cache for a JDBC data source" in the Oracle WebLogic Server Administration Console Help.

Statement Cache Algorithms

The Statement Cache Type (or algorithm) determines which prepared and callable statements to store in the cache for each connection in a data source. You can choose from the following options:

LRU (Least Recently Used)

When you select LRU (Least Recently Used, the default) as the Statement Cache Type, WebLogic Server caches prepared and callable statements used on the connection until the statement cache size is reached. When an application calls Connection.prepareStatement(), WebLogic Server checks to see if the statement is stored in the statement cache. If so, WebLogic Server returns the cached statement (if it is not already being used). If the statement is not in the cache, and the cache is full (number of statements in the cache = statement cache size), 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.

The LRU statement cache algorithm in WebLogic Server uses an approximate LRU scheme.

Fixed

When you select FIXED as the Statement Cache Type, WebLogic Server caches prepared and callable statements used on the connection until the statement cache size is reached. When additional statements are used, they are not cached.

With this statement cache algorithm, you can inadvertently cache statements that are rarely used. In many cases, the LRU algorithm is preferred because rarely used statements will eventually be replaced in the cache with frequently used statements.

Statement Cache Size

The Statement Cache Size attribute determines the total number of prepared and callable statements to cache for each connection in each instance of the data source. By caching statements, you can increase your system performance. However, you must consider how your DBMS handles open prepared and callable statements. In many cases, the DBMS will maintain a cursor for each open statement. This applies to prepared and callable statements in the statement cache. If you cache too many statements, you may exceed the limit of open cursors on your database server.

For example, if you have a data source with 10 connections deployed on 2 servers, if you set the Statement Cache Size to 10 (the default), you may open 200 (10 x 2 x 10) cursors on your database server for the cached statements.

Usage Restrictions for the Statement Cache

Using the statement cache can dramatically increase performance, but you must consider its limitations before you decide to use it. Please note the following restrictions when using the statement cache.

There may be other issues related to caching statements that are not listed here. If you see errors in your system related to prepared or callable statements, you should set the statement cache size to 0, which turns off statement caching, to test if the problem is caused by caching prepared statements.

Calling a Stored Statement After a Database Change May Cause Errors

Prepared statements stored in the cache refer to specific database objects at the time the prepared statement is cached. If you perform any DDL (data definition language) operations on database objects referenced in prepared statements stored in the cache, the statements may fail the next time you run them. For example, if you cache a statement such as select * from emp and then drop and recreate the emp table, the next time you run the cached statement, the statement may fail because the exact emp table that existed when the statement was prepared, no longer exists.

Likewise, prepared statements are bound to the data type for each column in a table in the database at the time the prepared statement is cached. If you add, delete, or rearrange columns in a table, prepared statements stored in the cache are likely to fail when run again.

These limitations depend on the behavior of your DBMS.

Using setNull In a Prepared Statement

If you cache a prepared statement that uses a setNull bind variable, you must set the variable to the proper data type. If you use a generic data type, as in the following example, data may be truncated or the statement may fail when it runs with a value other than null.

java.sql.Types.Long sal=null
   .
   .
   .
   if (sal == null)
      setNull(2,int)//This is incorrect
   else
      setLong(2,sal) 

Instead, use the following:

if (sal == null)
      setNull(2,long)//This is correct
   else
      setLong(2,sal) 

Statements in the Cache May Reserve Database Cursors

When WebLogic Server caches a prepared or callable statement, the statement may open a cursor in the database. If you cache too many statements, you may exceed the limit of open cursors for a connection. To avoid exceeding the limit of open cursors for a connection, you can change the limit in your database management system or you can reduce the statement cache size for the data source.

Connection Testing Options for a Data Source

To make sure that the database connections in a data source remain healthy, you should periodically test the connections. WebLogic Server includes two basic types of testing:

The following section discusses automatic connection testing options. For more information about manual connection testing, see Testing Data Sources and Database Connections.

To configure automatic testing options for a data source, you set the following options either through the Administration Console or through WLST using the JDBCConnectionPoolParamsBean:

See the JDBC Data Source: Configuration: Connection Pool page in the Administration Console or see "JDBCConnectionPoolParamsBean" in the Oracle WebLogic Server MBean Reference for more details about these options.

For instructions to set connection testing options, see "Configure testing options for a JDBC data source" in the Oracle WebLogic Server Administration Console Help.

Database Connection Testing Semantics

When WebLogic Server tests database connections in a data source, it reserves a connection from the data source, runs a small query on the connection, then returns the connection to the pool in the data source. The server instance tracks statistics on the pool status, including the amount of time a required to complete a connection test, the number of connections waiting for a connection, and the number of connections being tested. The history of recent test connection behavior is used to calculate the amount of time the server instance waits until a connection test is determined to have failed.

If a thread appears to be taking longer than normal to connect, the server instance may delay testing on other threads until the abnormally long-running test completes. If that thread hangs too long in connection testing (10 seconds by default), a pool may declare a DBMS connectivity failure, disable itself, and kill all connections, whether unreserved or in application hands.

This is very rare, and is intended to relieve the otherwise interminable hangs that can be caused by network cable disconnects and other problems that can lock any JVM thread which is doing a call in a socket read that the JVM will be unable to break until the OS TCP limit is hit (typically 10 minutes). If a pool disables itself in this manner, it will periodically try to reconnect to the DBMS (every 5 seconds by default). Once a new connection can be made, the pool will re-enable itself, and subsequent connection requests will be served as normal (with the pool repopulating itself as load requires).

The query used in testing is determined by the value in Test Table Name. If the value is a table name, the query is select 1 from table_name. If Test Table Name includes a full query starting with SQL followed by space and the query, WebLogic Server uses that query when testing database connections.

If a connection fails the test, WebLogic Server closes and recreates the connection, and then tests the new connection.

Details about the semantics of connection testing is discussed in the following sections:

Connection Testing When Database Connections are Created

When connections are created in a data source, WebLogic Server tests each connection using the query defined by the value in Test Table Name. Connections are created when a data source is deployed, either at server startup or when creating a data source, when increasing capacity to meet demand for connections, or when recreating a connection that failed a connection test.

The purpose of this testing is to ensure that new connections are viable and ready for use when an application requests a connection.

Periodic Connection Testing

If Test Frequency is greater than 0, WebLogic Server periodically tests the pooled connections that are not currently reserved by applications. The test is based on the query defined in Test Table Name. If a connection fails the test, WebLogic Server closes the connection, recreates the connection, and tests the new connection before returning it to the pool.

Testing Reserved Connections

When Test Connections On Reserve is enabled, when your application requests a connection from the data source, WebLogic Server tests the connection using the query specified in Test Table Name before giving the connection to the application. The default value is not enabled.

Testing reserved connections can cause a delay in satisfying connection requests, but it makes sure that the connection is viable when the application gets the connection. You can minimize the impact of testing reserved connections by tuning Seconds to Trust an Idle Pool Connection. See Minimizing Connection Request Delay with Seconds to Trust an Idle Pool Connection.

Minimized Connection Test Delay After Database Connectivity Loss

When connectivity to the DBMS is lost, even if only momentarily, some or all of the JDBC connections in a data source typically become defunct. If the data source is configured to test connections on reserve, when an application requests a database connection, WebLogic Server tests the connection, discovers that the connection is dead, and tries to replace it with a new connection to satisfy the request. Ordinarily, when the DBMS comes back online, the refresh process succeeds. However, in some cases and for some modes of failure, testing a dead connection can impose a long delay.

To minimize this delay, WebLogic data sources include logic that considers all connections in the data source as dead after a number of consecutive test failures, and closes all connections in the data source. After all connections are closed, when an application requests a connection, the data source creates a connection without first having to test a dead connection. This behavior minimizes the delay for connection requests following the data source's connection pool flush.

WebLogic Server determines the number of test failures before closing all connections based on the Test Frequency setting for the data source:

  • If Test Frequency is greater than 0, the number of test failures before closing all connections is set to 2.

  • If Test Frequency is set to 0 (periodic testing is disabled), the number of test failures before closing all connections is set to 25% of the Maximum Capacity for the data source.

Minimized Connection Request Delay After Connection Test Failures

If your DBMS becomes and remains unavailable, the data source will persistently test and try to replace dead connections while trying to satisfy connection requests. This behavior is beneficial because it enables the data source to react immediately when the database becomes available. However, testing a dead database connection can take as long as the network timeout, and can cause a long delay for clients.

To minimize this delay, the WebLogic data sources include logic that disables the data source after 2 consecutive failures to replace a dead connection. When an application requests a connection from a disabled data source, WebLogic Server throws a PoolDisabledSQLException immediately to notify the client that a connection is not available.

For data sources that are disabled in this manner, WebLogic Server periodically runs a refresh process. The refresh process does the following:

  • The server instance executes a health check on the database server every 5 seconds. This setting is not configurable.

  • If the server instance recognizes that the database was recovered, it creates a new database connection and enables the data source.

You can also manually enable the data source using the Administration Console.

Minimized Connection Request Delays After Loss of DBMS Connectivity

If your DBMS becomes and remains unavailable, the data source will persistently test and try to replace dead connections while trying to satisfy connection requests. This behavior is beneficial because it enables the data source to react immediately when the database becomes available. However, testing a dead database connection and trying futilely to replace it can in some cases take as long as the OS network timeout (minutes), and can cause long delays to clients before getting the expected failure message.

Note:

If a data source is added to a multi data source, the multi data source takes over the responsibility of disabling and re-enabling its data sources. By default, a multi data source will check every two minutes (configurable) and re-enable any of its data sources that can re-establish connections.

Minimizing Connection Request Delay with Seconds to Trust an Idle Pool Connection

For some applications that use DBMS connections in a lot of very short cycles (such as reserve-do_one_query-close), the data source's testing of the connection can contribute a significant amount of overhead to each use cycle. To minimize the impact of connection testing, you can set the Seconds To Trust An Idle Pool Connection attribute in the JDBC data source configuration to trust recently-used or recently-tested database connections and skip the connection test.

If Test Reserved Connections is enabled on your data source, when an application requests a database connection, WebLogic Server tests the database connection before giving it to the application. If the request is made within the time specified for Seconds to Trust an Idle Pool Connection, since the connection was tested or successfully used by an application, WebLogic Server skips the connection test before delivering it to an application.

If Test Frequency is greater than 0 for your data source (periodic testing is enabled), WebLogic Server also skips the connection test if the connection was successfully used and returned to the data source within the time specified for Seconds to Trust an Idle Pool Connection.

For instructions to set Seconds to Trust an Idle Pool Connection, see "Configure testing options for a JDBC data source" in the Oracle WebLogic Server Administration Console Help.

Seconds to Trust an Idle Pool Connection is a tuning feature that can improve application performance by minimizing the delay caused by database connection testing, especially during heavy traffic. However, it can reduce the effectiveness of connection testing, especially if the value is set too high. The appropriate value depends on your environment and the likelihood that a connection will become defunct.

Database Connection Testing Configuration Recommendations

You should set connection testing attributes so that they best fit your environment. For example, if your application cannot tolerate database connection failures, you should set Seconds to Trust an Idle Pool Connection to 0 and make sure Test Reserved Connections is enabled so that WebLogic Server will test every connection before giving it to an application. If your application is more sensitive to delays in getting a connection from the data source and can tolerate a possible application failure due to using a dead connection, you should set Seconds to Trust an Idle Pool Connection to a higher number, set Test Frequency to a lower number, and enable Test Reserved Connections.

With these settings, your application will rely more on the data source testing connections in the pool when they are not in use, rather than when an application requests a connection.

Note:

Ultimately, even if WebLogic does its best, a connection may fail in the instant after WebLogic successfully tested it, and just before the application uses it. Therefore, every application should be written to respond appropriately in the case of unexpected exceptions from a dead connection.

Default Test Table Name

When you create a data source using the Administration Console, the Administration Console automatically sets the Test Table Name attribute for a data source based on the DBMS that you select. The Test Table Name attribute is used in connection testing which is optionally performed periodically or when you create or reserve a connection, depending on how you configure the testing options. For database tests to succeed, the database user used to create database connections in the data source must have access to the database table. If not, you should either grant access to the user (make this change in the DBMS) or change the Test Table Name attribute to the name of a table to which the user does have access (make this change in the WebLogic Server Administration Console).

The Test Table Name is an overloaded parameter. Its simplest form is to name a table that WLS will query to test a connection. Setting it to any table, such as "DUAL" for Oracle, will cause the data source to run the query select count(*) from DUAL. If used in this mode, Oracle recommends that you choose a small, infrequently updated table (preferably a pseudo-table such as DUAL).

The second manner in which you can define this parameter is to allow any specific SQL string to be executed to test the connection. To use this option, set the parameter to "SQL " plus the desired sql string. For example SQL select 1 works for SQLServer, which does not need a table in queries to select constants. This option is useful for adding DBMS-side control of WLS pool connection testing, and to make the test as fast as possible.

Table 10-1 Default Test Table Name by DBMS

DBMS Default Test Table Name (Query)

Adabas for z/OS

SQL call shadow_adabas('select * from employees')

Cloudscape

SQL SELECT 1

DB2

SQL SELECT COUNT(*) FROM SYSIBM.SYSTABLES

FirstSQL

SQL SELECT 1

IMS/TM for z/OS

SQL call shadow_ims('otm','/dis','cctl')

Informix

SQL SELECT COUNT(*) FROM SYSTABLES

Microsoft SQL Server

SQL SELECT 1

MySQL

SQL SELECT 1

Oracle

SQL SELECT 1 FROM DUAL

PostgreSQL

SQL SELECT 1

Progress

SQL SELECT COUNT(*) FROM SYSTABLES

Sybase

SQL SELECT 1


Enabling Connection Creation Retries

WebLogic JDBC data sources include the Connection Creation Retry Frequency option (ConnectionCreationRetryFrequencySeconds in the JDBCConnectionPoolParamsBean) that you can use to specify the number of seconds between attempts to establish connections to the database. If set and if the database is unavailable when the data source is created, WebLogic Server attempts to create connections in the pool again after the number of seconds you specify, and will continue to attempt to create the connections until it succeeds. This option applies to connections created when the data source is created at server startup or when the data source is deployed or if the initial capacity is increased. It does not apply to connections created for pool expansion or to replace a defunct connection in the pool.

By default, Connection Creation Retry Frequency is 0 seconds. When the value is set to 0, connection creation retries is disabled and data source creation fails if the database is unavailable.

See the JDBC Data Source: Configuration: Connection Pool page in the Administration Console or see "JDBCConnectionPoolParamsBean" in the Oracle WebLogic Server MBean Reference for more details about this option.

Enabling Connection Requests to Wait for a Connection

JDBC data sources have two attributes that you can set to enable connection requests to wait for a connection from a data source: Connection Reserve Timeout (ConnectionReserveTimeoutSeconds) and Maximum Waiting for Connection (HighestNumWaiters). You use these two attributes together to enable connection requests to wait for a connection without disabling your system by blocking too many threads.

See the JDBC Data Source: Configuration: Connection Pool page in the Administration Console or see "JDBCConnectionPoolParamsBean" in the Oracle WebLogic Server MBean Reference for more details about these options.

Also see "Enable connection requests to wait for a connection" in the Administration Console Online Help.

Connection Reserve Timeout

When an application requests a connection from a data source, if all connections in the data source are in use and if the data source has expanded to its maximum capacity, the application will get a Connection Unavailable SQL Exception. To avoid this, you can configure the Connection Reserve Timeout value (in seconds) so that connection requests will wait for a connection to become available. After the Connection Reserve Timeout has expired, if no connection becomes available, the request will fail and the application will get a PoolLimitSQLException exception.

If you set Connection Reserve Timeout to -1, a connection request will timeout immediately if there is no connection available. If you set Connection Reserve Timeout to 0, a connection request will wait indefinitely. The default value is 10 seconds.

See "Enable connection requests to wait for a connection" in the Oracle WebLogic Server Administration Console Help.

Limiting the Number of Waiting Connection Requests

Connection requests that wait for a connection block a thread. If too many connection requests concurrently wait for a connection and block threads, your system performance can degrade. To avoid this, you can set the Maximum Waiting for Connection (HighestNumWaiters) attribute, which limits the number connection requests that can concurrently wait for a connection.

If you set Maximum Waiting for Connection (HighestNumWaiters) to MAX-INT (the default), there is effectively no bound on how many connection requests can wait for a connection. If you set Maximum Waiting for Connection to 0, connection requests cannot wait for a connection. If the maximum number of requests has been met, a SQLException is thrown when an application requests a connection.

Automatically Recovering Leaked Connections

A leaked connection is a connection that was not properly returned to the connection pool in the data source. To automatically recover leaked connections, you can specify a value for Inactive Connection Timeout on the JDBC Data Source: Configuration: Connection Pool page in the Administration Console. When you set a value for Inactive Connection Timeout, WebLogic Server forcibly returns a connection to the data source when there is no activity on a reserved connection for the number of seconds that you specify. When set to 0 (the default value), this feature is turned off.

See the JDBC Data Source: Configuration: Connection Pool page in the Administration Console or see "JDBCConnectionPoolParamsBean" in the Oracle WebLogic Server MBean Reference for more details about this option.

Note that the actual timeout could exceed the configured value for Inactive Connection Timeout. The internal data source maintenance thread runs every 5 seconds. When it reaches the Inactive Connection Timeout (for example 30 seconds), it checks for inactive connections. To avoid timing out a connection that was reserved just before the current check or just after the previous check, the server gives an inactive connection a "second chance." On the next check, if the connection is still inactive, the server times it out and forcibly returns it to the data source. On average, there could be a delay of 50% more than the configured value.

Avoiding Server Lockup with the Correct Number of Connections

When your applications attempt to get a connection from a data source in which there are no available connections, the data source throws an exception stating that a connection is not available in the data source. To avoid this error, make sure your data source can expand to the size required to accommodate your peak load of connection requests. To increase the maximum number of connections available in the data source, increase the value for Maximum Capacity for the data source on the JDBC Data Source: Configuration: Connection Pool page in the Administration Console.

Limiting Statement Processing Time with Statement Timeout

With the Statement Timeout option on a JDBC data source, you can limit the amount of time that a statement takes to execute on a database connection reserved from the data source. When you set a value for Statement Timeout, WebLogic Server passes the time specified to the JDBC driver using the java.sql.Statement.setQueryTimeout() method. WebLogic Server will make the call, and if the driver throws an exception, the value will be ignored. In some cases, the driver may silently not support the call, or may document limited support. Oracle recommends that you check the driver documentation to verify the expected behavior.

When Statement Timeout is set to -1, (the default) statements do not timeout.

See the JDBC Data Source: Configuration: Connection Pool page in the Administration Console or see "JDBCConnectionPoolParamsBean" in the Oracle WebLogic Server MBean Reference for more details about this option.

Using Pinned-To-Thread Property to Increase Performance

To minimize the time it takes for an application to reserve a database connection from a data source and to eliminate contention between threads for a database connection, you can add the Pinned-To-Thread property in the connection Properties list for the data source, and set its value to true.

When Pinned-To-Thread is enabled, WebLogic Server pins a database connection from the data source to an execution thread the first time an application uses the thread to reserve a connection. When the application finishes using the connection and calls connection.close(), which otherwise returns the connection to the data source, WebLogic Server keeps the connection with the execute thread and does not return it to the data source. When an application subsequently requests a connection using the same execute thread, WebLogic Server provides the connection already reserved by the thread. There is no locking contention on the data source that occurs when multiple threads attempt to reserve a connection at the same time and there is no contention for threads that attempt to reserve the same connection from a limited number of database connections.

Note:

In this release, the Pinned-To-Thread feature does not work with multi data sources, Oracle RAC, and IdentityPool. These features rely on the ability to return a connection to the connection pool and reacquire it if there is a connection failure or connection identity does not match.

See "JDBC Data Source: Configuration: Connection Pool" in the Oracle WebLogic Server Administration Console Help.

Changes to Connection Pool Administration Operations When PinnedToThread is Enabled

Because the nature of connection pooling behavior is changed when PinnedToThread is enabled, some connection pool attributes or features behave differently or are disabled to suit the behavior change:

  • Maximum Capacity is ignored. The number of connections in a connection pool equals the greater of either the initial capacity or the number of connections reserved from the connection pool.

  • Shrinking does not apply to connection pools with PinnedToThread enabled because connections are never returned to the connection pool. Effectively, they are always reserved.

  • When you Reset a connection pool, the reset connections from the connection pool are marked as Test Needed. The next time each connection is reserved, WebLogic Server tests the connection and recreates it if necessary. Connections are not tested synchronously when you reset the connection pool. This feature requires that Test Connections on Reserve is enabled and a Test Table Name or query is specified.

Additional Database Resource Costs When PinnedToThread is Enabled

When PinnedToThread is enabled, the maximum capacity of the connection pool (maximum number of database connections created in the connection pool) becomes the number of execute threads used to request a connection multiplied by the number of concurrent connections each thread reserves. This may exceed the Maximum Capacity specified for the connection pool. You may need to consider this larger number of connections in your system design and ensure that your database allows for additional associated resources, such as open cursors.

Also note that connections are never returned to the connection pool, which means that the connection pool can never shrink to reduce the number of connections and associated resources in use. You can minimize this cost by setting an additional driver parameter onePinnedConnectionOnly. When onePinnedConnectionOnly=true, only the first connection requested is pinned to the thread. Any additional connections required by the thread are taken from and returned to the connection pool as needed. Set onePinnedConnectionOnly using the Properties attribute, for example:

Properties="PinnedToThread=true;onePinnedConnectionOnly=true;user=examples"

If your system can handle the additional resource requirements, Oracle recommends that you use the PinnedToThread option to increase performance.

If your system cannot handle the additional resource requirements or if you see database resource errors after enabling PinnedToThread, Oracle recommends not using PinnedToThread.

Using Unwrapped Data Type Objects

Some JDBC objects from a driver that are returned from WebLogic Server are wrapped by default. Wrapping data source objects provides WebLogic Server the ability to:

WebLogic Server provides the ability to disable wrapping which provides the following benefits:

When wrapping is disabled (the wrap-types element is false), the following data types are not wrapped:

How to Disable Wrapping

You can use the Administration Console and WLST to disable data type wrapping.

Disable Wrapping using the Administration Console

To disable wrapping of JDBC data type objects:

  1. If you have not already done so, in the Change Center of the Administration Console, click Lock & Edit.

  2. In the Domain Structure tree, expand Services, then select Data Sources.

  3. On the Summary of Data Sources page, click the data source name.

  4. Select the Configuration: Connection Pool tab.

  5. Scroll down and click Advanced to show the advanced connection pool options.

  6. In Wrap Data Types, deselect the checkbox to disable wrapping.

  7. Click Save.

  8. To activate these changes, in the Change Center of the Administration Console, click Activate Changes.

    This change does not take effect immediately—it requires that the data source be redeployed or the server be restarted.

Disable Wrapping using WLST

The following is a WLST code snippet to disable data type wrapping:

. . .
jdbcSR = create(dsname,"JDBCSystemResource"); 
theJDBCResource = jdbcSR.getJDBCResource(); 
poolParams = theJDBCResource.getJDBCConnectionPoolParams();
poolParams.setWrapTypes(false); 
. . .

This change does not take effect immediately—it requires that the data source be redeployed or the server be restarted.