18 Tuning Data Source Connection Pools
- Increasing Performance with the Statement Cache
Reusing cached statements reduces CPU usage on the database server, improving performance for the current statement and leaving CPU cycles for other tasks. Cache configurations options include Statement Cache Type and Statement Cache size. - Initial Capacity Enhancement in the Connection Pool
Connection retry, early failure, and critical data sources are available from WebLogic Server 12.2.1.3, to enhance the initial capacity connections in the connection pool. - Connection Testing Options for a Data Source
Learn about testing the database connections for a data source using the Automatic and Manual testing methods. - Enabling Connection Creation Retries
WebLogic JDBC data sources offer the Creation Retry Frequency option, which sets the number of seconds between attempts to establish connections to the database. If you do not set this value, data source creation fails if the database is unavailable. If set and if the database is unavailable when the data source is created, WebLogic Server will attempt 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. - 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
). - Automatically Recovering Leaked Connections
You can automatically recover leaked connection by specifying values forInactive Connection Timeout
on the JDBC Data Source: Configuration: Connection Pool page in the WebLogic Server Administration Console. - Avoiding Server Lockup with the Correct Number of Connections
To avoid receiving an error while attempting to get a connection from a data source in which there are no available connections, make sure your data source can expand to the size required to accommodate your peak load of connection requests. - 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. - 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 thePinned To Thread
option on the JDBC data source totrue
. - Using Unwrapped Data Type Objects
Disabling wrapping allows applications to use native driver objects directly to provide a significant performance improvement. - Tuning Maintenance Timers
Learn about the tunable timer propertiesweblogic.jdbc.gravitationShrinkFrequencySeconds
weblogic.jdbc.harvestingFrequencySeconds
andweblogic.jdbc.securityCacheTimeoutSeconds
used by WebLogic JDBC. - JDBC Connection Creation Limits
Some databases place a limit on the rate of JDBC connections that can be created. For example, 100 connections per second. For large WebLogic Server deployments this rate can be exceeded during server startup, database rolling restarts, failover events, and so on. Even with databases that do not enforce a rate limit, many simultaneous JDBC connection requests can potentially overwhelm the database capacity.
Increasing Performance with the Statement Cache
Reusing cached statements reduces CPU usage on the database server, improving performance for the current statement and leaving CPU cycles for other tasks. Cache configurations options include Statement Cache Type and Statement Cache size.
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 WebLogic Server 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.
Parent topic: Tuning Data Source Connection Pools
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.
Parent topic: Statement Cache Algorithms
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.
Parent topic: Statement Cache Algorithms
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.
Parent topic: Increasing Performance with the 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
- Using setNull In a Prepared Statement
- Statements in the Cache May Reserve Database Cursors
- Other Considerations When Using the Statement Cache
Parent topic: Increasing Performance with the Statement Cache
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.
Parent topic: Usage Restrictions for the Statement Cache
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)
Parent topic: Usage Restrictions for the Statement Cache
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.
Parent topic: Usage Restrictions for the Statement Cache
Other Considerations When Using the Statement Cache
When oracle.jdbc.implicitstatementcachesize
is set in the
connection properties of a data source, the WebLogic Server statement cache size is
automatically set to zero (0).
There are several cases where special consideration is needed for the statement cache.
-
If a data source is configured to use DRCP, the cache is cleared whenever the connection is closed by the application. See Database Resident Connection Pooling.
-
When a data source is configured to use JDBC Replay Driver using the JDBC Replay Driver driver, the WebLogic Server statement cache size is automatically set to 0.
-
oracle.jdbc.implicitstatementcachesize
is set in the connection properties of a data source. -
For ease of use and to ensure caching is disabled, WebLogic Server automatically sets the statement cache size value to zero (0).
-
When the JDBC 4.0
setPoolable(false)
method is called for a WebLogic data source that has prepared statement caching enabled, the statement is removed from the cache in addition to calling the method on the driver object.
Parent topic: Usage Restrictions for the Statement Cache
Initial Capacity Enhancement in the Connection Pool
Connection retry, early failure, and critical data sources are available from WebLogic Server 12.2.1.3, to enhance the initial capacity connections in the connection pool.
Creating the Initial Capacity Connections in the Connection Pool
Whenever a server starts, the data source tries to create the initial capacity connections in the connection pool. Prior to 12.2.1.3, the data source attempted to create initial capacity connections even if some of the connection attempts failed. This can take a long time if one or more of the connection failures take a long time due to unavailability of network or database.
Connection Retry
There are two connection properties that control retrying the initial connection creation failure:
weblogic.jdbc.startupRetryCount
— If this property is set and the value is greater than 0, if failure occurs connection creation will be retried based on the value. The default value is 0 (no retry).
weblogic.jdbc.startupRetryDelaySeconds
— If this property is set and the value is greater than 0 and retry count is set, the connection creation will delay for the specified number of seconds between retries. The default value is 0 (no delay).
Early Failure
The following connection property controls whether or not to continue after connection creation fails:
weblogic.jdbc.continueMakeResourceAttemptsAfterFailure=true
— If startup retry is enabled, the driver property weblogic.jdbc.continueMakeResourceAttemptsAfterFailure=true
is ignored and the data source will not continue to create connections after a failure when the server is starting. It will continue create attempts if the data source is deployed or redeployed on a running server.
Critical Data Sources
If a failure occurs while populating the initial capacity connections in the connection pool, the data source is not deployed (it won't be in JNDI so the application will fail to find it) but the server continues to startup and is not marked as unhealthy. In some applications, a data source may be a critical resource such that no useful processing can be done if the data source is not deployed. This can be controlled using a connection property:
weblogic.jdbc.critical
— If this value is set to true, the managed server fails to boot; this does not apply to the administration server, which is available to process configuration changes. The default value is false, where the server continues to boot without deploying the data source.
Example 18-1 WLST Sample Code
edit()
startEdit()
datasource="dsname"
cd("/JDBCSystemResources/" + datasource + "/JDBCResource/" + datasource + "/JDBCDriverParams/"
+ datasource + "/Properties/" + datasource)
cmo.createProperty("weblogic.jdbc.startupRetryCount", "5")
cmo.createProperty("weblogic.jdbc.startupRetryDelaySeconds", "10")
save()
activate()
Parent topic: Tuning Data Source Connection Pools
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 WebLogic Server Administration Console or through WLST using the JDBCConnectionPoolParamsBean
:
-
Test Frequency—(
TestFrequencySeconds
in theJDBCConnectionPoolParamsBean
) 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 theJDBCConnectionPoolParamsBean
) 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 theJDBCConnectionPoolParamsBean
) 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 enteringSQL
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. See Database Connection Testing Using Default Test Table Name. -
Seconds to Trust an Idle Pool Connection—(
SecondsToTrustAnIdlePoolConnection
in theJDBCConnectionPoolParamsBean
) 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 theJDBCConnectionPoolParamsBean
) 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 Minimizing Connection Test Delay After Database Connectivity Loss. -
Connection Count of Refresh Failures Till Disable—(
CountOfRefreshFailuresTillDisable
in theJDBCConnectionPoolParamsBean
) 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 WebLogic Server 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.
Automatic connection testing options are:
- Database Connection Testing Semantics
- Database Connection Testing Configuration Recommendations
- Database Connection Testing Using Default Test Table Name
- Database Connection Testing Options
Parent topic: Tuning Data Source Connection Pools
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 Second
s 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 explained in the following topics:
- Connection Testing When Database Connections are Created
- Periodic Connection Testing
- Testing Reserved Connections
- Minimizing Connection Test Delay After Database Connectivity Loss
- Minimizing Connection Request Delays After Loss of DBMS Connectivity
- Minimizing Connection Request Delay with Seconds to Trust an Idle Pool Connection
Parent topic: Connection Testing Options for a Data Source
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.
Parent topic: Database Connection Testing Semantics
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.
Parent topic: Database Connection Testing Semantics
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.
Parent topic: Database Connection Testing Semantics
Minimizing 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 terminated. If the data source is configured to test connections on reserve, then when an application requests a database connection, WebLogic Server tests the connection, discovers that the connection is terminated, 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 terminated connection can impose a long delay.
To minimize this delay, WebLogic data sources include logic that considers all connections in the data source as terminated 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 terminated 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
CountOfTestFailuresTillFlush
.Note:
The default value is 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.
Note:
This value is overridden byCountOfTestFailuresTillFlush
value. Actually, the number of test failures before closing all connections follows the count of test failures till flush, that is,CountOfTestFailuresTillFlush
, which is located inConnection Pool
parameter of WebLogic Server Administration Console.
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, then all currently unused connections in the pool are terminated so that 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 terminate one or two connections
Parent topic: Database Connection Testing Semantics
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 WebLogic Server 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.
Parent topic: Database Connection Testing Semantics
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.
Parent topic: Database Connection Testing Semantics
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 withTest Connections on Reserve
and usingSecondsToTrustAnIdlePoolConnection
and/orTestFrequencySeconds
to reduce the overhead. -
CountOfTestFailuresTillFlush
andCountOfRefreshFailuresTillDisable
are ignored. The disabling an entire RAC instance occurs when a FAN event is received that indicates that the instance is down.
Parent topic: Connection Testing Options for a Data Source
Database Connection Testing Using Default Test Table Name
When you create a data source using the WebLogic Server Administration Console, the WebLogic Server 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 WebLogic Server queries to test a connection. Setting it to any table, such as "DUAL" for Oracle, causes 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 WebLogic Server pool connection testing, and to make the test as fast as possible.
Table 18-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 ISVALID |
Sybase |
SQL SELECT 1 |
Parent topic: Connection Testing Options for a Data Source
Database Connection Testing Options
For applications using an Oracle data base, particularly those with Oracle RAC environments, using the default value of the Test Table Name
attribute provides the best overall performance.
Oracle continues to support SQL PINGDATABASE
and SQL SELECT 1 FROM DUAL
. Although not as thorough as using SQL SELECT 1 FROM DUAL
, SQL ISVALID
significantly eliminate processing overhead and improve SOA workload performance.
Parent topic: Connection Testing Options for a Data Source
Enabling Connection Creation Retries
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 JDBC Data Source: Configuration: Connection Pool page in the WebLogic Server Administration Console or see JDBCConnectionPoolParamsBean in the MBean Reference for Oracle WebLogic Server .
Parent topic: Tuning Data Source Connection Pools
Enabling Connection Requests to Wait for a Connection
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.
Parent topic: Tuning Data Source Connection Pools
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.
Parent topic: Enabling Connection Requests to Wait for a Connection
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.
See JDBC Data Source: Configuration: Connection Pool page in the WebLogic Server Administration Console Online Help and JDBCConnectionPoolParamsBean in the MBean Reference for Oracle WebLogic Server.
Parent topic: Enabling Connection Requests to Wait for a Connection
Automatically Recovering Leaked Connections
You can automatically recover leaked connection by specifying values for
Inactive Connection Timeout
on the JDBC Data Source:
Configuration: Connection Pool page in the WebLogic Server Administration
Console.
A leaked connection is a connection that was not properly returned to the connection pool in the data source. 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.
For more details about this option, see JDBC Data Source: Configuration: Connection Pool page in the Oracle WebLogic Server Administration Console Online Help or see JDBCConnectionPoolParamsBean in the MBean Reference for Oracle WebLogic Server.
Note:
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.Parent topic: Tuning Data Source Connection Pools
Avoiding Server Lockup with the Correct Number of Connections
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.
Parent topic: Tuning Data Source Connection Pools
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.
Parent topic: Tuning Data Source Connection Pools
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
.
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
- Additional Database Resource Costs When PinnedToThread is Enabled
Parent topic: Tuning Data Source Connection Pools
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 totrue
, an error is thrown and the data source will not deploy. -
When used with
Use Database Credentials
set totrue
, 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 ongetConnection(user, password)
. Similarly, withOracle Proxy
set totrue
, the user and password are mapped to a database credential and the Oracle proxy is set. This is the same behavior as withoutPinnedToThread
. -
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 Data Source (MDS), connections are maintained by each member data source as they are selected by the MDS. 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 ofLoad-Balancing
, connections are maintained for each member of the MDS. -
When using Active GridLink,
Affinity
andRuntime 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 settingOnePinnedConnectionOnly=true
but on a per instance basis). Gravitation is not supported (no migration of connections to lightly used nodes).
Parent topic: Using Pinned-To-Thread Property to Increase Performance
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
.
Parent topic: Using Pinned-To-Thread Property to Increase Performance
Using Unwrapped Data Type Objects
Disabling wrapping allows applications to use native driver objects directly to provide a significant performance improvement.
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
-
Parent topic: Tuning Data Source Connection Pools
How to Disable Wrapping
You can use the WebLogic Server Administration Console and WLST to disable data type wrapping.
Parent topic: Using Unwrapped Data Type Objects
Disable Wrapping using the Administration Console
To disable wrapping of JDBC data type objects:
Parent topic: How to Disable Wrapping
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.
Parent topic: How to Disable Wrapping
Tuning Maintenance Timers
Learn about the tunable timer properties weblogic.jdbc.gravitationShrinkFrequencySeconds
weblogic.jdbc.harvestingFrequencySeconds
and weblogic.jdbc.securityCacheTimeoutSeconds
used by WebLogic JDBC.
-
weblogic.jdbc.gravitationShrinkFrequencySeconds
—Connections may be shut down periodically on Active 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 theweblogic.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 10 minutes.
Parent topic: Tuning Data Source Connection Pools
JDBC Connection Creation Limits
Some databases place a limit on the rate of JDBC connections that can be created. For example, 100 connections per second. For large WebLogic Server deployments this rate can be exceeded during server startup, database rolling restarts, failover events, and so on. Even with databases that do not enforce a rate limit, many simultaneous JDBC connection requests can potentially overwhelm the database capacity.
To avoid JDBC driver connection errors, the data source connection property
weblogic.jdbc.maxConcurrentCreateRequests
can be used to limit the
number of concurrent connection-create operations. The
weblogic.jdbc.concurrentCreateRequestsTimeoutSeconds
property can
be used to specify how long a connection create request waits (60 seconds by default)
for a permit to proceed.
Parent topic: Tuning Data Source Connection Pools