If your database server must execute many memory-consuming queries concurrently, the first few queries submitted might quickly exhaust the amount of memory specified in the DS_TOTAL_MEMORY configuration parameter.
Unless you allow the optimizer to determine memory allocation for queries, as described in Enabling Optimizer-Determined Memory Allocation, the active PDQPRIORITY setting and the MAX_PDQPRIORITY configuration parameter together determine the amount of memory to allocate to a query. For information about how these configuration parameters work together to determine how memory is allocated for queries, seeAllocating Memory for DSS Queries.
No well-defined rules exist for choosing these environment variable and parameter values. Consider the following factors:
Because queries compete for memory, a trade-off exists between running several queries more slowly and running only one or two query very fast. As a compromise, you might set MAX_PDQPRIORITY to a low value such as 20 or 30. With this setting, no query can be granted more than 20 or 30 percent of DS_TOTAL_MEMORY even if the query sets PDQPRIORITY to 100, and five or more queries can run simultaneously.
For information about managing memory use by specific sessions or groups of sessions, see Enabling Optimizer-Determined Memory Allocation, Restricting Optimizer-Determined Memory Allocation, and Overriding PDQPRIORITY Settings.
To limit the number of memory-consuming queries that run concurrently, use the DS_MAX_QUERIES configuration parameter.
Memory-consuming queries are managed by the Resource Grant Manager (RGM). Such queries perform complex tasks across coservers, such as scans of entire tables, multiple hash joins, and unnesting subqueries, which results in the creation of temporary tables. The RGM manages memory-consuming queries.
You might limit the number of concurrent DSS queries for the following reasons:
PDQPRIORITY specifies the amount of memory that a query requests. Queries with a low PDQPRIORITY setting request proportionally smaller amounts of memory, so more of those queries can run simultaneously. For information on how PDQPRIORITY-based memory allocation works, refer to How the RGM Grants Memory.
However, if the optimizer determines an appropriate memory allocation for queries submitted by a session, as described in Enabling Optimizer-Determined Memory Allocation, the value of PDQPRIORITY is ignored.
To specify the number of kilobytes a query can use for operator overflow on each coserver, see Limiting Temporary Space for Query-Operator Overflow.