10 Tuning Data Sources

To get the best performance from your Oracle WebLogic Server data sources, use the recommended tips to tune the data sources.

Tune the Number of Database Connections

Creating a database connection is a relatively expensive process in any environment. A straightforward and easy way to boost performance of a data source in WebLogic Server applications is to set the value of Initial Capacity equal to the value for Maximum Capacity when configuring connection pools in your data source.

Typically, a connection pool starts with a small number of connections. As client demand for more connections grow, there may not be enough in the pool to satisfy the requests. WebLogic Server creates additional connections and adds them to the pool until the maximum pool size is reached.

One way to avoid connection creation delays for clients using the server is to initialize all connections at server startup, rather than on-demand as clients need them. Set the initial number of connections equal to the maximum number of connections in the Connection Pool tab of your data source configuration. See JDBC Data Source: Configuration: Connection Pool in the Oracle WebLogic Server Administration Console Online Help. You will still need to determine the optimal value for the Maximum Capacity as part of your pre-production performance testing.

Note that if you configure the value of Initial Capacity to be zero, WebLogic Server does not get a connection during startup. This provides a big startup performance gain, especially if several data sources are available. But more importantly, it allows the data source to be deployed on startup, even if the database is not available or has problems at startup (or it could be a standby data source that is not even available when the primary service is running).

There are two situations in which a connection is reserved, even if Initial Capacity is zero:

  1. For a multi data source configured for LLR, a connection is reserved on each member data source to determine if the underlying database is an Oracle Real Application Clusters (Oracle RAC) database. If it is Oracle RAC, only one of the member data sources must be available.

  2. For an Active GridLink (AGL) data source configured with auto-ONS(that is, with no ONS host and port pairs provided), a connection is created to get the ONS configuration information from the database.

See Tuning Data Source Connection Pool Options in Administering JDBC Data Sources for Oracle WebLogic Server.

Waste Not

Another simple way to boost performance is to avoid wasting resources. Read about situations in which you can avoid wasting JDBC related resources.

  • JNDI lookups are relatively expensive, so caching an object that required a looked-up in client code or application code avoids incurring this performance hit more than once.

  • Once client or application code has a connection, maximize the reuse of this connection rather than closing and reacquiring a new connection. While acquiring and returning an existing creation is much less expensive than creating a new one, excessive acquisitions and returns to pools creates contention in the connection pool and degrades application performance.

  • Don't hold connections any longer than is necessary to achieve the work needed. Getting a connection once, completing all necessary work, and returning it as soon as possible provides the best balance for overall performance.

Use Test Connections on Reserve with Care

When Test Connections on Reserve is enabled, the server instance checks a database connection prior to returning the connection to a client. This reduces the risk of passing invalid connections to clients.

However, it is a fairly expensive operation. Typically, a server instance performs the test by executing a full-fledged SQL query with each connection prior to returning it. If the SQL query fails, the connection is destroyed and a new one is created in its place. A new and optional performance tunable has been provided in WebLogic Server 9.x within this "test connection on reserve" feature. The new optional performance tunable in 9.x allows WebLogic Server to skip this SQL-query test within a configured time window of a prior successful client use (default is 10 seconds). When a connection is returned to the pool by a client, the connection is timestamped by WebLogic Server. WebLogic Server will then skip the SQL-query test if this particular connection is returned to a client within the time window. Once the time window expires, WebLogic Server will execute the SQL-query test. This feature can provide significant performance boosts for busy systems using "test connection on reserve".

Cache Prepared and Callable Statements

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. See Increasing Performance with the Statement Cache in Administering JDBC Data Sources for Oracle WebLogic Server.

Using the statement cache can dramatically increase performance, but you must consider its limitations before you decide to use it. See Usage Restrictions for the Statement Cache in Administering JDBC Data Sources for Oracle WebLogic Server.

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, add the Pinned-To-Thread property in the connection Properties list for the data source, and set its value to true.

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

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

Database Listener Timeout under Heavy Server Loads

In some situations where WebLogic Server is under heavy loads, the database listener may timeout and throw an exception while creating a new connection. To workaround this issue, increase the listener timeout on the database server.

The following example is for an Oracle driver and database:

  • The exception thrown is a ResourceDeadException and the driver exception was Socket read timed out.

  • The workaround is to increase the timeout of the database server using the following:

    sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180

    listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=180

Disable Wrapping of Data Type Objects

By default, data type objects for Array, Blob, Clob, NClob, Ref, SQLXML, and Struct, plus ParameterMetaData and ResultSetMetaData objects are wrapped with a WebLogic wrapper. You can disable wrapping of data type objects.

See Using Unwrapped Data Type Objects in Administering JDBC Data Sources for Oracle WebLogic Server.

Advanced Configurations for Oracle Drivers and Databases

Oracle provides advanced configuration options that can provide improved data source and driver performance when using Oracle drivers and databases. Options include proxy authentication, setting credentials on a connection, connection harvesting, and labeling connections.

See Advanced Configurations for Oracle Drivers and Databases in Administering JDBC Data Sources for Oracle WebLogic Server.

Use Best Design Practices

Most performance gains or losses in a database application is not determined by the application language, but by how the application is designed. The number and location of clients, size and structure of DBMS tables and indexes, and the number and types of queries all affect application performance.

See Designing Your Application for Best Performance in Developing JDBC Applications for Oracle WebLogic Server.