Fusion Middleware Documentation
Advanced Search


Administering JDBC Data Sources for Oracle WebLogic Server
Close Window

Table of Contents

Show All | Collapse

17 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:

  • Statement Cache Type—The algorithm that determines which statements to store in the statement cache. See Statement Cache Algorithms.

  • Statement Cache Size—The number of statements to store in the cache for each connection. The default value is 10. See Statement Cache Size.

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 Online 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 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 has a resource cost, such as a cursor, for each open statement. This applies to prepared and callable statements in the statement cache. For example, if you cache too many statements, you may exceed the limit of open cursors on your database server. If you have a data source with 10 connections deployed on 2 servers, and 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.

  • Some drivers impose large memory requirements for every open statement. For a server, memory consumption is based on (number of data sources * number of connections * number of statements).

  • Some DBMSs may impose limits on the number of statements/cursors per connection.

The statement cache size is dependent on your applications. Ideally it is the total number of every prepared or callable statement made with a connection from the DataSource. One way to approximate the maximum size used by your applications is to set the cache size to a huge number, observe the pool statistics of your application, and then take a value slightly larger than the largest observed value. From a WebLogic DataSource perspective, there is no loss in performance for having a cache size larger than your applications require.

However, having a cache size that is too small negatively impacts performance as the cache turnover can be so high while trying to accommodate new statements that old statements are flushed before they are ever reused. In some cases where you cannot allow a big enough statement cache to hold all or most of your statements, you may find may the reuse rate is so small that your system performs better without a statement cache.

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.

Other Considerations

There are several cases where special consideration is needed for the statement cache.

  • A datasource is configured to use DRCP. In this case, the cache is cleared whenever the connection is closed by the application. See Database Resident Connection Pooling.

  • A datasource is configured to use Application Continuity. In this case, the statement cache is closed whenever the connection is replayed. See Application Continuity.

  • oracle.jdbc.implicitstatementcachesize is set in the connection properties of a datasource.

  • For ease of use and to ensure caching is disabled, WebLogic Server automatically sets the statement cache size value to zero (0).

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:

  • Automatic testing that you configure with options on the data source so that WebLogic Server makes sure that database connections remain healthy.

  • Manual testing that you can do to trouble-shoot a data source. 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:

  • Test Frequency—(TestFrequencySeconds in the JDBCConnectionPoolParamsBean) Use this attribute to specify the number of seconds between tests of unused connections. WebLogic Server tests unused connections, and closes and replaces any faulty connections. You must also set the Test Table Name.

  • Test Reserved Connections—(TestConnectionsOnReserve in the JDBCConnectionPoolParamsBean) Enable this option to test each connection before giving to a client. This may add a slight delay to the request, but it guarantees that the connection is healthy. You must also set a Test Table Name.

  • Test Table Name—(TestTableName in the JDBCConnectionPoolParamsBean) Use this attribute to specify a table name to use in a connection test. You can also specify SQL code to run in place of the standard test by entering SQL followed by a space and the SQL code you want to run as a test. Test Table Name is required to enable any database connection testing. A third option is to specify SQL PINGDATABASE when using the Oracle Thin driver; it does a light-weight test of the connection using pingDatabase(). See Oracle Database Connection Testing Using PINGDATABASE.

  • Seconds to Trust an Idle Pool Connection—(SecondsToTrustAnIdlePoolConnection in the JDBCConnectionPoolParamsBean) Use this option to specify the number of seconds after a connection has been proven to be OK that WebLogic Server trusts 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. This option is an optimization that minimizes the performance impact of connection testing, especially during heavy traffic. See Minimizing Connection Request Delay with Seconds to Trust an Idle Pool Connection.

  • Count of Test Failures Till Flush—(CountOfTestFailuresTillFlush in the JDBCConnectionPoolParamsBean) Use this option to specify the number of test failures allowed before WebLogic Server closes all connections in the connection pool to minimize the delay caused by further database testing. This parameter minimizes the amount of time allowed for failover when a Multi Data Source member fails. See Minimized Connection Test Delay After Database Connectivity Loss.

  • Connection Count of Refresh Failures Till Disable—(CountOfRefreshFailuresTillDisable in the JDBCConnectionPoolParamsBean) Use this option to specify the number of test failures allowed before WebLogic Server disables the connection pool to minimize the delay in handling the connection request caused by a database failure. See Minimizing Connection Request Delays After Loss of DBMS Connectivity.

See the JDBC Data Source: Configuration: Connection Pool page in the Administration Console or see "JDBCConnectionPoolParamsBean" in the MBean Reference for Oracle WebLogic Server 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 Online Help.

The following section discusses automatic connection testing options:

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 complete a test, 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. A pool closes all in-test or unused connections, and flags in-use connections to check them later as they may be hanging. After the Test Frequency Seconds has passed, WebLogic Server kills any in-use connections that have not progressed.

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).

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 count(*) 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 sting 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.

To minimize the delay that occurs during the test of dead database connections, you can set CountOfTestFailuresTillFlush attribute on the connection pool. To enable this feature, TestConnectionsOnReserve must also be set to true.

If the configured or default number of consecutive connection test failures are observed, all currently unused connections in the pool are destroyed so any subsequent connection requests get a new connection. Active connections are not interrupted but are monitored for activity. If no activity is detected with in 60 seconds, these connections are destroyed.

The default value is generally sufficient. You may need to increase this value if your environment has:

  • Slow-running applications that may not show JDBC activity for several minutes

  • Network/firewall issues that consistently kill one or two connections

Minimizing 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, in cases where the DBMS is truly down, it may be minutes, hours, or days before the DBMS is restored. Testing a dead database connection can take as long as the network timeout, and can cause a long delay for clients. This delay occurs for each dead connection in the connection pool until all connections are replaced and can cause long delays to clients before getting the expected failure message.

To minimize the delay that occurs for client applications while a database is unavailable, you can set the CountOfRefreshFailuresTillDisable attribute on the connection pool. The default value is 2. To enable this feature, TestConnectionsOnReserve must also be set to true and InitialCapacity must be greater than 0.

If the configured or default number of consecutive failures to replace a dead connection are observed, WebLogic Server suspends the connection pool. If an application requests a connection from a suspended connection pool, WebLogic Server throws PoolDisabledSQLException 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 or WLST.

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. Configure using test frequency seconds at the multi data source level. Note that the semantics of this setting are different than at the data source level.

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 Online 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.

When running with AGL and FAN enabled:

  • It is not necessary to run with Test Connections on Reserve because ONS will send down events when a database instance goes down. This can significantly improve performance by eliminating (or reducing) testing overhead in the database. However, Test Connections on Reserve tests for other failures such as network connectivity and application access to the database. Oracle recommends running with Test Connections on Reserve and using SecondsToTrustAnIdlePoolConnection and/or TestFrequencySeconds to reduce the overhead.

  • CountOfTestFailuresTillFlush and CountOfRefreshFailuresTillDisable are ignored. The disabling an entire RAC instance occurs when a FAN event is received that indicates that the instance is down.

Database Connection Testing Using 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 17-1 Default Test Table Name by DBMS

DBMS Default Test Table Name (Query)

DB2

SQL SELECT COUNT(*) FROM SYSIBM.SYSTABLES

Microsoft SQL Server

SQL SELECT 1

MySQL

SQL SELECT 1

Oracle

SQL SELECT 1 FROM DUAL

Sybase

SQL SELECT 1


Oracle Database Connection Testing Using PINGDATABASE

For applications using an Oracle data base, particularly those with Oracle RAC environments, you can improve the connection testing performance of your data source by setting the Test Table Name attribute of the connection pool to SQL PINGDATABASE. Although it is not as thorough as using the default value SQL SELECT 1 FROM DUAL, it can significantly eliminate processing overhead and improve SOA workload performance.

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 MBean Reference for Oracle WebLogic Server 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 MBean Reference for Oracle WebLogic Server 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 Online 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 MBean Reference for Oracle WebLogic Server 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 Oracle WebLogic Server Administration Console Online Help.

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 Oracle WebLogic Server Administration Console Online Help 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 set the Pinned To Thread option on the JDBC data source to true. See the JDBC Data Source: Configuration: Connection Pool page in the Oracle WebLogic Server Administration Console Online Help.

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:

The Pinned To Thread feature does not work with an IdentityPool. Starting with WebLogic Server release 12.1.2, configurations with this combination will cause the datasource to fail to deploy.

See "JDBC Data Source: Configuration: Connection Pool" in the Oracle WebLogic Server Administration Console Online 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.

Consider the following when using the PinnedToThread feature:

  • If used with Identity Based Connection Pooling Enabled set to true, an error is thrown and the data source will not deploy.

  • When used with Use Database Credentials set to true, all connections are owned by the default user as defined in the JDBC descriptor but the Oracle proxy is set to the user and password specified on getConnection(user, password). Similarly, with Oracle Proxy set to true, the user and password are mapped to a database credential and the Oracle proxy is set. This is the same behavior as without PinnedToThread.

  • Connection labeling is not supported when using PinnedToThread and an exception is thrown when trying to get a connection with label properties.

  • When using Multi-datasource, connections are maintained by each member datasource as they are selected by the multi-datasource. For example, with Algorithm Type of Failover, connections are initially be maintained only for the primary member of MDS. If a failover occurs, then connections are maintained for the next member of the MDS. When used with the Algorithm Type of Load-Balancing, connections are maintained for each member of the MDS.

  • When using Active GridLink, Affinity and Runtime Load Balancing continue to work as before with regard to choosing an instance. As many as one connection is stored per instance per thread (the equivalent of setting OnePinnedConnectionOnly=true but on a per instance basis). Gravitation is not supported (no migration of connections to lightly used nodes).

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="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:

  • Generate debugging output from all method calls.

  • Track connection utilization so that connections can be timed out appropriately.

  • Provide transparent automatic transaction enlistment and security authorization.

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

  • Although WebLogic Server generates a dynamic proxy for vendor methods that implement an interface to show through the wrapper, some data types do not implement an interface. For example, Oracle data types Array, Blob, Clob, NClob, Ref, SQLXML, and Struct are classes that do not implement interfaces. Disabling wrapping allows applications to use native driver objects directly.

    Note:

    Oracle recommends not using these concrete classes and instead using standard SQL types or corresponding Oracle interfaces. See "Using API Extensions for Oracle JDBC Types" in Developing JDBC Applications for Oracle WebLogic Server.

  • Eliminating wrapping overhead can provide a significant performance improvement.

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

  • Array

  • Blob

  • Clob

  • NClob

  • Ref

  • SQLXML

  • Struct

  • ParameterMetaData

    • No connection testing performed.

  • ResultSetMetaData

    • No connection testing performed.

    • No result set testing performed.

    • No JDBC MT profiling performed.

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.

Tuning Maintenance Timers

The following section provides information on tunable timer properties used by WebLogic JDBC:

  • weblogic.jdbc.gravitationShrinkFrequencySeconds—Connections may be shut down periodically on GridLink data sources. If the connections allocated to various RAC instances do not correspond to the Runtime Load Balancing percentages in the FAN load-balancing advisories, connections to overweight instances are destroyed and new connections opened. This process occurs every 30 seconds by default. You can tune this behavior using the weblogic.jdbc.gravitationShrinkFrequencySeconds system property which specifies the amount of time, in seconds, the system waits before rebalancing connections. A value less than or equal to 0 disables the rebalancing process.

  • weblogic.jdbc.harvestingFrequencySeconds—Connection harvesting releases reserved connections that are marked harvestable by the application when a data source falls to a specified number of available connections. This check by default is done every 30 seconds. This system property can be used to change the frequency of harvesting by Data Source the amount of time, in seconds. If set less than or equal to 0, connection harvesting is turned off. See Recover Harvested Connections.

  • weblogic.jdbc.securityCacheTimeoutSeconds—Performance is impacted when reserving connections from a connection pool, due to the credentials for the WebLogic server user being checked for each reserve connection request. To resolve this, checking can be controlled by this system property. If less than or equal to zero, the cache is turned off and user authentication happens each time. If greater than zero, user authentication is done only once for each user in the specified time period in seconds; the value is then cached. In situations where pool access restrictions are dynamically altered, the pool re-authenticates the users once each time after the cache is cleared. The default value is 5 seconds.