How is the hit ratio low ???
Physical Reads: data:454478 index:76966 Total: 531444
Logical Reads: data:4230668 index:12995643 Total:17226311
(1 -phy./Log.) * 100 ===> (1 - 531444/17226311)*100 ===> (1-0.0308)*100
===>96.91% ????
I would be interested to see what the tablespace structure of the tables is
and the io times from the snapshot to get the physical data in the pool.
Also, what size is the buffer pool. As Cynthia says, there are many factors
to look at that we have no info. on.
Regards, Pierre.
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
----- Original Message -----
From: "Baron Zinovy"
<db2udbdba-ezmlmshield-x43185147.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Friday, March 23, 2007 11:45 AM
Subject: RE: suggestion for monitoring performance issue?
> 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
>
>
> ---------------------------------------------------------------------
> 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