Configure and monitor memory caches
The 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.
The following table lists the main memory caches that have the greatest effect on performance and how to configure and monitor those caches.
| Cache Name | Cache Description | Configuration Parameters | onstat command |
|---|---|---|---|
| Data Dictionary | Stores information about the table definition (such as column names and data types). | DD_HASHSIZE: The maximum number of buckets in the
cache. DD_HASHMAX: The number of tables in each bucket |
onstat -g dic |
| Data Distribution | Stores distribution statistics for a column. | DS_POOLSIZE: The maximum number of entries in the
cache. DS_HASHSIZE: The number of buckets in the cache. |
onstat -g dsc |
| SQL Statement | Stores parsed and optimized SQL statements. | STMT_CACHE: Enable the SQL statement
cache. STMT_CACHE_HITS: The number of times anSQL statement is run before it is cached. STMT_CACHE_NOLIMIT: Prohibit entries into the SQL statement cache when allocated memory exceeds the value of the STMT_CACHE_SIZE configuration parameter. STMT_CACHE_NUMPOOL: The number of memory pools for the SQL statement cache. STMT_CACHE_SIZE: The size of the SQL statement cache, in KB. |
onstat -g ssc |
| UDR | Stores frequently used user-defined routines and SPL routines. | PC_POOLSIZE: The maximum number of user-defined
routines and SPL routines
in the cache. PC_HASHSIZE: The number of buckets in the UDR cache. |
onstat -g prc |
The following table lists more memory caches and how to configure and monitor those caches.
| Cache Name | Cache Description | Configuration Parameters | onstat command |
|---|---|---|---|
| Access method | Stores user-defined access methods. | None. | onstat -g cac am |
| Aggregate | Stores user-defined aggregates. | DS_POOLSIZE DS_HASHSIZE |
onstat -g cac agg |
| Cast | Stores user-defined casts. | DS_POOLSIZE DS_HASHSIZE |
onstat -g cac cast |
| External directives | Stores external directives. | None. | onstat -g cac ed |
| LBAC security policy information | Stores LBAC security policies. | PLCY_POOLSIZE PLCY_HASHSIZE |
onstat -g cac lbacplcy |
| LBAC credential memory | Stores LBAC credentials. | USRC_POOLSIZE USRC_HASHSIZE |
onstat -g cac lbacusrc |
| Operator class instance | Stores user-defined operator classes. | DS_POOLSIZE DS_HASHSIZE |
onstat -g cac opci |
| Procedure name | Stores user-defined routine and SPL routine names. | PC_POOLSIZE PC_HASHSIZE |
onstat -g cac prn |
| Routine resolution | Stores user-defined routine resolution information. | DS_POOLSIZE DS_HASHSIZE |
onstat -g cac rr |
| Secondary transient | Stores transient unnamed complex data types on secondary servers in a high-availability cluster. | DS_POOLSIZE DS_HASHSIZE |
onstat -g cac ttype |
| Extended type ID | Stores the IDs of user-defined types. | DS_POOLSIZE DS_HASHSIZE |
onstat -g cac typei |
| Extended type name | Stores the name of user-defined types. | DS_POOLSIZE DS_HASHSIZE |
onstat -g cac typen |