Prepared Statement Cache Size
When executing SQL statements against the database there are typically two phases required: prepare and execute. The prepare phase parses, compiles, and performs query optimizations on the SQL statement. The execute phase binds values to the parameters of the statement, and then the database management system executes the statement.
Having a prepared statement cache means that the prepare phase is reduced or eliminated altogether when performing SQL statements, reducing the number of calls to and time within the database. However, it uses more memory.
What it does
The Database Statement Cache Size defines the number of prepared database statements that will be cached by each connection in the DataSource resource template pool.
Original value
When you initially configure ActiveMatrix BPM you can select one of the following default values for the Database Statement Cache Size field in TIBCO Configuration Tool - see TIBCO Configuration Tool (TCT).
For development systems: 0 statements
For production systems: 100 statements
Changing the value
The larger the prepared statement cache, the more SQL statements that can use it and the greater the benefit. But each area makes a finite number of SQL statements, so having a very large value, such as 200, will be counterproductive since it will be using more memory within the JVM.
Too small a value, such as 10, and prepared statements will continually be swapped out of the cache in order to make space for others.
The default values provided for the Database Statement Cache Size parameter in TCT should be satisfactory for most systems, depending on the memory available to the JVM. If there are issues with JVM memory then you may consider turning this cache off by setting a value of 0. (This is the default setting for a development system.)