The database server uses caches to store information in memory instead of performing a disk read to obtain the data or performing some other operation to create the information. These memory caches improve performance for multiple queries that access the same tables.
You can specify configuration parameters to tune the effectiveness of each cache, as the following table shows.
| Cache Name | Cache Description | Configuration Parameter and
Description |
|---|---|---|
| Data Dictionary | Stores information about the table definition (such as column names and data types). For details, see Data-Dictionary Cache. | DD_HASHSIZE: Number of buckets in data dictionary
cache
DD_HASHMAX: Number of tables that the database server can store in each bucket |
| Data Distribution | Stores distribution statistics for a column. For details, see Data-Distribution Cache. | DS_POOLSIZE: Total number of column distribution
statistics that the database server can store in the data distribution
cache
DS_HASHSIZE: Number of buckets in data distribution cache |
| SQL Statement | Stores parsed and optimized SQL statements. For details, see SQL Statement Cache Configuration. | STMT_CACHE: Enable the SQL statement cache
STMT_CACHE_HITS: Number of times SQL statement is executed before caching STMT_CACHE_NOLIMIT: Prohibit entries into the SQL statement cache when allocated memory will exceed STMT_CACHE_SIZE STMT_CACHE_NUMPOOL: Number of memory pools for the SQL statement cache STMT_CACHE_SIZE: Size of the SQL statement cache in kilobytes |
| UDR | Stores frequently used UDRs (SPL routines and external routines). For details, seeUDR Cache. | Total number of user-defined routines and SPL routines
in User-Defined Routine cache
Number of buckets in the User-Defined Routine cache |