Perfromance Statistics 1

[balachandar]

Hi All,
I would like to compare the performance Statistics of two queries.(logical reads,physical reads,read ahead reads). How do you get such statistics for a query in DB2. What is the easier way for accomplishing this?.

Thanks and Regards
Balachandar

 

[gregsimpson]

DB2 has a cost based optimizer. If you wish to know how a query will be executed by DB2, you must analyze its access plan. You can find out all sorts of information with regards as to how DB2 will satisfy your query using either db2expln or dynexpln. Details you will be returned will be the access path choosen, type of join, sorts, scans, whether or not it will use list prefetch etc. Obviously what is returned here is only a guide as to what DB2 thinks will happen when it satisfies your query, not necessarily exactly the same as that which physically happens.

You probably need to read up on such things as "Explain", db2evmon, The Event analyzer and the performance monitor. As with most of the DB2 tools today, you can either run them from the command prompt or use the graphic interface provided by Control Center type tools.

A logical place to start I would say is by "explaining" your statements first. Example syntax below.

db2expln -c greg - d gregsdb -p sqlprog1 -s0 -t -g

If I remember rightly parameters are as follows

-c Creator of the database

-d Database Alias

-p Package to be explained

-s This parameter represents which Section in the package you wish to explain. Using 0 means, explain all sections.

-t Terminal output

-g include optimizer plan graphs

If you've got some budget to spare there are fantastic tools by people such as dbguys, which will give you a whole host of real time physical information as to how a query performed/is performing.

DBGUYS can be found at

http://www.breakthroughdb2.com

and do in fact offer a free download of their product to enable you to see whether or not it is right for your organisation.