Show TOC

CACHE_PARTITIONS OptionLocate this document in the navigation structure

Sets the number of partitions to be used for the main and temporary buffer caches.

Allowed Values

0, 1, 2, 4, 8, 16, 32, 64

  • 0 (default) SAP IQ computes the number of partitions automatically as number_of_cpus/8, rounded to the nearest power of 2, up to a maximum of 64.
  • 1 one partition only; this value disables partitioning.
  • 2 through 64 number of partitions; must be a power of 2.
Default

0

Scope

Option can be set at the database (PUBLIC) level only.

Requires the SET ANY SYSTEM OPTION system privilege to set this option. Takes effect for the current database the next time you start the database server.

Remarks

Partitioning the buffer cache can sometimes improve performance on systems with multiple CPUs by reducing lock contention. Normally, you should rely on the value that SAP IQ calculates automatically, which is based on the number of CPUs on your system. However, if you find that load or query performance in a multi-CPU configuration is slower than expected, you might be able to improve it by setting a different value for CACHE_PARTITIONS.

Both the number of CPUs and the platform can influence the ideal number of partitions. Experiment with different values to determine the best setting for your configuration.

The value you set for CACHE_PARTITIONS applies to both the main and temp buffer caches. The absolute maximum number of partitions is 64, for each buffer cache.

The -iqpartition start_iq server option sets the partition limit at the server level. If you specify -iqpartition at server startup, it overrides the CACHE_PARTITIONS setting.

The number of partitions does not affect other buffer cache settings. It also does not affect statistics collected by the IQ monitor; statistics for all partitions are rolled up and reported as a single value.

Example

In a system with 100 CPUs, if you do not set CACHE_PARTITIONS, SAP IQ automatically sets the number of partitions to 16:

100 cpus/8 = 12, rounded to 16.

With this setting, there are 16 partitions for the main buffer cache and 16 partitions for the temp cache.

In the same system with 100 CPUs, to explicitly set the number of partitions to 8, specify:

SET OPTION "PUBLIC".CACHE_PARTITIONS=8