Adjusting the amount of memory for DSS and PDQ queries

You can estimate the amount of shared memory to allocate to decision-support (DSS) queries. Then, if necessary, you can adjust the value of the DS_TOTAL_MEMORY configuration parameter, which specifies the amount of memory available for PDQ queries.

Use the following formula as a starting point for estimating the amount of shared memory to allocate to DSS queries:

DS_TOTAL_MEMORY = p_mem - os_mem - rsdnt_mem - 
                  (128 kilobytes * users) - other_mem

p_mem: represents the total physical memory that is available on the host computer.
os_mem: represents the size of the operating system, including the buffer cache.
resdnt_mem: represents the size of HCL OneDB™ resident shared memory.
users: is the number of expected users (connections) specified in the NETTYPE configuration parameter.
other_mem: is the size of memory used for other (non-HCL® OneDB) applications.

The value for DS_TOTAL_MEMORY that is derived from this formula serves only as a starting point. To arrive at a value that makes sense for your configuration, you must monitor paging and swapping. (Use the tools provided with your operating system to monitor paging and swapping.) When paging increases, decrease the value of DS_TOTAL_MEMORY so that processing the OLTP workload can proceed.

The amount of memory that is granted to a single parallel database query depends on many system factors, but in general, the amount of memory granted to a single parallel database query is proportional to the following formula:

memory_grant_basis = (DS_TOTAL_MEMORY/DS_MAX_QUERIES) *
               (PDQPRIORITY / 100) *
               (MAX_PDQPRIORITY / 100)

However, if the currently executing queries on all databases of the server instance require more memory than this estimate of the average allocation, another query might overflow to disk or might wait until concurrent queries completed execution and released sufficient memory resources for running the query. The following alternative formula estimates the PDQ memory available for a single query directly:

memory_for_single_query = DS_TOTAL_MEMORY  *
               (PDQPRIOIRTY / 100) *
               (MAX_PDQPRIORITY / 100)