Low pefetch hit ration

kumaran rajendran

Low pefetch hit ration

Hi 

Low prefetchers ration and More I/O time on IBM Data Server Manager report 

This means more of synchronous read, in database. 

What causes synchronous read, id happens when data is not sequential read 

 

regds

paul

Jorg Lueke

RE: Low pefetch hit ration
(in response to kumaran rajendran)

There's a lot of possibilities. Basically DB2 doesn't think it's efficient to read the data in sequence so there's no prefetch. This can be because the BP is too small, it can be that the data is disorganized, it can be because of a Join or the way a predicate is coded. It can be because of bad runstats or an old access path created with different data than exists now.

Are the running transaction SQL (transactions or batch processing using records) or is this a single SQL?

Charles Brown

Low pefetch hit ration
(in response to Jorg Lueke)
Re. "What causes synchronous read.."
Low hit ratio
Buffer not adequately sized

Sent from my iPad

> On May 15, 2017, at 5:14 PM, Jorg Lueke <[login to unmask email]> wrote:
>
> There's a lot of possibilities. Basically DB2 doesn't think it's efficient to read the data in sequence so there's no prefetch. This can be because the BP is too small, it can be that the data is disorganized, it can be because of a Join or the way a predicate is coded. It can be because of bad runstats or an old access path created with different data than exists now.
>
> Are the running transaction SQL (transactions or batch processing using records) or is this a single SQL?
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Learn how ESAi's fast data refresh & Test Data Management products can save up to 90% in CPU, I/O
> and manual efforts compared to typical solutions. Be a hero to your users with BCV5 & XDM. See
> ttp://www.ESAIGroup.com/idug
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

Venkat Srinivasan

RE: Low pefetch hit ration
(in response to kumaran rajendran)

Is this z/OS or LUW? Inefficient access path, poor relative clustering, disorganized data, inadequate bp size / settings improper object placement in pool, re-reading changed data that is pending to be written are some cases. Rarely if the pageset is showing strict random access to get one page everytime, there is really not much you could do. All other cases must be tunable.

In Reply to kumaran rajendran:

Hi 

Low prefetchers ration and More I/O time on IBM Data Server Manager report 

This means more of synchronous read, in database. 

What causes synchronous read, id happens when data is not sequential read 

 

regds

paul

kumaran rajendran

RE: Low pefetch hit ration
(in response to Jorg Lueke)

thanks for reply

Its batch process and have report which has top SQL in it. Yeah, all the points can cause sync read. So based on report like More I/O time and low prefetch ratio are indication about sync read. 

Attached DSM report, any help to confirm if there was really an sync read was happening which caused 

Attachments

  • POLICY01_RUN_05112017.pdf (476.7k)

kumaran rajendran

RE: Low pefetch hit ration
(in response to Venkat Srinivasan)

DB2 LUW V 10.5 fp 7 

Philip Gunning

Low pefetch hit ration
(in response to kumaran rajendran)
How many asynchronous pages fetched per prefetch request?



From: kumaran rajendran [mailto:[login to unmask email]
Sent: Tuesday, May 16, 2017 4:26 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Low pefetch hit ration



DB2 LUW V 10.5 fp 7



-----End Original Message-----