asutime from QMF query cost

william giannelli

asutime from QMF query cost

When given a query cost in QMF like 150 or 325.......how do you calculate ASUTIME from that?

thanks

Bill

Roy Boxwell

asutime from QMF query cost
(in response to william giannelli)
I have always taken these numbers as being basically random... many a time a number like 9762377543 ends quick-as-a-flash whereas 1 sits there for minutes...

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

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

From: william giannelli [mailto:[login to unmask email]
Sent: Tuesday, February 27, 2018 7:48 PM
To: [login to unmask email]
Subject: [DB2-L] - asutime from QMF query cost


When given a query cost in QMF like 150 or 325.......how do you calculate ASUTIME from that?

thanks

Bill

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

James Campbell

asutime from QMF query cost
(in response to william giannelli)
The QMF Query cost is an estimate. From (rather ancient memory) the QMF Query cost is
SQLERRD(4) / 1000 (or is it 10,000 - memory failure at this point). It only has relavance in
comparing two 'similar' queries.

RLF (is that what you are really interested in) is an actual value. Any relationship between
timerons and asutime is purely co-incidental.

James Campbell

On 27 Feb 2018 at 11:47, william giannelli wrote:

>
> When given a query cost in QMF like 150 or 325.......how do you calculate ASUTIME from that?
> thanks
> Bill
>

---
This email has been checked for viruses by AVG.
http://www.avg.com

Michael Hannan

RE: asutime from QMF query cost
(in response to Roy Boxwell)

In Reply to Roy Boxwell:

I have always taken these numbers as being basically random... many a time a number like 9762377543 ends quick-as-a-flash whereas 1 sits there for minutes...

That was also my experience with QMF Queries as well (many years ago), not quite random, since the same query had consistent cost numbers, however the more complex the query, the more meaningless became the QMF cost number.

The Optimizer is improving in recent years. At one time in the past, correlated (NOT)EXISTS subqueries were estimated to have no filtering. Now they have a filter factor but not a good one. Those subqueries probably caused QMF cost estimates to go unrealistically high. Meanwhile skewed data causes cost estimates to be unrealistically low. Also Range predicates on special registers like CURRENT DATE have new Filter Factor calculations in recent DB2. I presume QMF Cost estimates will get somewhat better.

The original question was about asutime, so I presume he was thinking of the Resource Limit facility, QMF Governor, etc. Predictive governing based of query cost estimates is definitely a minefield. I don't know if anyone likes it or uses it. Perhaps usable for simple queries on very well behaved data.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd