Oracle Weblogic : Tuning JDBC Data Source parameters
In this post , we will cover some of the parameters and factors which affect JDBC performance in Weblogic Server. But first I will like to show how jdbc connections are made (in Snaps below )and for that we need to understand two terms Data Sources and Connection Pool.
Data sources:
Are administered factory Objects that provide JDBC connections.
Are bound into Java naming and Directory Interface (JNDI) and configure using the Administration console.
Make the application code Portal Across databases.
– Creating a connection to the database is an expensive operation.
Connection pools:
– Remove the overhead of establishing connections.
-Improve server performance by sharing database connections among multiple users accessing the Web application.
A simple Way to boost JDBC application performance and avoid Wasting resources:
1. JNDI lookups are relatively expensive. So caching an object that requires a lookup in client.
code or application code avoids incurring additional performance cost.
2. When client or application code has a connection, maximize the reuse of this connection rather than closing and reacquiring a new connection. Although 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.
3. Do not 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.
Parameters that Affect JDBC Data Source performance (which can be changed using Weblogic console) :
1. Connection Pool Capacity (Most Important) :
o Connection creation is expensive.
o For applications that consistently involve heavy database traffic:
Determine the optimal Maximum Capacity of a data source experimentally
Set the lnitial Capacity and Maximum Capacity to the same value.
o For applications, where peak database load is intermittent:
Use different values for initial and maximum sizes.
Tune Capacity increment and Shrink Frequency on the basis of load changes.
In my Environment, I have kept Minimum value as 20 and Maximum as 100. Minimum cant be kept too high as there will be extra overhead because of this.
2. Connection testing :
– The WebLogic Server can test a connection from the connection pool before giving it to a client.
– Test Connection On Reserve parameter enables automatic testing of database connection.
– Connections can also be tested periodically for validity by using the Test Frequency parameter.
– Both these parameters can degrade performance (So , in production instances Avoid this)
3. Shrink Frequency
– The WebLogic Server periodically shrinks the connection pool to its initial capacity based on usage.
– The Shrink Frequency parameter is used to specify the number of seconds to wait before shrinking a connection pool.
– When set to Zero 0, shrinking is disabled. This can be helpful in a production environment.
4. Configuring Row Pre Fetch
-Row prefetching improves performance by fetching multiple rows from the sen/er to the client in one server access.
– The optimal prefetch size depends on the particulars of the query.
– In general, increasing this number will increase performance, until a particular value is reached.
Note: This is applicable only for external clients, not for clients in the same Java Virtual Machine (JVM) as the WebLogic Server.
Very rarely will increased performance result from Exceeding 100 rows.
Max value is 65536 and minimum value is 2
5. Statement caching:
The three types of statements in JDBC are:
-Statements (not cached)
-Prepared statements
-Callable statements
The prepared statements and callable statements are Cacheable and improve overall performance through reuse.
– The statement cache type determines how the cache is refreshed:
LRU: Replaces least recently used statement with new .
FIXED: no replacement is done
6. 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 will maintain a cursor for each open statement. This applies to prepared and callable statements in the statement cache. If you cache too many statements, you may exceed the limit of open cursors on your database server. Setting the size of the statement cache to 0 turns off statement caching.
-Minimum value: 0
-Maximum value: 1024
7. Connection Pinned to Thread:
– A data source can dedicate or “pin” a connection to the first server thread that requests it.
– This capability:
– May increase performance by eliminating potential contention for connections by threads
– ls not supported with multidata sources or Oracle Real Application Clusters (RAC)
8. Inactive Connection Timeout:
The number of inactive seconds on a reserved connection before WebLogic Server reclaims the connection and releases it back into the connection pool.
You can use the Inactive Connection Timeout feature to reclaim leaked connections – connections that were not explicitly closed by the application. Note that this feature is not intended to be used in place of properly closing connections.
When set to 0, the feature is disabled.
Things to Consider on Oracle Database Instance side:
Number of processes
-It includes simultaneous users and Oracle background processes.
-The default setting is usually too low.
Shared pool size
-It contains data and control information for each instance.
-lt is critical to performance.
-Even the moderate use of stored procedures and triggers may require an increase.
Maximum opened cursor
-It should be increased if you are using JDBC statement caching.
There are several other Factors which affect JDBC performance which i will try to cover later , the ones discussed above are primary and important . Although Performance issues differ from environment to environment and may be due to some other reasons or factors (which are not mentioned above) , but i hope understanding these parameters will certainly help.
Learn Oracle Weblogic Server Administration
Get 100 USD OFF + 100% Money Back Guarantee