13 Tuning Data Sources

This chapter provides tips on how to get the best performance from your WebLogic data sources.

Data Source Administration

"Tuning Data Source Connection Pool Options" in Administering JDBC Data Sources for Oracle WebLogic Server has many suggestions for improving data source performance. The following is a quick summary:

  • Use the statement cache to avoid the processing overhead of preparing statements.

  • Testing connections on reserve is important to avoid getting an invalid connection in the application. However, use options like Seconds to Trust an Idle Pool Connection to reduce the overhead. There are related parameters to help control when a data source is suspended.

  • Use Pinned-to-Thread to eliminate contention between threads getting a database connection and improve performance.

  • Turn off wrapping for Oracle data types.

  • Although a best practice for an application is to explicitly close a connection as soon as it is finished using the connection, enabling connection leak detection can return it to the pool automatically.

Waste Not

Another simple way to boost performance is to avoid wasting resources. Here are some situations where 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.

    Note:

    When using Active GridLink (AGL) data sources, holding connections for long periods of time can circumvent runtime load balancing (RLB).

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

Database Listener Timeout under Heavy Server Loads

In some situations where WebLogic Server is under heavy loads (high CPU utilization), 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

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 Oracle proxy authentication, Active GridLink and Database Resident Connection Pool. Active GridLink provides transaction and web session affinity to improve performance. See "Advanced Configurations for Oracle Drivers and Databases" in Administering JDBC Data Sources for Oracle WebLogic Server.

Additional Oracle Features

Oracle provides additional advanced features:

  • Connection Labeling can significantly improve performance when reserving connections if application initialization is expensive. See "Labeling Connections" in Administering JDBC Data Sources for Oracle WebLogic Server.

  • For applications that require more control of connection pooling, see "Connection Harvesting" 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.