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
- Parameter type
- Configurable online
- Configurable by member in a Db2® pureScale® environment
- Propagation class
- Default [range]
- Non-partitioned environments
- OFF [ON; OFF]
- Partitioned environments
- OFF [ON; OFF]
- Note: The default value is subject to change by the Db2 Configuration Advisor as part of 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.
- 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.
The following values indicate:
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
- 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.