After upgrading to DB2 v9.7, a query may have poorer performance
when an Index Scan (IXSCAN), Fetch with an Index Scan
(FETCH/IXSCAN), or Table Queue / Table Scan (TQ/TBSCAN) is used
instead of a direct TBSCAN on the inner of a Nested Loop Join
(NLJOIN), when the outer of the Nested Loop Join can generate
only a single row.
In order to determine if you are encoutering this issue, the
Query Execution Plan needs to be examined. The QEP can be
generating using the DB2 Explain facility (db2exfmt), or DB2
Optim Query Tuner / Graphical Explain. Ideally, the QEP from the
previous version of DB2 would also be available. Examin the
explain using the following steps:
1) Locate any Nested Loop Joins (NLJOIN)
2) Examine the outer / left leg of the NLJOIN, to determine if
the cardinality of that portion of the plan can generate only a
single row.
- Plans that use a Group By, GENROW, or a fully qualified
primary key access are most likely to generate only a single row
3) Examine the inner of the NLJOIN. If the inner portion of the
plan uses an IXSCAN, FETCH/IXSCAN, or a TQ, then you may be
experiencing this problem:
- if the IXSCAN, FETCH/IXSCAN or TQ with any of an IXSCAN,
FETCH/IXSCAN or TBSCAN, result in scanning the entire inner
table, or almost the entire inner table
- A FETCH/IXSCAN or TQ/FETCH/IXSCAN is used, and the index is
very poorly clustered (cr, or cf are less then approx 20% - 50%)
- If you have access to a plan from the previous version of
DB2, and you find a TBSCAN on the inner of this NLJOIN, and the
outer used a TQ to send the single row to the TBSCAN.
If the above conditions are met, then it is likely you are
experiencing the issue addressed by this APAR fix. This APAR fix
will allow the Table Scan (TBSCAN) to be generated on the inner
of the Nested Loop Join (NLJOIN) when the outer only produces a
single row. |
