|
Problem IC71509
|
Status: Closed |
SELECT SQL STATEMENT WILL RETURN DIFFERENT RESULTS BY SETTING CURRENT DEGREE =1 AND CURRENT DEGREE =ANY |
| product: |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 |
| 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 |
| Problem Summary: |
****************************************************************
* 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. |
| available fix packs: |
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
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-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC71993 IC71994
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 27.09.2010
05.07.2011
05.07.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.5.FP8
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.5.0.8
|
 |
