|
Problem IC62066
|
Status: Closed |
ROWID, RID_BIT(), and RID() PERFORMANCE SLOW IN SOME SQL QUERIES |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
ROWID, RID_BIT(), or RID() expressions in SQL queries may affect
performance in the following scenarios:
1) if the ROWID, RID_BIT(), or RID() is the left or right hand
side of an equal predicate, and the other side is not a column,
host variable, global variable, or parameter marker, then the
access method maybe SCAN instead of FETCH. For example, given a
UDF sales_rowid() that returns a VARCHAR(16) FOR BIT DATA rowid
of the SALES table:
select * from sales where rowid = sales_rowid();
Depending on the number of rows, the direct FETCH access method
may perform better than the SCAN access method.
2) if ROWID, RID_BIT(), or RID() is in the select list of a
query or sub-query, and there are no other expressions or long
or lob columns, then SQL run time query performance maybe
affected compared to the same select list without the ROWID,
RID_BIT(), or RID() expression. For example:
select * from sales;
may perform better than:
select sales.*, rowid from sales;
This is true even though a similar query execution plan is made
and is an affect of the runtime performance.
3) the command "db2pd -tcbscans" may incorrectly count direct
FETCH access as a "Scans", so "Scans" count maybe too high.
Local Fix:
The local fixes for the above three issues are:
1) use ROWID, RID_BIT() or RID() in equal predicates, comparing
with column, host variable, global variable or parameter marker.
Verify the access method is FETCH with, for example, db2exfmt.
2) there is no workaround for the runtime performance of ROWID,
RID_BIT() or RID() in select list.
3) be aware that the "db2pd -tcbscans" reporting of "Scans"
maybe inflatted for ROWID, RID_BIT() or RID() direct FETCH table
accesses. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* DB2 LUW v97. *
****************************************************************
* PROBLEM DESCRIPTION: *
* ROWID, RID_BIT(), or RID() expressions in SQL queries may *
* affect *
* performance in the following scenarios: *
* *
* 1) if the ROWID, RID_BIT(), or RID() is the left or right *
* hand *
* side of an equal predicate, and the other side is not a *
* column, *
* host variable, global variable, or parameter marker, then *
* the *
* access method maybe SCAN instead of FETCH. For example, *
* given a *
* UDF sales_rowid() that returns a VARCHAR(16) FOR BIT DATA *
* rowid *
* of the SALES table: *
* *
* *
* *
* select * from sales where rowid = sales_rowid(); *
* *
* *
* *
* Depending on the number of rows, the direct FETCH access *
* method *
* may perform better than the SCAN access method. *
* *
* *
* *
* 2) if ROWID, RID_BIT(), or RID() is in the select list of a *
* *
* query or sub-query, and there are no other expressions or *
* long *
* or lob columns, then SQL run time query performance maybe *
* *
* affected compared to the same select list without the ROWID, *
* *
* RID_BIT(), or RID() expression. For example: *
* *
* select * from sales; *
* *
* may perform better than: *
* *
* select sales.*, rowid from sales; *
* *
* This is true even though a similar query execution plan is *
* made *
* and is an affect of the runtime performance. *
* *
* *
* *
* 3) the command "db2pd -tcbscans" may incorrectly count *
* direct *
* FETCH access as a "Scans", so "Scans" count maybe too high. *
****************************************************************
* RECOMMENDATION: *
* Install DB2 LUW v97 fixpak 1. *
**************************************************************** |
| Local Fix: |
The local fixes for the above three issues are:
1) use ROWID, RID_BIT() or RID() in equal predicates,
comparing with column, host variable, global variable or
parameter marker. Verify the access method is FETCH with, for
example, db2exfmt.
2) there is no workaround for the runtime performance of ROWID,
RID_BIT() or RID() in select list.
3) be aware that the "db2pd -tcbscans" reporting of "Scans"
maybe inflatted for ROWID, RID_BIT() or RID() direct FETCH
table accesses. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Fixed in DB2 LUW v97 fixpak 1. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 16.07.2009
17.02.2010
17.02.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
