Monitor memory utilization

You can use some specific onstat -g command options to monitor memory utilization.

Use the following onstat -g options to monitor memory utilization. For overall memory information, omit table name, pool name, or session id from the commands that permit those optional parameters.

Table 1. onstat -g Options for monitoring memory utilization
Argument Description
ffr pool name | session id Displays free fragments for a pool of shared memory or by session
dic table name Displays one line of information for each table cached in the shared-memory dictionary

If you provide a specific table name as a parameter, this argument displays internal SQL information about that table.

dsc Displays one line of information for each column of distribution statistics cached in the data distribution cache.
mem pool name | session id Displays memory statistics for the pools that are associated with a session

If you omit pool_name | session id, this argument displays pool information for all sessions.
mgm Displays Memory Grant Manager resource information, including:
  • The values of the PDQ configuration parameters
  • Memory and scan information
  • Load information, such as the number of queries that are waiting for memory, the number of queries that are waiting for scans, the number of queries that are waiting for queries with higher PDQ priority to run, and the number of queries that are waiting for a query slot
  • Active queries and the number of queries at each gate
  • Statistics on free resources
  • Statistics on queries
  • The resource/lock cycle prevention count, which shows the number of times the system immediately activated a query to avoid a potential deadlock
nsc client id Displays shared-memory status by client ID

If you omit client id, this argument displays all client status areas.
nsd Displays network shared-memory data for poll threads
nss session id Displays network shared-memory status by session id

If you omit session id, this argument displays all session status areas.
osi Displays information about your operating system resources and parameters, including shared memory and semaphore parameters, the amount of memory currently configured on the computer, and the amount of memory that is unused

Use this option when the server is not online.
prc Displays one line of information for each user-defined routine (SPL routine or external routine written in C or Java™ programming language) cached in the UDR cache
seg Displays shared-memory-segment statistics

This argument shows the number and size of all attached segments.

ses session id Displays memory usage for session id

If you omit session id, this argument displays memory usage for all sessions.
ssc Displays one line of information for each query cached in the SQL statement cache
stm session id Displays memory usage of each SQL statement for session id

If you omit session id, this argument displays memory usage for all sessions.
ufr pool name | session id Displays allocated pool fragments by user or session