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.