Prepared Statement Discard in Websphere Application Server.

Hi all,

Many of us struggle when it comes down to Prepared Statement Discard issue, what the Prepared Statement Cache in WebSphere Application Server (WAS) is about and how to configure it. This article tries to answer at least some of these questions. 

Introduction Statement cache size:

Statement cache size specifies the number of statements that can be cached per connection. The application server caches a statement after you close that statement.

The WebSphere Application Server data source optimizes the processing of prepared statements and callable statements by caching those statements that are not used in an active connection. Both statement types help maximize the performance of transactions between your application and datastore.

• A prepared statement is a precompiled SQL statement that is stored in a Prepared Statement object. The application server uses this object to run the SQL statement multiple times, as required by your application run time, with values that are determined by the run time.
• A callable statement is an SQL statement that contains a call to a stored procedure, which is a series of precompiled statements that perform a task and return a result. The statement is stored in the CallableStatement object. The application server uses this object to run a stored procedure multiple times, as required by your application run time, with values that are determined by the run time.

If the statement cache is not large enough, useful entries are discarded to make room for new entries. To determine the highest value for your cache size to avoid any cache discards, add the number of uniquely prepared statements and callable statements (as determined by the SQL string, concurrency, and the scroll type) for each application that uses this data source on a particular server. This value is the maximum number of possible statements that can be cached on a given connection over the life of the server. Setting the cache size to this value means you never have cache discards. In general, configure a larger cache for applications with a greater number of statements.

Tuning the statement cache improves throughput from 10% to 20%. However, because of potential resource limitations, this might not always be possible.

Observations For Prepared statement discard in PTT:

We can use different tools to observer the prepared statement discard for our WAS environment. It is easy to observe the same through the tool call Websphere Application Server Performance tunning tool kit. Using this tool you will get the output in following two formate.

Fig: Observation From PTT console

Fig: Observation from generated report

Here we get the name of data source for which we need to tune the prepared statement cache size. In this example we have a datasouce of name CMSPool. Which discard 23 prepared statement approximately On the basic of these observation we increasing the size of prepared statement by current size + 20.

Note: Make sure to also talk to your DB administrator. Most often nowadays modern databases have their own PreparedStatement cache mechanism integrated at the DB Server side.

 

Procedure:

1. To access this administrative console page switch to following paths:

Resources > JDBC > Data sources > datasource_name > WebSphere Application Server data source properties

2. Here we are getting the default value of statement cache size 10, change it to 30 and save the changes. 

3. Restart the WAS services once.

Hope this will help you and resolve the issue of Prepared Statement Discard in your environment.

“Effort only fully releases its reward after a person refuses to quit.”

Regards,

Akhilesh B. Humbe