When given a query cost in QMF like 150 or 325.......how do you calculate ASUTIME from that?
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.
DB2 Application Performance Specialist
CPT Global Ltd