Memory Allocation

For parallel processing, the Resource Grant Manager (RGM) coordinates the use of resources and determines which queries to run if more than one query is in the queue.

The RGM dynamically allocates the following resources for parallel operations, such as DSS queries and index building:

• The amount of memory that the query can reserve in the virtual portion of database server shared memory

  The RGM uses values that you set to determine how to grant memory to a decision-support query. For information about adjusting the resources granted to decision support queries, refer toManaging Memory for DSS Applications.

• The number of parallel threads that can be started for each query

  The RGM uses the SQL operators that make up the query plan to determine the number of threads to start for a query.

  For join, group, and sort SQL operators, the RGM uses the following factors to determine the number of threads to start:

  • The values of configuration parameters (NUMCPUVPS, DS_TOTAL_MEMORY, DBSPACETEMP, and so forth)

    For information on setting these parameters, refer to Tuning Configuration for Resource Usage.

  • The number of CPU virtual processors available
  • The availability of computer-system resources (CPUs, memory, and disk I/O)

  For scan and insert SQL operators, the RGM also uses the number of fragments that the database operation accesses to determine the number of scan and insert threads (disk I/O).

For information about operators, refer to SQL Operators.

How the RGM Grants Memory

The RGM grants memory to a query for such operations as sorts, hash joins, and processing of GROUP BY clauses. The setting of DS_TOTAL_MEMORY determines the amount of memory that the RGM controls. The total amount of memory that all large memory-consuming queries use cannot exceed DS_TOTAL_MEMORY.

Figure 27. Sample RGM-Related Memory-Availability Configuration for a Single Coserver

The memory-availability configuration for a single coserver shown in Figure 27 provides about 15 percent of memory for OLTP and other application processing. It reserves about 85 percent for processing the queries that the RGM manages because these queries require large amounts of memory and use tables that are fragmented across coservers. The systemwide default setting of PDQPRIORITY in Figure 27 controls use of this memory. Although this systemwide default recommends that a single query be allocated a maximum and minimum amount of memory, users and applications can use the PDQPRIORITY environment variable and SQL statements to override the system default and request more or less memory.

Setting MAX_PDQPRIORITY to 90 provides a scaling factor that ensures no query can use more than 90 percent of the DS_TOTAL_MEMORY amount. In fact, the PDQPRIORITY amount requested by any query is scaled to 90 percent of the request. For example, a query run with the default PDQPRIORITY setting of 60 high and 25 low would be allocated a maximum of 90 percent of its 60 percent request, or 54 percent of DS_TOTAL_MEMORY.

A query can use the SQL statement SET PDQPRIORITY to request a single percentage of memory or a minimum and maximum percentage range of memory. The default set in the ONCONFIG file or by the application can be used instead if it is appropriate.

Although many system factors influence the amount of memory that the RGM grants to a single memory-consuming query, in general, the RGM uses the following formula to estimate the minimum amount of memory to grant to a single query:

min_memory_grant = DS_TOTAL_MEMORY*(min_pdq_priority/100)
               * (MAX_PDQPRIORITY / 100) 
               * number_of_coservers

The value of number_of_coservers is the number of currently running coservers that your ONCONFIG file defines.

The value of min_pdq_priority is the integer value specified with the LOW keyword in the PDQPRORITY configuration parameter, the PDQPRIORITY environment variable, or the SQL statement SET PDQPRiORITY.

The most recent setting of PDQPRIORITY determines its value for a query. If the session sets PDQPRIORITY in the environment variable, that setting takes precedence over the onconfig setting. If the query sets PDQPRIORITY with the SQL statement, that setting takes precedence over previous settings.

Use the following formula to estimate the maximum amount of memory that the RGM grants to a query:

max_memory_grant = DS_TOTAL_MEMORY*(max_pdq_priority/100)
                  * (MAX_PDQPRIORITY / 100) 
                  * number_of_coservers

The value of max_pdq_priority is the maximum integer value specified with the HIGH keyword in the PDQPRORITY configuration parameter, the PDQPRIORITY environment variable, or the SQL statement SET PDQPRORITY.