Set SQL statement cache parameters

There are different ways that you can configure the SQL statement cache.

The following table shows ways to configure the SQL statement cache.

Table 1. Configure the SQL statement cache
Configuration parameter Purpose The onmode command
STMT_CACHE Turns on, enables, or disables the SQL statement cache in memory. If turned on, specifies whether the SQL statement cache can hold a parsed and optimized SQL statement. onmode -e mode
STMT_CACHE_HITS Specifies the number of hits (references) to a statement before it is fully inserted into the SQL statement cache. onmode -W STMT_CACHE_HITS
STMT_CACHE_NOLIMIT Controls whether to insert statements into the SQL statement cache after its size is greater than the STMT_CACHE_SIZE value. onmode -W STMT_CACHE_NOLIMIT
STMT_CACHE_NUMPOOL Defines the number of memory pools for the SQL statement cache. None
STMT_CACHE_SIZE Specifies the size of the SQL statement cache. None
Use the following onstat options to monitor the SQL statement cache:
  • onstat -g ssc
  • onstat -g ssc all
  • onstat -g ssc pool

For more information about these configuration parameters, onstat -g options, and onmode commands, see the HCL Informix® Administrator's Reference.

For more information about using the SQL statement cache, monitoring it with the onstat -g options, and tuning the configuration parameters, see improving query performance in the HCL Informix Performance Guide. For details on qualifying and identical statements, see the HCL Informix Guide to SQL: Syntax.

Copyright© 2019 HCL Technologies Limited