DB2 - Problembeschreibung
Problem IC71509 | Status: Geschlossen |
SELECT SQL STATEMENT WILL RETURN DIFFERENT RESULTS BY SETTING CURRENT DEGREE =1 AND CURRENT DEGREE =ANY | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 | |
Problembeschreibung: | |
The problem can only occur when INTRA_PARALLEL is ON and the query degree is > 1. The SQL statement itself must have DISTINCT operation above non-pushdownable predicate, for example, SELECT DISTINCT ... GROUP BY ... HAVING COUNT(column) > 1 . In such cases the optimizer may choose to do a UNIQUE SORT and place it before the predicate is applied. The problem can be determined by using db2exfmt tool. The steps are listed as follows: 1) db2 set current explain mode explain 2) db2 -tvf <sql_script> (eg: sql.ddl) 3) db2 set current explain mode no 4) db2exfmt -d <DB_NAME> -g TIC -w -1 -n % -s % -# 0 -o <FILE_NAME> (eg: exfmt.txt) The similar following entries will be written in the formatted explain table file in step 4. In database context section, the intra-parallelism is set on. In access plan graph section, the Query Degree is >1 . The uniqueness flag of the SORT(13) operator before the FILTER(11) predicate is set to TRUE. Database Context: ---------------- Parallelism: Intra-Partition Parallelism Access Plan: ----------- Total Cost: 1.82934e+06 Query Degree: 8 FILTER ( 11) 731537 33459 | 453941 TBSCAN ( 12) 723457 33459 | 453941 SORT ( 13) 679313 32222 Extended Diagnostic Information: -------------------------------- 11) FILTER: (Filter) Cumulative Total Cost: 731537 Cumulative CPU Cost: 1.68065e+10 Cumulative I/O Cost: 33459 Cumulative Re-Total Cost: 52222.8 Cumulative Re-CPU Cost: 1.27465e+09 Cumulative Re-I/O Cost: 1237 Cumulative First Row Cost: 721993 Estimated Bufferpool Buffers: 3047 Predicates: ---------- 19) Residual Predicate Comparison Operator: Less Than (<) Subquery Input Required: No Filter Factor: 0.333333 Predicate Text: -------------- (1 < Q7.$C1) 13) SORT : (Sort) Cumulative Total Cost: 679313 Cumulative CPU Cost: 1.55318e+10 Cumulative I/O Cost: 32222 Cumulative Re-Total Cost: 0 Cumulative Re-CPU Cost: 0 Cumulative Re-I/O Cost: 1237 Cumulative First Row Cost: 679313 Estimated Bufferpool Buffers: 5528 Arguments: --------- DUPLWARN: (Duplicates Warning flag) FALSE NUMROWS : (Estimated number of rows) 453942 PARTCOLS: (Table partitioning columns) 1: Q8."C1" ROWWIDTH: (Estimated width of rows) 16 SORTKEY : (Sort Key column) 1: Q7."C1"(A) SORTTYPE: (Intra-Partition parallelism sort type) PARTITIONED SPILLED : (Pages spilled to bufferpool or disk) 2481 TEMPSIZE: (Temporary Table Page Size) 4096 UNIQUE : (Uniqueness required flag) TRUE | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * The problem can only occur when INTRA_PARALLEL is ON and the * * query degree is > 1. The SQL statement itself must have * * DISTINCT operation above non-pushdownable predicate, for * * example,SELECT DISTINCT ... GROUP BY ... HAVING * * COUNT(column) > 1 . * * In such cases the optimizer may choose to do a UNIQUE SORT * * and place it before the predicate is applied. * * * * The problem can be determined by using db2exfmt tool. The * * steps are listed as follows: * * * * 1) db2 set current explain mode explain * * * * 2) db2 -tvf <sql_script> (eg: sql.ddl) * * * * 3) db2 set current explain mode no * * * * 4) db2exfmt -d <DB_NAME> -g TIC -w -1 -n % -s % -# 0 -o * * <FILE_NAME> (eg: exfmt.txt) * * * * The similar following entries will be written in the * * formatted explain table file in step 4. In database context * * section, the intra-parallelism is set on. In access plan * * graph section, the Query Degree is >1 . * * The uniqueness flag of the SORT(13) operator before the * * FILTER(11) predicate is set to TRUE. * * * * Database Context: * * ---------------- * * Parallelism: Intra-Partition Parallelism * * * * * * Access Plan: * * ----------- * * Total Cost: 1.82934e+06 * * Query Degree: 8 * * * * FILTER * * ( 11) * * 731537 * * 33459 * * | * * 453941 * * TBSCAN * * ( 12) * * 723457 * * 33459 * * | * * 453941 * * SORT * * ( 13) * * 679313 * * 32222 * * * * Extended Diagnostic Information: * * -------------------------------- * * 11) FILTER: (Filter) * * Cumulative Total Cost: 731537 * * Cumulative CPU Cost: 1.68065e+10 * * Cumulative I/O Cost: 33459 * * Cumulative Re-Total Cost: 52222.8 * * Cumulative Re-CPU Cost: 1.27465e+09 * * Cumulative Re-I/O Cost: 1237 * * Cumulative First Row Cost: 721993 * * Estimated Bufferpool Buffers: 3047 * * * * Predicates: * * ---------- * * 19) Residual Predicate * * Comparison Operator: Less Than (<) * * Subquery Input Required: No * * Filter Factor: 0.333333 * * * * Predicate Text: * * -------------- * * (1 < Q7.$C1) * * * * * * 13) SORT : (Sort) * * Cumulative Total Cost: 679313 * * Cumulative CPU Cost: 1.55318e+10 * * Cumulative I/O Cost: 32222 * * Cumulative Re-Total Cost: 0 * * Cumulative Re-CPU Cost: 0 * * Cumulative Re-I/O Cost: 1237 * * Cumulative First Row Cost: 679313 * * Estimated Bufferpool Buffers: 5528 * * * * Arguments: * * --------- * * DUPLWARN: (Duplicates Warning flag) * * FALSE * * NUMROWS : (Estimated number of rows) * * 453942 * * PARTCOLS: (Table partitioning columns) * * 1: Q8."C1" * * ROWWIDTH: (Estimated width of rows) * * 16 * * SORTKEY : (Sort Key column) * * 1: Q7."C1"(A) * * SORTTYPE: (Intra-Partition parallelism sort type) * * PARTITIONED * * SPILLED : (Pages spilled to bufferpool or disk) * * 2481 * * TEMPSIZE: (Temporary Table Page Size) * * 4096 * * UNIQUE : (Uniqueness required flag) * * TRUE * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 version 9.5 fix pack 8. * **************************************************************** | |
Local-Fix: | |
This can be worked around by turning INTRA_PARALLEL OFF, setting the DB config parameter DFT_DEGREE = 1, setting the DBM config parameter MAX_QUERYDEGREE = 1 or setting the special register CURRENT DEGREE 1. | |
verfügbare FixPacks: | |
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows | |
Lösung | |
The fix will be included in DB2 Version 9.5 fix pack 8. | |
Workaround | |
This can be worked around by turning INTRA_PARALLEL OFF, setting the DB config parameter DFT_DEGREE = 1, setting the DBM config parameter MAX_QUERYDEGREE = 1 or setting the special register CURRENT DEGREE 1. | |
Bug-Verfolgung | |
Vorgänger : APAR is sysrouted TO one or more of the following: IC71993 IC71994 Nachfolger : | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 27.09.2010 05.07.2011 05.07.2011 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.5.FP8 | |
Problem behoben lt. FixList in der Version | |
9.5.0.8 |