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.

Figure 1: Syntax:

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 2: 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.)