RE: suggestion for monitoring performance issue?

RE: suggestion for monitoring performance issue?

 

  

Look at the data reads

Buffer pool data logical reads = 4230668
> Buffer pool data physical reads = 454478

The bufferpool hit ratio for data (not indexes) is approx. 89 % which is
not really very good. Need to increase a bufferpool.

Regards,
Z

-----Original Message-----
From: Pierre Saint-Jacques
[mailto:db2udbdba-ezmlmshield-x59710111.[Email address protected]
Sent: Friday, March 23, 2007 2:02 PM
To: LazyDBA Discussion
Subject: Re: suggestion for monitoring performance issue?

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
>
>




---------------------------------------------------------------------
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