How to know ACTUAL performance metrics

[Celestun]

My understanding is that EXPLAIN will generate an access plan with an *estimate* of the expected resource usage (along with the sequence of steps to do it).

How do I see/lookup/find the actual resource usage for a query?

I have the same quiery with 3 separate nested selects (1704 rows, 422 rows and 8 rows) that have the same timeron estimate.

Give me a break. There HAS to be more resouce usage for the 1704 query. I understand that EXPLAIN is an estimate....I want actuals.

 

[gregsimpson]

Celestun,

there's a number of things which can effect your "timeron count". Things which spring to mind are how organised your data is in terms of satisfying the query. Whether you've run runstats and what level of detail you've collected in doing so. Sizes of tables and indexes.

It's quite possible given the small sizes of all three of your result sets that db2 would choose the same access path for all three queries, especially as the queries are essentially the same, so it's just your predicates which have changed?

The estimated timerons is only indicative of the work DB2 has to do and if that work is more or less the same for each of these queries, then I'm not surprised at your results. It's worth remembering that the data is retrieved in pages not rows, so therefore one page can hold many rows for the results set of your query, meaning the amount of difference in the work to get the result sets may well be negligible, if a returned page holds many of the rows required.

Cheers
Greg

 

[gregsimpson]

Celestun,

apologies, I didn't read your question correctly. You can get further real time information from db2batch, where you're able to run a query and collect snapshot statistics. There's something on it here

http://publib.boulder.ibm.com/infoc...topic=/com.ibm.db2.udb.doc/admin/c0005063.htm

However db2batch and "run query" in google, should get you what you're after.

Cheers
Greg