The sqt_max_cache_size configuration parameter determines the maximum SQT cache memory, in bytes. You set its value based on the number of transactions you are processing and the number of statements within the transactions.
The following formula gives the upper bound, or worst case, estimate for SQT cache size. A good default value for the SQT cache when you are using parallel DSI is 2MB.
The SQT cache formula is:
sqt_max_cache_size = T * (O + (S * N) )
The factors included in the formula are as follows:
T is the number of transactions to cache for a DSI. It should be 1 plus the number of ordinary DSI threads, multiplied by 20:
20 * (1 + dsi_num_threads - dsi_num_large_xact_threads)
O is the SQT cache’s per transaction overhead, including the begin and commit statements. This is 3K bytes.
S is the statement size within the SQT cache. This is 1K when the modified data is less than 100 bytes, 2K when the modified data is between 100 and 300 bytes, and 5K when the modified data is greater than 300 bytes.
N is the number of statements modified by a transaction. It is determined by the application.
When a steady stream of transactions is flowing to the DSI, the SQT should be full and the number of closed transactions should be about 20. More closed transactions indicates that the cache is larger than required, and fewer indicates that the cache is not large enough.
