TEMP_CACHE_MEMORY_MB option

Specifies the size of the temporary shared buffer cache, in MB.

1 to 4294967295 (2³² -1)

12MB

Can be set only for the PUBLIC group. DBA authority is required to set the option. You must shut down and restart the database server for the change to take effect.

This option sets the size of the temporary shared memory buffer cache for the database. Sybase recommends that you do not use this option; instead, use the -iqtc server option to set the temporary buffer cache size.

On 64-bit systems, you can allocate as much physical memory as you have to IQ buffer caches; however, for values greater than 4GB, you must use the server options -iqmc and -iqtc to set main and temporary buffer cache sizes. On 32-bit systems, the operating system limits the amount of memory you can allocate. See the Sybase IQ Installation and Configuration Guide for your platform for details.

In almost every case, the default temporary buffer cache size of 8MB is too low. For optimal performance, allocate as much memory as possible to the IQ main and temporary buffer caches. For example, if you have 4GB of shared memory on your machine available to Sybase IQ, you can split that amount between the main and temporary shared buffer caches.

If your IQ temporary buffer cache is larger than 10GB, Sybase also recommends increasing the SORT_PHASE1_HELPERS database option.

When setting the temp cache size, you must consider many factors, including total physical memory, swap space, memory for the main buffer cache, your indexes and query types, your mix of query and load processing, the number of concurrent users, and memory requirements of the operating system and other applications on the machine.