Configuring memory for queries with hash joins, aggregates, and other memory-intensive elements

Certain configuration parameters can be set to provide more memory for queries that require sorting, hash joins, aggregates, and other memory-intensive elements.

How you configure the amount of memory that is available for a query depends on whether or not the query is a Parallel Database Query (PDQ).

Configuring memory for non-PDQ queries

If the PDQ priority is set to 0 (zero), you can change the amount of memory that is available for a query that is not a PDQ query by changing the setting of the DS_NONPDQ_QUERY_MEM configuration parameter. You can only use this parameter if the PDQ priority is set to zero. Its setting has no effect if the PDQ priority is greater than zero.

You can also change the value of DS_NONPDQ_QUERY_MEM with an onmode -wm or onmode -wf command.

For example, if you use the onmode utility, specify a value as shown in the following example:

onmode -wf DS_NONPDQ_QUERY_MEM=500

The minimum value for DS_NONPDQ_QUERY_MEM is 128 kilobytes. The maximum supported value is 25 percent of DS_TOTAL_MEMORY. 128 kilobytes is the default value of DS_NONPDQ_QUERY_MEM. If you specify a value for the DS_NONPDQ_QUERY_MEM parameter, determine and adjust the value based on the number and size of table rows involved in the query.

HCL OneDB™ might recalculate the value of DS_NONPDQ_QUERY_MEM initialization if the value is more than 25 percent of the DS_TOTAL_MEMORY value.

If HCL OneDB changes the value that you set, the server sends a message in this format:

DS_NONPDQ_QUERY_MEM recalculated and changed from old_value Kb to new_value Kb.

In the message, old_value represents the value that you assigned to DS_NONPDQ_QUERY_MEM in the user configuration file, and new_value represents the value determined by HCL OneDB.

For formulas for estimating the amount of additional space to allocate for hash joins, see Estimating temporary space for dbspaces and hash joins.

Configuring memory for PDQ queries

The Memory Grant Manager (MGM) component of HCL OneDB coordinates the use of memory, CPU virtual processors (VPs), disk I/O, and scan threads among decision-support queries. The MGM uses the DS_MAX_QUERIES, DS_TOTAL_MEMORY, DS_MAX_SCANS, and MAX_PDQPRIORITY configuration parameter settings to determine the quantity of these PDQ resources that can be granted to a decision-support query. The MGM also grants memory to a query for such activities as hash joins. For more information about the MGM, see The Memory Grant Manager.