Memory Grant Manager

The Memory Grant Manager (MGM) controls and reserves resources used by the parallel database queries and all parallel database queries (PDQPRIORITY>0) must pass through the MGM. The purpose of the MGM is to control

	The number of concurrent PDQs
	The number of scan threads that are initialised by the engine, and by each PDQ
	The number of PDQ threads initialised
	The quantity of memory and CPU available to the PDQ. The total memory allocated can not exceed DS_TOTAL_MEMORY

The onstat -g mgm command is used to monitor the MGM sub-system.

Memory is allocated in quantum units and is required for sorts, hash joins, and GROUP BY activities. A quantum is the smallest unit of memory that can be allocated and it is calculated from the following

Quantum Size = DS_TOTAL_MEMORY/DS_MAX_QUERIES

When the MGM detects a PDQ request the MGM must reserve a percentage of the DS_TOTAL_MEMORY, this calculated as follows and is rounded down to the nearest quantum

DS_TOTAL_MEMORY * (1/100 * PDQPRIORITY)

Therefore, if the effective PDQPRIORITY is 100, then 100% of DS_TOTAL_MEMORY will be reserved for the query when it registers with the MGM. In this case no other queries would be able to run.

Although the Memory Grant Manager reserves an amount of memory for a query, the query may use less than what was reserved.

The current MGM configuration and usage can be seen from onmode -g mgm output

Memory Grant Manager (MGM)
==========================
MAX_PDQ_PRIORITY:      100
DS_MAX_QUERIES:          1
DS_MAX_SCANS:           10
DS_TOTAL_MEMORY:    420000 KB

Queries: Active Ready Maximum
              1     0       1

Configuration parameters - These are the currently registered settings

The Active field shows the number of active parallel database queries. The Ready field shows the number of parallel database queries that are ready to run, but cannot because the Memory Grant Manager is preventing it for some reason. The Maximum field shows the maximum number of parallel database queries that can run at one time.

Memory:    Total    Free   Quantum
(KB)      420000       0    420000

Scans:     Total    Free   Quantum
             10        2        10

The memory is summarised into the total available (ignoring OS rounding this will be DS_TOTAL_MEMORY), the current free memory, and the current Quantum size.

The scan summary details the total number of scan threads that can be started for PDQ (usually DS_MAX_SCANS)., the number of scan threads that can be started, and the Quantum field. The scan Quantum is the smallest unit of scan threads that will be started at one time if required (one scan thread is started for each fragment).

Load Control: (Memory)   (Scans)   (Priority)   (Max Queries)   (Reinit)
                Gate 1    Gate 2       Gate 3          Gate 4     Gate 5
(Queue Length)       0         0            0               0          0

Before a query can be executed it must acquire the resources it needs and this checked by a series of gates or tests. Until a query has passed through all the gates it will not be executed. The Load Control section lists the size of the queue at each gate.

Gate
Memory	The number of queries waiting to be granted memory. A queue here usually this means that other queries have already reserved memory that equals DS_TOTAL_MEMORY.
Scans	The number of queries waiting to be allocated scan threads. If the the total number of scan threads has reached DS_MAX_SCANS then there will be a queue here.
Priority	The number of queries waiting for queries with higher PDQ priority to run.
Max Queries	The number of queries waiting for other queries to complete. A queue here means that DS_MAX_QUERIES has been reached.
Reinit	If either the DS_TOTAL_MEMORY or DS_MAX_QUERIES parameter has been changed then queries will be held until these changes have become effective. One the re-initialisation has been completed these queries will be released.

Active Queries:

Session      Query  Priority     Thread    Memory     Scans Gate
3         c0d73184       100   c6527842  40200/40200    9/8    -

Ready Queues: None

The 'Active Query' section lists the active and waiting PDQs. The summary session information is displayed for any active queries, further information can be retrieved via the onstat -g ses [id] command. The Query and Thread field lists the address of the internal control block associated with the query and the thread respectively. The Priority field lists the PDQPRIORITY of the query. The Memory field lists the number of memory get-calls. Within the Scan field there are two values, the number of allocated scan threads and the number reserved for the query by the MGM. The Gate field is only used for ready queries and lists the gate at which the query is waiting.

The final section from the onstat -g mgm output displays information about the average free resources and queries. This section summaries since the engine was started or since the last onmode -Q, onmode -M or onmode -S was issued.

Free Resources     Average #       Minimum #
==============  ==============   =============
Memory             0.0 +- 0.0           32
Scan               2.0 +- 0.0      1048576

Queries            Average #       Maximum #      Total #
==============   =============   =============  ===========
Active             1.0 +- 0.0            1            1
Ready              0.0 +- 0.0            0            0

The Free Resources section lists average and minimum amounts of memory [8K bytes units] for PDQs, and the the average number of scan threads used at any one time. The Average # column lists the average of memory/scans, and the standard deviation from that average. The Minimum # column lists the minimum available memory scans at any one time.

Within the query section the Average # column lists the average active and ready queue length. The Maximum # column lists the maximum size of the active and ready queue during the current period period. The Total # column lists the total number of queries that have been active and ready.