onstat -g mgm command: Print MGM resource information

Use the onstat -g mgm command to show resource information about Memory Grant Manager (MGM).

You can use the onstat -g mgm command to monitor how MGM coordinates memory use and scan threads. This command reads shared-memory structures and provides statistics that are accurate at the instant that the command runs.

Read syntax diagramSkip visual syntax diagram
Syntax:

>>-onstat-- -g--mgm--------------------------------------------><

The onstat -g mgm output shows a unit of memory that is called a quantum. The memory quantum represents a unit of memory, as follows:
memory quantum = DS_TOTAL_MEMORY / DS_MAX_QUERIES
The following calculation shows the memory quantum for the values that the onstat -g mgm output shows:
memory quantum = 4000 kilobytes / 31 
                = 129 kilobytes

The database server adjusts the value of a quantum as needed when it grants memory. Therefore, the value of the quantum as shown by the onstat -g mgm command is not always accurate.

The scan thread quantum is always equal to 1.

Example output

Figure 1. onstat -g mgm command output
Memory Grant Manager (MGM) 
--------------------------
MAX_PDQPRIORITY:  100
DS_MAX_QUERIES:    31
DS_MAX_SCANS:      1048576
DS_NONPDQ_QUERY_MEM: 128 KB
DS_TOTAL_MEMORY:   4000 KB

Queries:   Active     Ready   Maximum
                        0         0        31 
Memory:     Total      Free   Quantum 
(KB)         4000      4000       128 

Scans:      Total      Free   Quantum 
               1048576   1048576         1 

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

Active Queries:  None 
Ready Queries:  None 
Free Resource        Average #        Minimum # 
--------------    ---------------     --------- 
Memory               0.0 +- 0.0           500
Scans                0.0 +- 0.0          1048576 

Queries              Average #        Maximum #    Total #
--------------    ---------------     ---------    -------
Active               0.0 +- 0.0             0          0
Ready                0.0 +- 0.0             0          0

Resource/Lock Cycle Prevention count:  0

Output description

The first portion of the output shows the values of the PDQ configuration parameters.

The second portion of the output describes MGM internal control information. It includes four groups of information. The first group is Queries:

Active
Number of PDQ queries that are currently running
Ready
Number of user queries ready to run but whose execution the database server deferred for load-control reason
Maximum
Maximum number of queries that the database server allows to be active. Reflects current value of the DS_MAX_QUERIES configuration parameter

The next group is Memory:

Total
KB of memory available for use by PDQ queries (DS_TOTAL_MEMORY specifies this value.)
Free
KB of memory for PDQ queries not currently in use
Quantum
Approximate number of KB of memory in a memory quantum

The next group is Scans:

Total
The total number of scan threads as specified by the DS_MAX_SCANS configuration parameter
Free
Number of scan threads currently available for decision-support queries
Quantum
The number of scan threads in a scan-thread quantum

The last group in this portion of the output describes MGM Load Control:

Memory
Number of queries that are waiting for memory
Scans
Number of queries that are waiting for scans
Priority
Number of queries that are waiting for queries with higher PDQ priority to run
Max Queries
Number of queries that are waiting for a query slot
Reinit
Number of queries that are waiting for running queries to complete after an onmode -M or -Q command

The next portion of the output, Active Queries, describes the MGM active and ready queues. This portion of the output shows the number of queries that are waiting at each gate:

Session
The session ID for the session that initiated the query
Query
Address of the internal control block that is associated with the query
Priority
PDQ priority that is assigned to the query
Thread
Thread that registered the query with MGM
Memory
Memory that is currently granted to the query or memory that is reserved for the query (Unit is MGM pages, which is 8 KB.)
Scans
Number of scan threads currently used by the query or number of scan threads that are allocated to the query
Gate
Gate number at which query is waiting

The next portion of the output, Free Resource, provides statistics for MGM free resources. The numbers in this portion and in the final portion reflect statistics since system initialization or the last onmode -Q, -M, or -S command. This portion of the output contains the following information:

Average
Average amount of memory and number of scans
Minimum
Minimum available memory and number of scans

The next portion of the output, Queries, provides statistics about MGM queries:

Average
Average active and ready queue length
Maximum
Maximum active and ready queue length
Total
Total active and ready queue length
Resource/Lock Cycle Prevention count
Number of times the system immediately activated a query to avoid a potential deadlock. (The database server can detect when some of the queries in its queue might create a deadlock situation if the queries are not run immediately.)

Copyright© 2019 HCL Technologies Limited