Memory Grant Manager

The Memory Grant Manager (MGM) is a database server component that 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 parameters to determine the quantity of these PDQ resources that can be granted to a decision-support query. For more information about these configuration parameters, see Effect of Configuration on Memory Utilization.

The MGM dynamically allocates the following resources for decision-support queries:

• The number of scan threads started for each decision-support query
• The number of threads that can be started for each query
• The amount of memory in the virtual portion of database server shared memory that the query can reserve

When your database server system has heavy OLTP use, and you find performance is degrading, you can use the MGM facilities to limit the resources committed to decision-support queries. During off-peak hours, you can designate a larger proportion of the resources to parallel processing, which achieves higher throughput for decision-support queries.

The MGM grants memory to a query for such activities as sorts, hash joins, and processing of GROUP BY clauses. The amount of memory that decision-support queries use cannot exceed DS_TOTAL_MEMORY.

The MGM grants memory to queries in quantum increments. To calculate a quantum, use the following formula:

memory quantum = DS_TOTAL_MEMORY / DS_MAX_QUERIES

For example, if DS_TOTAL_MEMORY is 12 megabytes and DS_MAX_QUERIES is 4, the quantum is 3 megabytes (12/4). Thus, with these values in effect, a quantum of memory equals 3 megabytes. In general, memory is allocated more efficiently when quanta are smaller. You can often improve performance of concurrent queries by increasing DS_MAX_QUERIES to reduce the size of a quantum of memory.

To monitor resources that the MGM allocates, run the onstat -g mgm command. This command displays only the amount of memory that is currently used; it does not display the amount of memory that has been granted. For more information about this command, see Monitoring MGM Resources.

The MGM also grants a maximum number of scan threads per query based on the values of the DS_MAX_SCANS and the DS_MAX_QUERIES parameters.

The following formula yields the maximum number of scan threads per query:

scan_threads = min (nfrags, DS_MAX_SCANS * (pdqpriority / 100)
   * (MAX_PDQPRIORITY / 100))

nfrags

is the number of fragments in the table with the largest number of fragments.

pdqpriority

is the value for PDQ priority that is set by either the PDQPRIORITY environment variable or the SQL statement SET PDQPRIORITY.

For more information about any of these database server configuration parameters, see Effect of Configuration on Memory Utilization.

The PDQPRIORITY environment variable and the SQL statement SET PDQPRIORITY request a percentage of PDQ resources for a query. You can use the MAX_PDQPRIORITY configuration parameter to limit the percentage of the requested resources that a query can obtain and to limit the impact of decision-support queries on OLTP processing. For more information, see Limiting the Priority of DSS Queries.