MON_GET_MEMORY_POOL table function - get memory pool information

The MON_GET_MEMORY_POOL table function retrieves metrics from the memory pools contained within a memory set.

Syntax

The schema is SYSPROC.

Table function parameters

memory_set_type

An input argument of type VARCHAR(32) that specifies the type of the memory set when calling this function. If the argument is NULL or an empty string, then metrics are retrieved for all memory sets at the instance and database level. Otherwise metrics for the specified memory set are retrieved.

These parameter values are accepted:

Value	Scope	Description
DBMS	Instance	Database manager (DBM) memory set
FMP	Instance	Fenced mode process (FMP) memory set
PRIVATE	Instance	Private memory set
DATABASE	Database	Database memory set
APPLICATION	Database	Application memory set
FCM	Host - there is only one FCM memory set allocated per machine for an instance.	Fast communication manager (FCM) memory set
NULL	All	All memory sets at the instance and database level

db_name

An input argument of type VARCHAR(128) that specifies a valid database name in the same instance as the currently connected database when calling this function.

The database must have a directory entry type of either INDIRECT or HOME, as returned by a LIST DATABASE DIRECTORY command. The database must be active. Alternatively, the CURRENT_SERVER special register can be specified to retrieve metrics from the currently connected database. The register value contains the actual name of the database, not an alias.

If the argument is NULL or an empty string, metrics are taken from all active databases in the instance. This input argument applies only to database level memory sets.

member

An input argument of type INTEGER that specifies from which member the data is returned. Specify -1 for the current database member, or -2 for all active members. If the NULL value is specified, -1 is set implicitly.

Authorization

One of the following authorities is required to execute the routine:

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

None

Example

Example 1: Retrieve memory set metrics for the current instance and the currently connected database.

SELECT varchar(memory_set_type, 20) AS set_type,
       varchar(memory_pool_type,20) AS pool_type,
       varchar(db_name, 20) AS dbname,
       memory_pool_used,
       memory_pool_used_hwm
FROM TABLE( 
       MON_GET_MEMORY_POOL(NULL, CURRENT_SERVER, -2))

An example of output from this query.

SET_TYPE     POOL_TYPE        DBNAME       MEMORY_POOL_USED MEMORY_POOL_USED_HWM
------------ ---------------- ------------ ---------------- --------------------
DBMS         FCM_LOCAL        -                           0                    0
DBMS         FCM_SESSION      -                     2359296              2359296
DBMS         FCM_CHANNEL      -                      589824               589824
DBMS         FCMBP            -                      983040               983040
DBMS         FCM_CHANNEL      -                    35520512             35520512
DBMS         MONITOR          -                      458752               589824
DBMS         RESYNC           -                      262144               262144
DBMS         OSS_TRACKER      -                     7667712              7667712
DBMS         APM              -                    13041664             13238272
DBMS         BSU              -                     3932160              4390912
DBMS         KERNEL_CONTROL   -                     3932160              4390912
DBMS         EDU              -                      655360               655360
FMP          MISC             -                      655360               655360
DATABASE     UTILITY          TESTDB                  65536                65536
DATABASE     PACKAGE_CACHE    TESTDB                 983040               983040
DATABASE     XMLCACHE         TESTDB                 196608               196608
DATABASE     CAT_CACHE        TESTDB                 458752               458752
DATABASE     BP               TESTDB              850132992            850132992
DATABASE     BP               TESTDB                 655360               655360
APPLICATION  APPLICATION      TESTDB                 393216               393216
APPLICATION  APPLICATION      TESTDB                 262144               262144
                       
  21 record(s) selected

Usage notes

In a partitioned database environment, the fast communication manager (FCM) memory set is allocated per host; all members on this host machine share this set. The MON_GET_MEMORY_POOL function retrieves data from each member. Since the FCM memory set is shared among all members on the host, the metrics reported for FCM memory for each member on the host represent information about the same shared memory set. For this reason, when examining metrics for FCM memory, examine the data for each unique host. For hosts with multiple members, use data from only one member on that host, as the metrics for FCM memory represent the aggregated total for all members on the given host.

Information returned

Table 1. Information returned for MON_GET_MEMORY_POOL
Column name	Data type	Description
MEMBER	SMALLINT	member - Database member
HOST_NAME	VARCHAR(255)	host_name - Host name
DB_NAME	VARCHAR(128)	db_name - Database name
MEMORY_SET_TYPE	VARCHAR(32)	memory_set_type - Memory set type. See the `memory_set_type` input parameter for the list of possible types.
MEMORY_POOL_TYPE	VARCHAR(32)	memory_pool_type - Memory pool type.
MEMORY_POOL_ID	BIGINT	memory_pool_id - Memory pool identifier
APPLICATION_HANDLE	BIGINT	application_handle - Application handle. Only applicable to APPLICATION, STATISTICS, STATEMENT, and SORT_PRIVATE memory pool types. Otherwise, the value is NULL.
EDU_ID	BIGINT	edu_ID - Engine dispatchable unit ID monitor element. Only applicable for memory pools allocated from the PRIVATE memory set type. Otherwise, the value is NULL.
MEMORY_POOL_USED	BIGINT	memory_pool_used - Amount of memory pool in use
MEMORY_POOL_USED_HWM	BIGINT	memory_pool_used_hwm - Memory pool high water mark