[DB2-L] Costly Conundrum

Walter Janißen

[DB2-L] Costly Conundrum
Hi Andy

I also see that very often, especially in V8. In V9 there is a new column in DSN_STATEMNT_TABLE: TOTAL_COST. And almost in all cases, this column has the lowest value for the chosen access path. When I tried to change the access path using optimizer hints or by disabling the index, the total_cost was almost always higher, even though the PROCMS and PROCMU are lower. According to that, what Terry explained to me, the optimizer always tries to find the access path with the lowest elapsed time.

I think, this really doesn't help you, because you have proven, that X2 is much better than X3 (regarding elapsed time, which is supposed to count). And I must admit, that I haven't seen such a high difference in PROCSU (I assume, you are talking about that column, when you say costs) and the total_costs (which unfortunately isn't externalized in V8) should be less.

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Andy Hunt
Gesendet: Mittwoch, 9. Februar 2011 00:24
An: [login to unmask email]
Betreff: Re: [DB2-L] Costly Conundrum

Hi,
Thanks for all replies.

I've reorged, runstated etc and tried different runstat parameter variations, but my point is more of a generic one....

DB2 is looking at the clusterratio, cardinality and all the other access path fields etc... and its coming up with a cost value of 178,000 when it uses X3.

When X3 is dropped, it looks at clusteratio, cardinality etc. and uses X2 with a cost value of 36.

When X2 and X3 existed and the optimiser is considering all access paths (this is not a complex query either), it has a choice of X3 (cost 178,000) or X2 (36). Shouldn't a cost based optimiser choose the lower cost option? ....Always?


Sure, I can understand that it may get the cost value wildly wrong if given incorrect stats.....but I'd still expect it to use the lowest cost value option that it generates based on those (incorrect) stats.

Thanks, Andy

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv