Home
AdministeringIn addition to administering the database server, you can tune performance, replicate data, and archive data.
System administrationThese topics contain concepts, procedures, and reference information for database and database server administrators to use for managing and tuning HCL Informix® database servers.
Performance GuideThe Informix® Performance Guide describes how to configure and operate your HCL Informix® database server to improve overall system throughput and to improve the performance of SQL queries.
Effect of configuration on memory utilizationThe combination of operating-system and Informix® configuration parameters can affect memory utilization.
Configure and monitor memory cachesThe database server uses caches to store information in memory instead of performing a disk read or another operation to obtain the information. These memory caches improve performance for multiple queries that access the same tables. You can set some configuration parameters to increase the effectiveness of each cache. You can view information about memory caches by running onstat commands.
Monitor and tune the SQL statement cacheThe SQL statement cache stores optimized SQL statements so that multiple users who run the same SQL statement can achieve some performance improvements.
Monitoring and tuning the size of the SQL statement cacheIf the size of the SQL statement cache is too small, performance problems can occur. You can monitor the effectiveness of the size of the SQL statement cache.
Changing the size of the SQL statement cacheYou can analyze onstat -g ssc all output to determine if the SQL statement cache is too small. If the size of the cache is too small, you can change it.

Changing the size of the SQL statement cache

You can analyze onstat -g ssc all output to determine if the SQL statement cache is too small. If the size of the cache is too small, you can change it.

To determine if the size of the SQL statement cache is too small:

Run onstat -g ssc all to determine if the cache is too small.
Look at the values in the following output columns in the Statement Cache Entries portion of the onstat -g ssc all output:
- The flags column shows the current status of an SQL statement in the cache.
  A value of F in the second position indicates that the statement is currently fully cached.
  
  A value of - in the second position indicates that only the statement text (key-only entry) is in the cache. Entries with this - value in the second position appear in the onstat -g ssc all output, but not in the onstat -g ssc output.
- The hits column shows the number of times the SQL statement has been executed, excluding the first time it is inserted into the cache.
If you do not see fully cached entries for statements that applications use most and the value in the hits column is large for the entries that do occupy the cache, then the SQL statement cache is too small.

To change the size of the SQL statement cache:

Update the value of the STMT_CACHE_SIZE configuration parameter.
Restart the database server for the new value to take effect.

Rate this topic

5 stars 4 stars 3 stars 2 stars 1 star

Comment on this topic.

By clicking this box, you acknowledge that you are NOT a U.S. Federal Government employee or agency, nor are you submitting information with respect to or on behalf of one. HCL provides software and services to U.S. Federal Government customers through its partners immixGroup, Inc. Contact this team at https://hcltechsw.com/resources/us-government-contact. Do not include any personal data in this Comment box.