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 |
| 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 OneDB™ 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 OneDB Performance Guide. For details on qualifying and identical statements, see the HCL OneDB Guide to SQL: Syntax.