We are running DB2 9.7 FP5 on Windows Datacenter 2008.
What is the best way to sort the queries to find the ones that are causing the biggest performance hit on the system?
So far I have exported the top 100 queries by TOTAL_EXEC_TIME, then sorted by NUM_EXECUTION, kept the top 50. To me this ensures that the query is frequently used AND has a big impact, time wise. I then sorted by Avg Rows Read by execution (ROWS_READ / NUM_EXECUTION).
As it happened after this exercise I ended up with a query on top that has the shortest TOTAL_EXEC_TIME of all the 50 queries I have left. My question now is should I add other criteria to my sort/weighting? Such as TOTAL_EXEC_TIME, what about STMT_SORTS or TOTAL_USR_CPU_TIME?
How are you ranking/grading queries using SNAPDYN_SQL?