DB2 - Problem description
Problem IT27825 | Status: Closed |
DB2: HIGH NUMBER OF SQL VARIATIONS & ANCHOR_COMMON LATCH WAITS CAUSING CPU SPIKE | |
product: | |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 | |
Problem description: | |
Under rare runtime condition, Db2 might not reuse the dynamic SQL variation across multiple connection, which will result in: - multiple variations of the same dynamic SQL kept in package cache - an increased time spent compiling queries - latch waits on SQLO_LT_sqlrlc_anchor_common__anchor_latch, which is obtained during compilation Problem is specific to Db2 version 10.5 Fix Pack 9 and 10 (i.e. it did not exist in FP8 and older) can be diagnosed by reviewing "db2pd -dynamic" output, were we would see high number of variations (NumVar) for the same statement (AnchID, StmtUID pair), e.g.: Dynamic SQL Statements: AnchID StmtUID NumEnv NumVar NumRef NumExe Text 851 1 1 19058 13867834 13867834 Dynamic SQL Variations: AnchID StmtUID EnvID VarID NumRef Typ 851 1 20 1520360 1 6 851 1 20 1520359 1 6 851 1 20 1520358 1 6 851 1 20 1520357 1 6 851 1 20 1520356 1 6 | |
Problem Summary: | |
Local Fix: | |
Setting an internal access plan manager (APM) variable may help resolve the problem: $ db2set APM_THROTTLE=70 (instance restart required) | |
Solution | |
Workaround | |
not known / see Local fix | |
Comment | |
This APAR is a duplicate of IT27830 | |
BUG-Tracking | |
forerunner : follow-up : IT27829 | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 21.01.2019 20.04.2020 20.04.2020 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |