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.
| 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 onmode -wm STMT_CACHE=0 (0 is mode ‘off’) onmode -wm STMT_CACHE=1 (1 is mode ‘on’) onmode -wm STMT_CACHE=2 (2 is mode ‘enable’) |
| 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 onmode -wm STMT_CACHE_HITS=<numhits> |
| 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 onmode -wm STMT_CACHE_NOLIMIT={0/1} |
| 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 |
| STMT_QUERY_PLAN | Specifies the query plan from any query that exists in the Statement Cache. | onmode -wm STMT_QUERY_PLAN={0/1} |
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.