|
Problem IC97099
|
Status: Closed |
LARGE SORT HEAP USAGE WHEN 0-CARDINALITY INDICATED ON SORT OPERATION |
| product: |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 |
| Problem description: |
DB2 will use large the fully confirgured amount of SORT HEAP
when 0-cardinality is indicated on a SORT operation.
The issue can be identified by examining the query access plan,
and
looking for a SORT with a 0 estimated cardinality. Typically a
0-cardinality function, table, global temporary table, or
session temporary table can cause this plan.
In Access plan :
3) SORT : (Sort)
Cumulative Total Cost: 22.6971
Cumulative CPU Cost: 126925
Cumulative I/O Cost: 2.9975
Cumulative Re-Total Cost: 0.0097435
Cumulative Re-CPU Cost: 34379.9
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 22.6971
Estimated Bufferpool Buffers: 2.9975
Arguments:
---------
BUFFSORT: (Sort for Buffering Rows)
TRUE
DUPLWARN: (Duplicates Warning flag)
FALSE
NUMROWS : (Estimated number of rows)
0 <--------------------------------0 cardinality estimated.
ROWWIDTH: (Estimated width of rows)
424
SORTKEY : (Sort Key column)
NONE
TEMPSIZE: (Temporary Table Page Size)
4096
UNIQUE : (Uniqueness required flag)
FALSE |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All users with large sort heap configured and 0-cardinality *
* tables *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Customers experiencing this issue should upgrade to DB2 *
* Cancun Release 10.5.0.4 or higher. *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 9 for Linux, UNIX, and Windows
|
| Solution |
Update to DB2 Cancun Release 10.5.0.4 and higher contain this
fix.
Customers should no longer see maximum sort heap allocations for
0-cardinality SORT operations. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 22.10.2013
06.10.2014
06.10.2014 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.5.0.4
|
 |
