Hi,
you buffer pool hit rate is quite low, your SQL is doing a lot of
physical I/O's. High system CPU time maybe attributed to the I/O wait.
Try to increase bufferpools or tune I/O subsystem. Run vmstat to check
I/O wait.
Regards,
Z
-----Original Message-----
From: Cynthia Morrison
[mailto:db2udbdba-ezmlmshield-x9983595.[Email address protected]
Sent: Thursday, March 22, 2007 10:02 PM
To: LazyDBA Discussion
Subject: Re: suggestion for monitoring performance issue?
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
---------------------------------------------------------------------
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
-----------------------------------------
CONFIDENTIALITY NOTICE
----------------------
This transmission is intended for the sole use of the individual
and/or entity to whom it is addressed, and may contain information
and/or attachments that are privileged, confidential and exempt
from disclosure under applicable law. If the reader of this
transmission is not the intended recipient, you are hereby notified
that any disclosure, dissemination, distribution, duplication or
the taking of any action in reliance on the contents of this
transmission by someone other than the intended addressee or its
designated agent is strictly prohibited. If your receipt of this
transmission is in error, please notify the sender by replying
immediately to this transmission and destroying the transmission.
For your protection, do not include Social Security numbers,
passwords or other non-public and personal information in your
email. Thank you
DB2 & UDB email list listserv db2-l LazyDBA home page