DB2 10.5 for Linux, UNIX, and Windows

self_tuning_mem- Self-tuning memory configuration parameter

This parameter determines whether the memory tuner will dynamically distribute available memory resources as required between memory consumers that are enabled for self-tuning.

Configuration type

Database

Parameter type

Configurable online
Configurable by member in a DB2® pureScale® environment

Propagation class

Immediate

Default [range]

Single-database partition environments: ON [ON; OFF]
Multi-database partition environments: OFF [ON; OFF]

In a database that is upgraded from an earlier version, self_tuning_mem will retain the old value.

Note: The default value is subject to change by the DB2 Configuration Advisor after initial database creation.

Because memory is being traded between memory consumers, there must be at least two memory consumers enabled for self-tuning in order for the memory tuner to be active. When self_tuning_mem is set to ON, but there are less than two memory consumers enabled for self-tuning, the memory tuner is inactive. (The exception to this is the sort heap memory area, which can be tuned regardless of whether other memory consumers are enabled for self-tuning or not.)

This parameter is ON by default in single database partition environments. In multi-database partition environments, it is OFF by default.

The memory consumers that can be enabled for self-tuning include:

Package cache (controlled by the pckcachesz configuration parameter)
Lock List ( controlled by the locklist and maxlocks configuration parameters)
Sort heap (controlled by the sheapthres_shr and sortheap configuration parameters)
Database shared memory (controlled by the database_memory configuration parameter)
Buffer pools (controlled by the size parameter of the ALTER BUFFERPOOL and CREATE BUFFERPOOL statements)
Note: In DB2 pureScale environments, only the local buffer pools (LBPs) can be managed using the self-tuning memory feature. Memory for group buffer pools (GBPs) is allocated and controlled using the cf_gbp_sz configuration parameter.

To view the current setting for this parameter, use the GET DATABASE CONFIGURATION command specifying the SHOW DETAIL parameter. The possible settings returned for this parameter are:

Self Tuning Memory          (SELF_TUNING_MEM) = OFF
Self Tuning Memory          (SELF_TUNING_MEM) = ON (Active)
Self Tuning Memory          (SELF_TUNING_MEM) = ON (Inactive)
Self Tuning Memory          (SELF_TUNING_MEM) = ON

The following values indicate:

ON (Active) - the memory tuner is actively tuning the memory on the system
ON (Inactive) - that although the parameter is set ON, self-tuning is not occurring because there are less than two memory consumers enabled for self-tuning, or the database or instance is in quiesce mode.
ON without (Active) or (Inactive) - from a query without the SHOW DETAIL option, or without a database connection.

In partitioned environments, the self_tuning_mem configuration parameter will only show ON (Active) for the database partition on which the tuner is running. On all other nodes self_tuning_mem will show ON (Inactive). As a result, to determine if the memory tuner is active in a partitioned database, you must check the self_tuning_mem parameter on all database partitions.

If you have upgraded to DB2 Version 9 from an earlier version of DB2 and you plan to use the self-tuning memory feature, you should configure the following health indicators to disable threshold or state checking:

Shared Sort Memory Utilization - db.sort_shrmem_util
Percentage of sorts that overflowed - db.spilled_sorts
Long Term Shared Sort Memory Utilization - db.max_sort_shrmem_util
Lock List Utilization - db.locklist_util
Lock Escalation Rate - db.lock_escal_rate
Package Cache Hit Ratio - db.pkgcache_hitratio

One of the objectives of the self-tuning memory feature is to avoid having memory allocated to a memory consumer when it is not immediately required. Therefore, utilization of the memory allocated to a memory consumer might approach 100% before more memory is allocated. By disabling these health indicators, you will avoid unnecessary alerts triggered by the high rate of memory utilization by a memory consumer.

Instances created in DB2 Version 9 will have these health indicators disabled by default.