DB2 - Problembeschreibung
Problem IC98157 | Status: Geschlossen |
DB2 OPTIMIZER MAY USE FREQUENCY VALCOUNT IN SYSSTAT.COLDIST INCORRECTLY AND GIVE SUB-OPTIMIAL ACCESS PLAN. | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
Under rare circumstances, DB2 optimizer may use frequency VALCOUNT of a COLVALUE that doesn't match with the value in a query. The problem happens on CHAR or VARCHAR data type columns only. For example, 1. In TAB1, value "0A00000005" appears in 1000 rows, and others values appear in 1 row only. db2 "select count(*),c1 from tab1 group by c1" 1 C1 ----------- ---------- 1 0000000009 1 0A00000000 1000 0A00000005 1 0A00000019 1 0A00000123 1 0A99900999 1 0B99990099 2. SYSSTAT.COLDIST has valid data, and frequency VALCOUNT of "0A00000005" is 1000. select substr(colvalue, 1, 30) colvalue, valcount from sysstat.coldist where colname = 'C1' and tabname ='TAB1' and tabschema = '<SCHEMA>' and TYPE = 'F' COLVALUE VALCOUNT ------------------------------ -------------------- '0A00000005' 1000 - -1 - -1 - -1 - -1 3. For the following query, optimizer incorrectly uses frequency VALCOUNT of "0A00000005" for "c1='0A00000019'" predicate and estimate 1000 returned rows. select * from tab1 where c1='0A00000019'; Rows RETURN ( 1) Cost I/O | 1000 DTQ ( 2) 0.850451 0 | 1000 IXSCAN ( 3) 0.502298 0 | 1006 INDEX: SHULIN TAB1_IDX1 Q1 | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * All users * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 version 9.7 fixpack 10 * **************************************************************** | |
Local-Fix: | |
statistical views can be used to work around the issue. | |
Lösung | |
Problem was first fixed in DB2 version 9.7 fixpack 10 | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Bug-Verfolgung | |
Vorgänger : APAR is sysrouted TO one or more of the following: IC98769 IC98771 Nachfolger : | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 05.12.2013 10.11.2014 10.11.2014 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7.FP10 | |
Problem behoben lt. FixList in der Version | |
9.7.0.10 |