IBM Support

Memory utilization considerations when using prepared statement caching

Troubleshooting


Problem

Currently published recommendations for setting Prepared Statement Cache sizes for IBM® WebSphere® Application Server might result in very large Application Server memory footprints, and in extreme cases, OutOfMemory errors or crashes of the Java™ process.  
These are memory utilization considerations needed for applications with very large prepared statement cache sizes.

Resolving The Problem

Overview of Prepared Statement Caching
WebSphere Application Server provides a mechanism for caching and reusing prepared statement objects. Caching prepared statements improves overall response times because an application can reuse a PreparedStatement on a connection if it exists in that connection's cache, bypassing the need to create a new PreparedStatement.
When an application creates a PreparedStatement on a connection, the connection's cache is first searched to determine if a PreparedStatement with the same SQL string already exists. If a match is found, the cached PreparedStatement is returned for use. If it is not, a new PreparedStatement is created and returned to the application.  When a prepared statement is closed by the application, it is returned to the connection's cache of statements, rather than being completely closed.
Elements are removed from the connection's cache of prepared statements only when the number of currently cached prepared statements exceeds the configured statement cache limit for the connection.  When the cache limit is reached, the least recently used statement is removed from the cache to make room for the new statement.  Because in use prepared statements are not considered part of the prepared statement cache, the total number of statements open per connection can actually be slightly higher than the configured statement cache size.  For example, if the prepared statement cache is configured to ten statements per connection and the application has up to two prepared statements in use at a time, the true upper limit on the prepared statements per connection is twelve.
The number of prepared statements to hold in the cache is configurable on the data source. Each cache must be tuned according to the application's need for prepared statements.  The prepared statement cache value is the number of prepared statements to cache for each connection; to get the total number of prepared statements that are cached, multiply this value by the maximum number of connections in your connection pool.

Memory utilization for Prepared Statement Caching

Recommendations are made in several WebSphere Application Server documents on the value for the prepared statement cache.  They all recommend estimating the number of unique SQL statements an application prepares and using this number to set the number of prepared statements to be cached for each connection, the cache value is the number of unique statements. Application Server then creates one cache of this size for each connection.
The connection manager then divides this number by the number of maximum database connections and creates a cache of this size for each connection.
These formulas work well when the number of unique prepared statements and maximum connections are relatively small; however, these formulas do not take into account the possible memory consumption of the cached prepared statements, particularly when the total number of statements being cached is large.  What is considered a small or large prepared statement caches depends on the database vendor in use.
Each prepared statement object consumes some amount of memory.  The actual amount is variable, based on the database vendor in use, as well as the number and size of the parameter data for the statement.  When prepared statement caches are configured to large values, it is possible to outgrow the amount of memory available to the cache, resulting in unexpected behavior.  Depending on the type of JDBC™ driver, the memory consumption might be from the Java heap or from the JVM™ native heap.  In a 32-bit JVM, which includes the supported WebSphere Application Server JVMs, the limit for the combination of the Java heap and the native heap is 2 GB.  For more details on the different heaps and how they are configured and allocated, please check the documentation for the JVM for your particular platform.  
Both the JDBC type 3 and 4 drivers are pure Java code within the JVM.  This means that the memory consumed in the JVM is allocated from the Java heap.  The Java heap is a bounded memory space, the size of which can be configured by setting JVM parameters within WebSphere Application Server.  If the Java heap is exhausted, java.lang.OutOfMemory exceptions begin to occur in the Application Server.  It is also important to note that with a type 3 driver, there might also be memory consumption in the JDBC provider’s server middleware component corresponding to the cached prepared statements, which might require tuning of the prepared statement cache.  Cached prepared statements in a JDBC type 1 or 2 driver would likely consume most of their memory in native objects, which are allocated from the JVM native memory heap. The size of this heap is not directly configurable.  When the native heap is consumed, a java.lang.OutOfMemory error might occur, or the JVM itself might crash.  

Tuning the Prepared Statement Cache

As with any other tuning exercise, tuning of the prepared statement cache should be done before the application is put into production.  If you are experiencing memory growth in your JVM to the point where one of your heaps is exhausted, there are procedures and tools available for investigating which of the heaps is growing and what might be causing this growth.  Refer to the documentation provided by your particular JVM, or https://www.oracle.com/java/technologies/ for more information on debugging Java memory utilization.  
If you believe that the problem might be due to a very large prepared statement cache, consider the following possibilities:
  • If the memory growth is taking place in the Java heap, and there appears to be memory available in the native heap, try tuning the size of your maximum Java heap.
  • Decrease the size of your prepared statement cache.  A good model might be to cut the cache in half and analyze the memory usage at this level.  If this halved-cache setting causes a leveling-off of memory, consider whether there is available memory in the JVM.  If so, begin tuning the cache upward until you reach the optimal memory utilization.  If the available memory is still being consumed with the halved cache, try cutting the cache size in half again.  
If you choose to decrease the size of your prepared statement cache, some cycling of the statement cache could occur, as the least recently used statements are closed to make room for more recently used statements.  It can be worthwhile to analyze the usage pattern of the prepared statements in your application.  If some prepared statements are executed infrequently, the penalty in consumed resources might outweigh the advantage of the caching mechanism.  These infrequently used statements might be better suited to the java.sql.Statement interface, rather than the java.sql.PreparedStatement interface.  Statement objects are not cached by the Application Server and will not consume memory beyond the scope in which they are used.

Document Location

Worldwide

[{"Line of Business":{"code":"LOB45","label":"Automation"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEQTP","label":"WebSphere Application Server"},"ARM Category":[{"code":"a8m50000000CdYAAA0","label":"J2C-ConnectionPooling-JDBCDrivers->Connection Pooling-J2C-DB Connections->Connection Pooling"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)"}]

Document Information

Modified date:
25 April 2021

UID

ibm16410242