Costly Conundrum

Andy Hunt

Costly Conundrum
Hi,
DB2 V8 on ZOS.

Ive a query that has multiple indexes, lets say X1, X2, X3 and X4 on table T1.

I run my query. It uses index X3 and it runs extremely slow. It has a cost of 178,000 (or thereabouts).

I drop index X3, bind the package, run the query and it now uses index X2. This query runs in seconds and has a cost of 36.

DB2 had the option of X2, even when X3 was there, so why is my cost based optimiser letting me down?

If I recreate X3 again, rebind the package, the query picks up X3 again with 178,000 cost.

I'm seeing the same symptoms on other SQL in the system.

Does DB2 always choose the lowest cost?

Anyone seen this feature before at V8 (or any other level)?

Cheers, 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

Steen Rasmussen

Re: Costly Conundrum
(in response to Andy Hunt)
Hi Andy - How many columns exist in those indexes and what are the MATCHCOLS for the respective indexes used. Finally - if not all columns used in the predicates - have you tried RUNSTATS COLGROUP COLNO ?

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect
IBM Certified Database Associate - DB2 9 Fundamentals
IBM Certified database Administrator - DB2 9 DBA for z/OS



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Andy Hunt
Sent: Tuesday, February 08, 2011 1:16 PM
To: [login to unmask email]
Subject: [DB2-L] Costly Conundrum

Hi,
DB2 V8 on ZOS.

Ive a query that has multiple indexes, lets say X1, X2, X3 and X4 on table T1.

I run my query. It uses index X3 and it runs extremely slow. It has a cost of 178,000 (or thereabouts).

I drop index X3, bind the package, run the query and it now uses index X2. This query runs in seconds and has a cost of 36.

DB2 had the option of X2, even when X3 was there, so why is my cost based optimiser letting me down?

If I recreate X3 again, rebind the package, the query picks up X3 again with 178,000 cost.

I'm seeing the same symptoms on other SQL in the system.

Does DB2 always choose the lowest cost?

Anyone seen this feature before at V8 (or any other level)?

Cheers, 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 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

Roger Hecq

Re: Costly Conundrum
(in response to Steen Rasmussen)
I have several questions for which I don't see the answers in your
e-mail. My immediate suspicion is that the stats are either
inconsistent or inaccurate.

1. How current and accurate are the Runstats on the table and it
indexes?
2. Was the table reorged before running the Runstats utility?
3. Also, are you collecting statistics on all of the indexed columns?
4. Do the indexes have about the same number of columns and have you
Keycard . . . Numcols for the Runstats utility
5. Are the clusterratiof, matchcols, etc comparable for the 2 indexes?
6. Is one of the indexes sufficiently larger than the other such that
there is a significant difference in the number of levels in the index
structure?
7. Is the Order By clause causing the X3 index to be chosen?


Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Andy Hunt
Sent: Tuesday, February 08, 2011 2:16 PM
To: [login to unmask email]
Subject: [DB2-L] Costly Conundrum

Hi,
DB2 V8 on ZOS.

Ive a query that has multiple indexes, lets say X1, X2, X3 and X4 on
table T1.

I run my query. It uses index X3 and it runs extremely slow. It has a
cost of 178,000 (or thereabouts).

I drop index X3, bind the package, run the query and it now uses index
X2. This query runs in seconds and has a cost of 36.

DB2 had the option of X2, even when X3 was there, so why is my cost
based optimiser letting me down?

If I recreate X3 again, rebind the package, the query picks up X3 again
with 178,000 cost.

I'm seeing the same symptoms on other SQL in the system.

Does DB2 always choose the lowest cost?

Anyone seen this feature before at V8 (or any other level)?

Cheers, 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
Visit our website at http://www.ubs.com

This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.

E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.


UBS reserves the right to retain all messages. Messages are protected
and accessed only in legally justified cases.

_____________________________________________________________________
* 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

George Toolan

Re: Costly Conundrum
(in response to Roger Hecq)
Hi Andy,

If all the usual problems with bad stats have been eliminated, I have one other thing to look at (based on experience). See if the X3 index is showing as clustered (not clustering, physically clustered - cluster ratio >= 94% [might be 95 or 96, been a while]). IBM (at least in the past when I had the same problem) so favored an index that statistically was clustered that it chose the index over an index that had been created for the problem query. I had to run an update (to set cluster ratio to 40%) after every stats on the index that was being chosen incorrectly). This fixed the proble for me. This goes back 10 years so it might not be current.

Good luck!
George

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Andy Hunt
Sent: Tuesday, February 08, 2011 2:16 PM
To: [login to unmask email]
Subject: [DB2-L] Costly Conundrum

Hi,
DB2 V8 on ZOS.

Ive a query that has multiple indexes, lets say X1, X2, X3 and X4 on table T1.

I run my query. It uses index X3 and it runs extremely slow. It has a cost of 178,000 (or thereabouts).

I drop index X3, bind the package, run the query and it now uses index X2. This query runs in seconds and has a cost of 36.

DB2 had the option of X2, even when X3 was there, so why is my cost based optimiser letting me down?

If I recreate X3 again, rebind the package, the query picks up X3 again with 178,000 cost.

I'm seeing the same symptoms on other SQL in the system.

Does DB2 always choose the lowest cost?

Anyone seen this feature before at V8 (or any other level)?

Cheers, 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 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

Myron Miller

Re: Costly Conundrum
(in response to George Toolan)
Quite often. If the distribution is not normally distributed, then it can
happen. Also in certain types of Outer joins. And as others have mentioned, if
stats are not complete all kinds of wild and woolly things can happen. And by
that, I mean full distribution stats, histograms and the works.

But even there, there are fixes for too much stats caused by other factors.
So, In addition, how current are you on maintenance? I know of some fixes for
both V8 and V9 that change the selection of index based upon number of column
hits versus clustering, where it overly favored a clustering index with only a 1
column match over another index with a 5-7 column match - clearly the second one
was more efficient.

In other words, there are a number of access path fixes that have been created
in the last year or so that are needed for non-uniformly distributed queries, as
well as those with CASE statements in the where clause.

Myron



________________________________
From: Andy Hunt <[login to unmask email]>
To: [login to unmask email]
Sent: Tue, February 8, 2011 2:16:08 PM
Subject: [DB2-L] Costly Conundrum

Hi,
DB2 V8 on ZOS.

Ive a query that has multiple indexes, lets say X1, X2, X3 and X4 on table T1.

I run my query. It uses index X3 and it runs extremely slow. It has a cost of
178,000 (or thereabouts).


I drop index X3, bind the package, run the query and it now uses index X2. This
query runs in seconds and has a cost of 36.

DB2 had the option of X2, even when X3 was there, so why is my cost based
optimiser letting me down?

If I recreate X3 again, rebind the package, the query picks up X3 again with
178,000 cost.

I'm seeing the same symptoms on other SQL in the system.

Does DB2 always choose the lowest cost?

Anyone seen this feature before at V8 (or any other level)?

Cheers, 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 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

Andy Hunt

Re: Costly Conundrum
(in response to Myron Miller)
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

Patrick Bossman

Re: Costly Conundrum
(in response to Andy Hunt)
Hi Andy,
It looks weird. Can you recreate the bad path, open a pmr, and send in the doc?

Best regards,
Patrick Bossman

_____________________________________________________________________
* 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

Andy Hunt

Re: Costly Conundrum
(in response to Patrick Bossman)
Hi,
Yes, I'll be raising a PMR. I was just wondering if the DB2 Optimiser ALWAYs used the access path with the lowest cost value.

Thanks, Andy

_____________________________________________________________________
* 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

Roy Boxwell

Re: Costly Conundrum
(in response to Andy Hunt)
yep - It is a cost based optimizer after all. Whenever I have seen this
"in the wild" it has always been due to the fact that the optimizer "sees"
a bunch of data in the SYSCOLDIST for the index that it is "not" using and
decides to use another index. When the "not used" index is killed and its
column freq/card data is deleted from coldist this "extra" data is not
available and so the optimzer falls back to using default filter factors
etc and can then choose another access path and lo-and-behold it is a lot
faster than the "other" one. In my experience this was also often caused
by skewed and wrong data in syscoldist. Check out my APAR list for
RUNSTATS and APAR list for SQL PERFORMANCE and download and run the
Statistics HealthCheck freeware program to validate your DB2 cataog as
sometimes RUNSTATS has bugs you know...and sometimes even - holy cow
batman - the optimizer has bugs....sorry Terry et al

Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



Andy Hunt <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>
09.02.2011 08:13
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie

Thema
Re: [DB2-L] Costly Conundrum






Hi,
Yes, I'll be raising a PMR. I was just wondering if the DB2 Optimiser
ALWAYs used the access path with the lowest cost value.

Thanks, Andy

_____________________________________________________________________
* 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


_____________________________________________________________________
* 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