Use SQL Statement Cache Size (-SQLStmtCache) to set the number of statements that can be stored in the SQL cache.

Operating system and syntax UNIX / Windows -SQLStmtCache n
Use with Maximum value Minimum value Single-user default Multi-user default
SQL 1024 1 — 100
n
The number of statements to be stored in the SQL Statement Cache.

Use the command line to change the value of -SQLStmtCache.

Statement caching improves performance by:
  • Reusing previously prepared SQL statements
  • Avoiding parsing
  • Planning overhead for repeated queries

For example, a SQL query with several joins might consume a significant amount of time during parsing and execution plan preparation. In general, the default cache size of 100 is sufficient. However, if a single connection persists for an extended duration and executes hundreds of distinct queries, a higher cache size may be more appropriate.

The SQL Statement Cache Size (-SQLStmtCache) parameter may be modified while your database is online using PROMON or through the _DbParams VST. This change will only take effect on the primary broker and its newly spawned servers.