DB2 Optimization Hint works on 1 subsystem/DS group but not another

Donna Domovic

DB2 Optimization Hint works on 1 subsystem/DS group but not another

We've run into a strange situation that has us scratching our heads.  We have a DB2 package that had not been rebound for several versions of DB2 (I know this is bad) but was automatically rebound after we upgraded to DB2 v11 CM.  After the upgrade, the access path changed with the order of the 2 indexes being swapped.  This resulted in the run time and CPU time both increasing by 5-6 times.

The package did not use a hint but we tried to add one pointing to the old access path.  When we do this, even though the bind says it used the hint, the rows in the PLAN_TABLE still list the indexes in the post v11 order resulting in the longer execution and CPU times.

We were able to get the hint to work on one of our development systems the way that we want it to but we can't get it to work on another dev system or on the prod system.

We have taken a look at the DB2 zparms and they appear to be the same across subsystems except for values that you'd expect to be different.  We've also tried various things including updating stats and changing one of the indexes but we still can't get the hint to work or the access path back to where it was before the v11 bind.

Does anyone have any ideas of what we should try or look at to get the access path back to where we want/need it to be or to determine why the hint is working as expected on the 1 dev subsystem/DS group but not on the others?

Thanks in advance.

Donna Domovic

 

Michael Hannan

RE: DB2 Optimization Hint works on 1 subsystem/DS group but not another
(in response to Donna Domovic)

Donna,

Even your text is quite long, your post is still a little vague for me about the actual access path. You should show the path. If it is an MX/MI access path, IBM does not give you full control over that access path (officially), so Hints capabilities are limited with what influence you are permitted. So in past you may have thought a Hint worked 100%,  but got lucky with the MX/MI steps, being what you wanted even the Optimizer was NOT taking note of all PLAN_TABLE columns in the OPTHINT. I think they were concerned about MX hints being too complex.

Db2 V12 ZOS should make substantial improvements for MX/MI with adaptive methods (solving earlier problems), but V10 and V11 can be very bad due to a bad filtering estimation, and due to runtime overflow of a bad MX leg to work file instead of dropping it, as in V9. The order of MX/MI legs before V12 might be  critical. If DB2 gets down to less than 32 RIDs (or some other threshold) it can ignore remaining MX/MI legs. A leg with millions of RIDs qualifying might be able to be dropped instead of overflowing to a work file (not the best idea in all cases).

V11 migration did change some MX access paths, and often for the better fixing previous bad performance. It is still possible for some to go the other way.

If I guessed wrong I could be on a tangent. I think of this because you say the indexes switched rather than table join sequence switched.

Show your SQL and access path and Index col definitions and we could do better. I could probably tell how to fix an MX/MI problem if have the detail. Have seen these blow up very badly.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Jun 19, 2019 - 02:59 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jun 19, 2019 - 03:00 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jun 19, 2019 - 03:04 PM (Europe/Berlin)

Stefan Kolev

RE: DB2 Optimization Hint works on 1 subsystem/DS group but not another
(in response to Donna Domovic)

Donna, you could try manipulating Nlevels and Clustering% to make one index appear more attractive to the optimizer.  If that works you may not need a Hint after all.

Stefan Kolev | Database Services | |D|S|T| SS&C

Walter Janißen

AW: DB2 Optimization Hint works on 1 subsystem/DS group but not another
(in response to Donna Domovic)
Hi

I once experienced different explains, when a package was bound on member A and member B, but if I remember right it was the only time in all the years with Db2.
Does this phenomenon occur always for that statement?

Did you try to store your hint as a statement level hint in SYSQUERY in the dev- and production-system and then rebind that package?

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Donna Domovic <[login to unmask email]>
Gesendet: Dienstag, 18. Juni 2019 18:42
An: [login to unmask email]
Betreff: [DB2-L] - DB2 Optimization Hint works on 1 subsystem/DS group but not another


We've run into a strange situation that has us scratching our heads. We have a DB2 package that had not been rebound for several versions of DB2 (I know this is bad) but was automatically rebound after we upgraded to DB2 v11 CM. After the upgrade, the access path changed with the order of the 2 indexes being swapped. This resulted in the run time and CPU time both increasing by 5-6 times.

The package did not use a hint but we tried to add one pointing to the old access path. When we do this, even though the bind says it used the hint, the rows in the PLAN_TABLE still list the indexes in the post v11 order resulting in the longer execution and CPU times.

We were able to get the hint to work on one of our development systems the way that we want it to but we can't get it to work on another dev system or on the prod system.

We have taken a look at the DB2 zparms and they appear to be the same across subsystems except for values that you'd expect to be different. We've also tried various things including updating stats and changing one of the indexes but we still can't get the hint to work or the access path back to where it was before the v11 bind.

Does anyone have any ideas of what we should try or look at to get the access path back to where we want/need it to be or to determine why the hint is working as expected on the 1 dev subsystem/DS group but not on the others?

Thanks in advance.

Donna Domovic



-----End Original Message-----
Attachments

  • image001.png (2.6k)

Donna Domovic

RE: AW: DB2 Optimization Hint works on 1 subsystem/DS group but not another
(in response to Walter Janißen)

Thanks to everyone for all the information.  We were finally able to fix the access path without adding a hint.  We did that by clearing out the access path stats for the table and indexes, ran RUNSTATS again, then bound the package without a hint and it picked the good access path with the indexes accessed in the correct order.  This resulted in the following improvements:

 

Bad access path:  173 minutes 23 seconds CPU; 6 hours 10 minutes elapsed time

Good access path:  3 minutes 9 seconds CPU; 27 minutes elapsed time

 

At this point, we're back to where we want to be.

Thanks,

Donna Domovic

Michael Hannan

RE: AW: DB2 Optimization Hint works on 1 subsystem/DS group but not another
(in response to Donna Domovic)

I normally recommend collecting Runstats without clearing out Stats first. Clearing Stats leaves possibility of danger access paths for Dynamic SQL for a start. Collecting new Stats does automatically replace old stats of the same type. So if you are going to collect Stats on an index with mandatory KEYCARD, there is no need to clean out stats for that index or the associated SYSCOLDIST Type 'C' Stats collected by KEYCARD. The special exception is old Stats are so corrupted that they don't get automatically replaced.

I saw a site use RESET ACCESSPATH on RUNSTATS with bad repercussions, and Terry Purcell advises against using that too.

I would reserve clearing out Stats for corrupt Catalog Stats which I have seen from 3rd party products, and completely useless stats like 15 column Frequent Value Stats (when Index match cols never exceeds 3), as an example, or least frequent values not captured for any reason.

Capturing lots of Histogram Stats is largely not needed. Capture them for specific reasons, makes more sense. Cases where the Optimizer can use them might not be great for static SQL.

I always like to have a reason for doing things, which means trying to understand how Db2 can actually use the Stats, and what happens if it has not got them or they become very misleading. 

Above all, Stats that say a table is empty are quite dangerous. I would not do that for tables where growth is expected. If you have got tables, currently with empty or almost empty Stats, but table growth is expected or possible, at least set the table to VOLATILE, to let the optimizer know dynamic growth will happen, and to choose matching indexes over tablespace scans.

So I would say, if you create a table, don't Runstat it until after the data is loaded. Empty Stats is probably a lot worse than Default Stats. If there is no data to be loaded, but growth will come with usage, then make it Volatile, at least till it reaches a size that does not favour tablespace scan, then Runstat it.

Always have a health check to detect tables that have grown much larger than the Stats say as a percentage, so indicate new Stats are urgently needed. Zero stats is a special case of this. If CARDF for a table is 10, but realtime stats say it has 100 rows, that is 1000% out, potentially bad. If it now has 1 million rows, that can be disaster for access paths. Volatile specified would be safer in my view.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd