Determining the number of nonshared entries in the SQL statement cache

To determine how many nonshared entries exist in the SQL statement cache, run onstat -g ssc all.

About this task

The onstat -g ssc all option displays the key-only entries in addition to the fully cached entries in the SQL statement cache.

To determine how many nonshared entries exist in the cache:

Compare the onstat -g ssc all output with the onstat -g ssc output.
If the difference between these two outputs shows that many nonshared entries exist in the SQL statement cache, increase the value of the STMT_CACHE_HITS configuration parameter to allow more shared statements to reside in the cache and reduce the management overhead of the SQL statement cache.

You can use one of the following methods to change the STMT_CACHE_HITS parameter value:

Update the ONCONFIG file to specify the STMT_CACHE_HITS configuration parameter. You must restart the database server for the new value to take effect.
You can use a text editor to edit the ONCONFIG file. Then bring down the database server with the onmode -ky command and restart with the oninit command.
Increase the STMT_CACHE_HITS configuration parameter dynamically while the database server is running:
You can use any of the following methods to reset the STMT_CACHE_HITS value at run time:
- Issue the onmode -W command. The following example specifies that three (3) instances are required before a new query is added to the statement cache:
```
onmode -W STMT_CACHE_HITS 2
```
- Call the ADMIN or TASK function of the SQL administration API. The following example is equivalent to the onmode command in the previous example:
```
EXECUTE FUNCTION TASK("ONMODE", "W", "STMT_CACHE_HITS", "2");
```
If you increase STMT_CACHE_HITS dynamically without updating the configuration file, and the database server is subsequently restarted, the STMT_CACHE_HITS setting reverts the value in the ONCONFIG file. Therefore, if you want the setting to persist after subsequent restarts, modify the ONCONFIG file.