SQL Statement Cache Improvements

The database server uses the SQL statement cache (SSC) to store SQL statements that a user executes. When users execute a statement stored in the SQL statement cache, the database server does not parse and optimize the statement again, which improves performance.

In Version 9.21, the SQL statement cache was enhanced to support the following capabilities:

• Insert the statement in the SQL statement cache as a key-only entry to track the number of times it has been referenced. After the statement has been referenced a specific number of times, it is inserted fully into the cache.
• Control whether statements enter the SQL statement cache after it exceeds its size limit.
• Define multiple pools for the SQL statement cache.

  Task	Manual
  Learn about the SQL statement cache	IBM Informix Performance Guide
  Use qualifying and identical statements; also learn about the memory limits and key-only cache entries.	IBM Informix Guide to SQL: Syntax
  Configure the SQL statement cache: STMT_CACHE_HITS STMT_CACHE_NOLIMIT STMT_CACHE_NUMPOOL STMT_CACHE_SIZE	IBM Informix Administrator's Reference
  Display SQL statement cache statistics: onstat -g ssc onstat -g ssc all onstat -g ssc pool	IBM Informix Administrator's Reference
  Configure the SQL statement cache on the fly: onmode -W STMT_CACHE_HITS onmode -W STMT_CACHE_NOLIMIT onmode -W STMT_CACHE_SIZE	IBM Informix Administrator's Reference
  Understand the performance advantages of the SQL statement cache: Use the SQL statement cache Enable the SQL statement cache Configure the SQL statement cache Monitor the SQL statement cache	IBM Informix Performance Guide