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]
-
- Non-partitioned environments
- OFF [ON; OFF]
- Partitioned environments
- OFF [ON; OFF]
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.
- 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.
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), indicating that it is receiving tuning information from the tuning node. 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.
- 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
Instances created in Db2 Version 9 will have these health indicators disabled by default.