Sizing the SQT cache

Replication Server configuration parameter: sqt_max_cache_size Database connection configuration parameter: dsi_sqt_max_cache_size

Monitor SQT cache usage by reviewing counter 24005 – CacheMemUsed. Although this counter may indicate that the SQT cache is constantly full, sqt_max_cache_size may not need to be increased. Instead, monitor counter 24009 – TransRemoved. If TransRemoved remains zero, indicating that transactions are not being flushed from the cache to make room for others, you may not need to adjust sqt_max_cache_size.

However, sqt_max_cache_size can be set too high. Monitor counter 24019 – SQTCacheLowBnd to determine the minimum cache size before transactions are flushed. This value depends on the number and size (in terms of bytes requred to store in cache) of transactions; it varies as the transaction profile varies. Monitor this counter when there is a heavy transaction load, and set sqt_max_cache_size to the size reported by this counter plus 10 to 20%. At this size, you may see an occasional transaction removed from cache, but typically a frequency of no more than one transaction every five minutes does not introduce significant latency.

sqt_max_cache_size applies to all SQT caches supporting DIST clients, and provides a default value for SQT caches that support DSI clients. The DISTs can push through transactions rapidly; their SQT caches do not need to be as large as SQT caches for DSIs. Thus, it is advisable to set SQT cache sizes for DSIs individually using the connection configuration parameter dsi_sqt_max_cache_size, and using sqt_max_cache_size for DIST SQT caches only.