Question & Answer
Question
Why access plan happens to be changed to TBSCAN from expected IXSCAN and its cost is getting much bigger even if runstats is not run?
Cause
DB2 query optimizer uses statistics available in the catalog tables to determine the most efficient access plan for any given query. In the case when there are no statistics available in the catalog tables, the optimizer may decide to estimate statistics with table information such as number of data pages.
Since V8 FixPak 10 and V9.1 GA level, the statistics fabrication algorithm use the UDI counters (the number of update, delete, and insert operations that have been performed) in addition to the table information applied.
The fabricated statistics are saved in the internal packed descriptor if its statistics show a smaller number of records compared to the Update, Delete, and Insert (UDI) counters. If the newly fabricated statistics show a larger number of records compared to the UDI counters, the same fabricated statistics may still remain until it is reset by runstats because the statistics fabrication does not work when npages > 1, fpages > 1 and card > 1 in cache and some of those value may be increased by the previous fabrication.
The change in the fabricated statistics may lead to changes in the plan chosen for the query execution.
Answer
We have two kinds of solutions against this issue as follows.
A) Update statistics through runstats. The runstats will update the table statistics, reset UDI counters, and may increase fpage. So fabrication does not need to work in this case.
B) To avoid TBSCAN as much as possible in the access plan, the following workarounds are applicable.
B-1) db2set DB2_REDUCED_OPTIMIZATION=UNIQUEINDEX
UNIQUEINDEX
The UNIQUEINDEX keyword enables a reduced optimization technique in the query optimizer to favor fully qualified unique index accesses. An unique index access is fully qualified if all columns in the key are qualified by equality predicates. This is a safe setting to use. It is also on by default when DB2_REDUCED_OPTIMIZATION is enabled (e.g. DB2_REDUCED_OPTIMIZATION=ON).
B-2) alter table <table_name> VOLATILE CARDINALITY ( if user table can be changed ).
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21272850