By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,586 Members | 2,418 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,586 IT Pros & Developers. It's quick & easy.

MQTs: Similar timeron costs, vastly different execution times

P: n/a
Friends:

I've been working on tuning a fairly complex--and certainly vexing--
query. Several days ago I created an MQT which aided the query and
dropped its cost from 3.4 million timerons to around 650,000, enabling
the query to run in cir. 15 mins. For a variety of reasons, I've
recently created another MQT on a different set of tables than those
covered by the other one, which I've since dropped. Using this new MQT
the query's cost dropped to about 633,000 timerons, yet, unlike
before, the query runs for hours before having to be killed.

This latter MQT is a better one, as it's more generic and thus re-
usable, but what's puzzling me is why this query never returns a
result set when the other MQT--aiding the same query and showing a
virtually identical cost--returned one in 15 mins. Do timerons not
tell the "whole story"?

The environment is DB2 UDB LUW 8.2.3 on AIX 5.x.

Let me know what else you need from me, and thanks.

--Jeff

Feb 16 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Feb 15, 11:55 pm, "jefftyzzer" <jefftyz...@sbcglobal.netwrote:
Friends:

I've been working on tuning a fairly complex--and certainly vexing--
query. Several days ago I created an MQT which aided the query and
dropped its cost from 3.4 million timerons to around 650,000, enabling
the query to run in cir. 15 mins. For a variety of reasons, I've
recently created another MQT on a different set of tables than those
covered by the other one, which I've since dropped. Using this new MQT
the query's cost dropped to about 633,000 timerons, yet, unlike
before, the query runs for hours before having to be killed.

This latter MQT is a better one, as it's more generic and thus re-
usable, but what's puzzling me is why this query never returns a
result set when the other MQT--aiding the same query and showing a
virtually identical cost--returned one in 15 mins. Do timerons not
tell the "whole story"?

The environment is DB2 UDB LUW 8.2.3 on AIX 5.x.

Let me know what else you need from me, and thanks.

--Jeff
Timerons are an estimate of the relative cost of an SQL statement. DB2
uses the statistics from runstats utility to help estimate the cost of
the SQL statement. As you may know, there are varying degrees of
statistics that can be gathered. Even so, DB2 must sometimes make
certain assumptions about the data, especially the distribution of
values for a given column, that may not turn out to be accurate.

One thing you can do to improve the accuracy of the timeron estimates
is to execute runstats capturing more detail than the defaults. This
includes capturing distribution of values for key columns (or even all
columns), and detailed stats on indexes. Even with these more detailed
stats, sometimes the optimizer may have to make certain assumptions
that are not accurate.

For decision support queries you could also try to use optimization
level 7.

Feb 16 '07 #2

P: n/a
On Feb 15, 11:11 pm, "Mark A" <m00...@yahoo.comwrote:
On Feb 15, 11:55 pm, "jefftyzzer" <jefftyz...@sbcglobal.netwrote:


Friends:
I've been working on tuning a fairly complex--and certainly vexing--
query. Several days ago I created an MQT which aided the query and
dropped its cost from 3.4 million timerons to around 650,000, enabling
the query to run in cir. 15 mins. For a variety of reasons, I've
recently created another MQT on a different set of tables than those
covered by the other one, which I've since dropped. Using this new MQT
the query's cost dropped to about 633,000 timerons, yet, unlike
before, the query runs for hours before having to be killed.
This latter MQT is a better one, as it's more generic and thus re-
usable, but what's puzzling me is why this query never returns a
result set when the other MQT--aiding the same query and showing a
virtually identical cost--returned one in 15 mins. Do timerons not
tell the "whole story"?
The environment is DB2 UDB LUW 8.2.3 on AIX 5.x.
Let me know what else you need from me, and thanks.
--Jeff

Timerons are an estimate of the relative cost of an SQL statement. DB2
uses the statistics from runstats utility to help estimate the cost of
the SQL statement. As you may know, there are varying degrees of
statistics that can be gathered. Even so, DB2 must sometimes make
certain assumptions about the data, especially the distribution of
values for a given column, that may not turn out to be accurate.

One thing you can do to improve the accuracy of the timeron estimates
is to execute runstats capturing more detail than the defaults. This
includes capturing distribution of values for key columns (or even all
columns), and detailed stats on indexes. Even with these more detailed
stats, sometimes the optimizer may have to make certain assumptions
that are not accurate.

For decision support queries you could also try to use optimization
level 7.- Hide quoted text -

- Show quoted text -
Mark:

Thanks for your considered reply. I should have mentioned that all
tables were REORGed (offline) and RUNSTATSed before I ran my query.
However, I believe the stats were SAMPLED, so I will revisit all
stats.

--Jeff

Feb 16 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.