Setting SQL Statement Cache Parameters

Home | Previous Page | Next Page Disk, Memory, and Process Management > Managing Shared Memory >

Setting SQL Statement Cache Parameters

Table 27 shows the different ways that you can configure the SQL statement cache.

Table 27. Configuring the SQL Statement Cache
Configuration Parameter	Purpose	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.	onmode -W STMT_CACHE_SIZE

Use the following onstat options to monitor the SQL statement cache:

onstat -g ssc (same as onstat -g cac stmt)
onstat -g ssc all
onstat -g ssc pool

For more information on these configuration parameters, onstat -g options, and onmode commands, see the IBM Informix Dynamic Server Administrator's Reference.

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