Ray,
Before anyone can answer whether 373 seconds per execution is a reasonable
number, we would need to know more about the environment. Is it a
warehouse or an OLTP system? How large are the tables and what is the sql
going after?
However, your gut reaction is correct. It would seem to me something is
wrong. Any statement that executes that often and takes the long to
execute needs further examination. i.e. Is there a way to make it faster
per execution and does it need to execute that often?
What strikes me in this snapshot is that the # rows read is not very high,
why is the execution time so long? Why is the system cpu time so high
compared to user cpu time? (what os and system parameters?)
Of course this assumes the snapshot data is valid and you are not hitting a
"bug."
In my mind, this is a "low hanging fruit" that you can tackle and increase
performance dramatically for your system by "fixing" if at all possible.
Regards,
Cindi
Internet: [Email address protected]
"RLam"
<db2udbdba-ezmlms
hield-x33546374.x To
[Email Address Removed] "LazyDBA Discussion"
a.com> <[Email address protected]
cc
03/22/2007 08:01
PM Subject
suggestion for monitoring
performance issue?
I've recently added a cron job to collect "dynamic sql snapshot" and got
very excited at some huge numbers until I figure out I should divide
Total execution time (sec.ms) / Number of executions
In the example below, I get 373 sec.ms (1608686319 / 4307732)
Is 373 sec.ms a reasonable number? Is the number of executions too high?
What numbers are considered good / bad? Any other snapshots I should
collect?
Thank you in advance for your suggestion (tomorrow is Friday :)
Number of executions = 4307732
Number of compilations = 2
Worst preparation time (ms) = 59
Best preparation time (ms) = 1
Internal rows deleted = 0
Internal rows inserted = 0
Rows read = 4209772
Internal rows updated = 0
Rows written = 0
Statement sorts = 0
Statement sort overflows = 0
Total sort time = 0
Buffer pool data logical reads = 4230668
Buffer pool data physical reads = 454478
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool index logical reads = 12995643
Buffer pool index physical reads = 76966
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total execution time (sec.ms) = 1608686319.718094
Total user cpu time (sec.ms) = 494.320000
Total system cpu time (sec.ms) = 4513.890000
Cheers
Ray
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page