By Laurent Goldsztejn on Sep 16, 2013
In this post you will find some common best practices aimed at ensuring high levels of performance with WebLogic JDBC. However, rather than just throwing some tips, I will detail why each recommendation is beneficial to JDBC and Weblogic Server performance.
- Use Oracle JDBC thin driver (Type 4) rather than OCI driver (Type 2)
The Oracle JDBC thin driver is lightweight (easy to install and administrate), platform-independent (entirely written in Java), and provides slightly higher performance than the JDBC OCI (Oracle Call Interface) driver. The thin driver does not require any additional software on the client side. Oracle JDBC FAQ stipulates that the performance benefit with the thin driver is not consistent and that the OCI driver can even deliver better performances in some scenarios.
- Use PreparedStatements objects rather than Statement
- Close all JDBC resources in a finally Block
This include ResultSet, Connection, Statement and Prepared Statement objects and to avoid potential memory leaks. The connection.close() won't necessarily automatically clean up all the other objects because the implementation of close() may differ between JDBC drivers. Also, JDBC objects not properly closed could lead to this error:
java.sql.SQLException: ORA-01000: maximum open cursors exceeded.
If you don't explicitly close Statements and ResultSets right away, cursors may accumulate and exceed the maximum number allowed in your DB before the Connection is closed.
- Set Shrink frequency to 0 in production environments
By disabling Shrink frequency you will not allow waits before shrinking a connection pool that has incrementally increased to meet demand. The Shrink Frequency parameter is used to specify the number of seconds to wait before shrinking a connection pool, given that ShrinkingEnabled is kept at its default value, or set to true. Weblogic shrinks a connection pool by reducing the number of connections to the greater of either the minimum capacity or the number of connection in use and thus frees up some resources. In development we can afford to keep the no-longer-used connection active rather than immediately returning them to the pool.
- Consider skipping the SQL-query test when Test Connections on Reserve is enabled
When Test Connections on Reserve is enabled (see Advanced Connection Pool Configuration in the console), the Weblogic Server checks a database connection prior to returning the connection to a client to avoid passing an invalid connection in the application. Since this operation could impact the performance, it's recommended to use Seconds to Trust an Idle Pool Connection (set to 10 seconds by default) that defines how long WebLogic Server will trust the connection and therefore skip the connection test after a connection has been proven valid.
- Enable Pinned-to-Thread
- Ensure that Maximum Thread Constraint property doesn't exceed the number of database connection
This property (See Environment Work Manager in the console) will set a maximum number of possible concurrent requests. If it exceeds the number of database connection then some threads might end up waiting until existing database connection are made available.
Visit Tuning Data Source Connection Pools and Tuning Data Sources for additional parameters tuning in JDBC data sources and connection pools to improve system performance with Weblogic Server 12.1.2, and Performance Tuning Your JDBC Application for application-specific design and configuration.