In the product documentation under Database Access the STMTCACHE directive is defined as follows:
Sets the cache size for prepared statements used by OpenESQL. The default is 20. Depending upon your application and data source, performance improvements or data errors can result if this value is set higher than that. What exactly does this mean? What does the value of this setting actually refer to?
The setting of STMTCACHE refers to the number of prepared statements that the runtime will keep alive. The cache is managed on a least recently used basis. A statement can be recycled if it is not an open cursor.
At one time we had problems with Oracle running out of resources so we introduced STMTCACHE to restrict the amount of server resources a client app could tie up. OpenESQL always uses prepare execute rather than direct execution so that any statement executed multiple times will execute faster on the 2nd and subsequent executions.
Only statements that cause server side execution are cached (ie open cursor, insert, delete, etc. are cached since they cause execution of a SQL statement at the server, but fetch, commit, etc. are not since they map to API calls or local action only).
We've seen STMTCACHE yielding benefits with settings up to 300 in large batch jobs. The optimum setting depends on the application, so it’s something you need to experiment with. Increased values may speed up execution of an application but will tie up server memory so may limit overall throughput.