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