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.

Table 1. Main memory 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.

Table 2. Additional memory 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
AQT dictionary Stores accelerated query tables that the database server uses to determine which queries can be processed by Informix® Warehouse Accelerator. None. onstat -g cac aqt
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