SQL statement cache configuration

The value of the STMT_CACHE configuration parameter enables or disables the SQL statement cache.

For more information about how the value of the STMT_CACHE configuration parameter enables the SQL statement cache, see Enabling the SQL statement cache describes.

Configuration parameters that affect the SQL statement cache shows how the database server uses the values of the pertinent configuration parameters for the SQL statement cache. Further explanation follows the figure.
Figure 1: Configuration parameters that affect the SQL statement cache

This figure is described in the surrounding text.
When the database server uses the SQL statement cache for a user, it means the database server takes the following actions:
  • Checks the SQL statement cache first for a match of the SQL statement that the user is executing
  • If the SQL statement matches an entry, executes the statement using the query memory structures in the SQL statement cache (User 2 in Configuration parameters that affect the SQL statement cache)
  • If the SQL statement does not match an entry, the database server checks if it qualifies for the cache.

    For information about what qualifies an SQL statement for the cache, see SQL statement cache qualifying criteria.

  • If the SQL statement qualifies, inserts an entry into the cache for subsequent executions of the statement.
The following parameters affect whether or not the database server inserts the SQL statement into the cache (User 1 in Configuration parameters that affect the SQL statement cache):
  • STMT_CACHE_HITS specifies the number of times the statement executes with an entry in the cache (referred to as hit count). The database server inserts one of the following entries, depending on the hit count:
    • If the value of STMT_CACHE_HITS is 0, inserts a fully cached entry, which contains the text of the SQL statement plus the query memory structures
    • If the value of STMT_CACHE_HITS is not 0 and the statement does not exist in the cache, inserts a key-only entry that contains the text of the SQL statement. Subsequent executions of the SQL statement increment the hit count.
    • If the value of STMT_CACHE_HITS is equal to the number of hits for a key-only entry, adds the query memory structures to make a fully cached entry.
  • STMT_CACHE_SIZE specifies the size of the SQL statement cache, and STMT_CACHE_NOLIMIT specifies whether or not to limit the memory of the cache to the value of STMT_CACHE_SIZE. If you do not specify the STMT_CACHE_SIZE parameter, it defaults to 524288 (512 * 1024) bytes.

    The default value for STMT_CACHE_NOLIMIT is 1, which means the database server will insert entries into the SQL statement cache even though the total amount of memory might exceed the value of STMT_CACHE_SIZE.

    When STMT_CACHE_NOLIMIT is set to 0, the database server inserts the SQL statement into the cache if the current size of the cache will not exceed the memory limit.

The following sections on STMT_CACHE_HITS, STMT_CACHE_SIZE, STMT_CACHE_NOLIMIT, STMT_CACHE_NUMPOOL and provide more details on how the following configuration parameters affect the SQL statement cache and reasons why you might want to change their default values.