Memory limit and size

Although the database server tries to clean the SQL statement cache, sometimes entries cannot be removed because they are currently in use. In this case, the size of the SQL statement cache can exceed the value of the STMT_CACHE_SIZE configuration parameter.

The default value of the STMT_CACHE_NOLIMIT configuration parameter is 1, which means the database server inserts the statement even though the current size of the cache might be greater than the value of the STMT_CACHE_SIZE parameter.

If the value of the STMT_CACHE_NOLIMIT configuration parameter is 0, the database server does not insert either a fully-qualified or key-only entry into the SQL statement cache if the size will exceed the value of STMT_CACHE_SIZE.

Use the onstat -g ssc option to monitor the current size of the SQL statement cache. Look at the values in the following output columns of the onstat -g ssc output:
  • The currsize column shows the number of bytes currently allocated in the SQL statement cache.

    In onstat -g ssc output, the currsize column has a value of 11264.

  • The maxsize column shows the value of STMT_CACHE_SIZE.

    In onstat -g ssc output, the maxsize column has a value of 524288, which is the default value (512 * 1024 = 524288).

When the SQL statement cache is full and users are currently executing all statements within it, any new SQL statements that a user executes can cause the SQL statement cache to grow beyond the size that STMT_CACHE_SIZE specifies. When the database server is no longer using an SQL statement within the SQL statement cache, it frees memory in the SQL statement cache until the size reaches a threshold of STMT_CACHE_SIZE. However, if thousands of concurrent users are executing several ad hoc queries, the SQL statement cache can grow very large before any statements are removed. In such cases, take one of the following actions:
  • Set the STMT_CACHE_NOLIMIT configuration parameter to 0 to prevent insertions when the cache size exceeds the value of the STMT_CACHE_SIZE parameter.
  • Set the STMT_CACHE_HITS parameter to a value greater than 0 to prevent caching unshared SQL statements.
You can use one of the following methods to change the STMT_CACHE_NOLIMIT configuration parameter value:
  • Update the ONCONFIG file to specify the STMT_CACHE_NOLIMIT configuration parameter. You must restart the database server for the new value to take effect.
  • Use the onmode -W command to override the STMT_CACHE_NOLIMIT configuration parameter dynamically while the database server is running. 
    onmode -W STMT_CACHE_NOLIMIT 0

    If you restart the database server, the value reverts the value in the ONCONFIG file. Therefore, if you want the setting to remain for subsequent restarts, modify the ONCONFIG file.