What can cause lot of Sync I/O in spite of buffer utilization shows over 80%

Ray

What can cause lot of Sync I/O in spite of buffer utilization shows over 80%
Dear Listers,

One of our programs is running for quite sometime and yes it is due to sync I/O which is "coded in" and a design issue and the programmer will break the join between the two tables that he had coded. However, what I seek clarification is the following:

Buffer usage for the program statement shows over 80 % utlization and yet it has a high synch I/O? How is that possible? The statement has processed just about 100 SQL calls and has issued over 3 million getpages with an avg get page less than optimal. It has been running for over 2 hours instead of the average completion time of 10 minutes. I also notice that it has invoked List Prefetch. I checked on the Buffer pools and we have enough space there!

Any pointers?



Cheers, Ray

---------------------------------
Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Joel Goldstein

Re: What can cause lot of Sync I/O in spite of buffer utilization shows over 80%
(in response to Ray)
The first question to address your question - what do you mean by 80% utilization?
If you are looking at something like "buffers in use" or "buffers allocated", or similar from online monitors - you have
a common mis-understanding of the field. Not your fault, it just has a**-backwards terminology.

Those are unavailable buffers - either updated and have not been written out, and/or allocated to a read process that has not completed.

Now - you say coded in synch IO? might be semantics, but there is no such thing. Perhaps you mean random access...??

However, seeing List Prefetch, means this is the access type chosen by the optimizer..... which obviates random access.
If all access was random, you could see dynamic prefetch, but not list prefetch.

If you can provide some detailed information from whatever monitor you are using, I'm sure there are many people here that will try to help you.

Very high synch IO counts come from - either very large objects and very random access, and/or improper pool sizing and tuning.

Regards,
Joel



Joel Goldstein
Responsive Systems
Buffer Pool Tool for DB2, the worldwide industry standard
Performance software that works......
Predicts Group Buffer Pool performance too!
www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416
----- Original Message -----
From: Ray
Newsgroups: bit.listserv.db2-l
To: [login to unmask email]
Sent: Wednesday, December 19, 2007 2:01 PM
Subject: [DB2-L] What can cause lot of Sync I/O in spite of buffer utilization shows over 80%


Dear Listers,

One of our programs is running for quite sometime and yes it is due to sync I/O which is "coded in" and a design issue and the programmer will break the join between the two tables that he had coded. However, what I seek clarification is the following:

Buffer usage for the program statement shows over 80 % utlization and yet it has a high synch I/O? How is that possible? The statement has processed just about 100 SQL calls and has issued over 3 million getpages with an avg get page less than optimal. It has been running for over 2 hours instead of the average completion time of 10 minutes. I also notice that it has invoked List Prefetch. I checked on the Buffer pools and we have enough space there!

Any pointers?



Cheers, Ray


------------------------------------------------------------------------------
Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Mike Bell

Re: What can cause lot of Sync I/O in spite of buffer utilization shows over 80%
(in response to Joel Goldstein)




Items to check

1. Did the access path change due to bind or rebind?

2. Does it fetch all the rows for the list prefetch or does it open
the cursor fetch a few rows and then close the cursor?

3. Are the buffer pools separate for the index and data?

4. What is the average IO time? Did the dataset move? Is there another
job running at the same time that is not letting you get as much disk cache
as usual? I assume this is one of the smart disk devices that says yes I am
a 3390 but isn't.

5. Was there a table change?

6. How many SQL statements does it usually process? Are you better off
cancelling the job and fixing the access path?



It is very easy to get 80% buffer utilization if you are repeating a build
of list prefetch and then throwing it away and doing it again. Especially
with one of those repositioning SQL's that qualifies half the table on an
average.



Post the SQL statement - especially the where clause and the stats from
current position.

3 million get pages for 100 SQL statements means to me that DB2 is
discarding a lot of work.



Mike

HLS Technologies








The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Ray

Re: What can cause lot of Sync I/O in spite of buffer utilization shows over 80%
(in response to Mike Bell)
Joel/Mike,

Thanks for your response. Let me answer the questions that you guys have asked. I have attached the details in a text document.

I use mainly OMEGAMON and APPTUNE and 'SQLs wriiten to query Catalog tables' to monitor jobs. I have attached a report as a text document that has all the Wait time details.

I referred to " % of Getpages read from Buffer" as Buffer utilization

What I really meant by "coding in synchronous I/O" was : The SQL that was built creates a lot of I/O ..random I/O as you mentioned. The Statement is a DECLARE CURSOR
and most of the work gets done in OPEN cycle as there is a ORDER BY clause and for reasons unknown to me yet has an OPTIMIZE for 1 ROW!

Yes, the explain shows List prefetch. Time spent in CPU or Lock Time or Uncategorized time or Other Wait times is very minimal. (Please refer to the attachment).

The statement has NOT been REBOUND recently and there is no change in ACESS path
and no table changes either. I have asked them to cancel the job and restart it as single thread. No luck with synch I/O either.

The Buffer pools are seperate for Index and data

The job is partitioned and yes there are other partitioned jobs executing at the same time
(This is where I think we introduce I/O as each partition tries to grab the data page and in doing so introduces random I/O. But I got thrown off by what APPTUNE reports % of datapage from buffer)

Cheers
Joel Goldstein - Responsive Systems <[login to unmask email]> wrote:
The first question to address your question - what do you mean by 80% utilization?
If you are looking at something like "buffers in use" or "buffers allocated", or similar from online monitors - you have
a common mis-understanding of the field. Not your fault, it just has a**-backwards terminology.

Those are unavailable buffers - either updated and have not been written out, and/or allocated to a read process that has not completed.

Now - you say coded in synch IO? might be semantics, but there is no such thing. Perhaps you mean random access...??

However, seeing List Prefetch, means this is the access type chosen by the optimizer..... which obviates random access.
If all access was random, you could see dynamic prefetch, but not list prefetch.

If you can provide some detailed information from whatever monitor you are using, I'm sure there are many people here that will try to help you.

Very high synch IO counts come from - either very large objects and very random access, and/or improper pool sizing and tuning.

Regards,
Joel



Joel Goldstein
Responsive Systems
Buffer Pool Tool for DB2, the worldwide industry standard
Performance software that works......
Predicts Group Buffer Pool performance too!
www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416
----- Original Message -----
From: Ray
Newsgroups: bit.listserv.db2-l
To: [login to unmask email]
Sent: Wednesday, December 19, 2007 2:01 PM
Subject: [DB2-L] What can cause lot of Sync I/O in spite of buffer utilization shows over 80%


Dear Listers,

One of our programs is running for quite sometime and yes it is due to sync I/O which is "coded in" and a design issue and the programmer will break the join between the two tables that he had coded. However, what I seek clarification is the following:

Buffer usage for the program statement shows over 80 % utlization and yet it has a high synch I/O? How is that possible? The statement has processed just about 100 SQL calls and has issued over 3 million getpages with an avg get page less than optimal. It has been running for over 2 hours instead of the average completion time of 10 minutes. I also notice that it has invoked List Prefetch. I checked on the Buffer pools and we have enough space there!

Any pointers?



Cheers, Ray
---------------------------------
Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services


Cheers, Ray

---------------------------------
Never miss a thing. Make Yahoo your homepage.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Ray

Re: What can cause lot of Sync I/O in spite of buffer utilization shows over 80%
(in response to Ray)
Mike,

As far as I understand, OPTIMIZE for 1 ROW loses its ability to remove List Prefetch
if one has an ORDER BY clause in the SQL.

Thanks for taking a detailed look tomorrow. I am also really surprised at what's going on and hence the post.

Cheers,
Ray


Mike Bell <[login to unmask email]> wrote:
v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);} w\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);} Real basic question – how can you have 316 opens and no fetches (0)
That almost sounds like you are matching to a input file and never moving from the first record?
The other basic question is how is DB2 getting to list prefetch with an OPTIMIZE for 1 row. Should never happen.

I will look at it some more tomorrow.

Mike

From: Ray [mailto:[login to unmask email]
Sent: Wednesday, December 19, 2007 9:18 PM
To: [login to unmask email]
Cc: Joel Goldstein - Responsive Systems; [login to unmask email]
Subject: Re: [DB2-L] What can cause lot of Sync I/O in spite of buffer utilization shows over 80%


Joel/Mike,



Thanks for your response. Let me answer the questions that you guys have asked. I have attached the details in a text document.



I use mainly OMEGAMON and APPTUNE and 'SQLs wriiten to query Catalog tables' to monitor jobs. I have attached a report as a text document that has all the Wait time details.



I referred to " % of Getpages read from Buffer" as Buffer utilization



What I really meant by "coding in synchronous I/O" was : The SQL that was built creates a lot of I/O ..random I/O as you mentioned. The Statement is a DECLARE CURSOR

and most of the work gets done in OPEN cycle as there is a ORDER BY clause and for reasons unknown to me yet has an OPTIMIZE for 1 ROW!



Yes, the explain shows List prefetch. Time spent in CPU or Lock Time or Uncategorized time or Other Wait times is very minimal. (Please refer to the attachment).



The statement has NOT been REBOUND recently and there is no change in ACESS path

and no table changes either. I have asked them to cancel the job and restart it as single thread. No luck with synch I/O either.



The Buffer pools are seperate for Index and data



The job is partitioned and yes there are other partitioned jobs executing at the same time

(This is where I think we introduce I/O as each partition tries to grab the data page and in doing so introduces random I/O. But I got thrown off by what APPTUNE reports % of datapage from buffer)



Cheers
Joel Goldstein - Responsive Systems <[login to unmask email]> wrote:

The first question to address your question - what do you mean by 80% utilization?

If you are looking at something like "buffers in use" or "buffers allocated", or similar from online monitors - you have

a common mis-understanding of the field. Not your fault, it just has a**-backwards terminology.



Those are unavailable buffers - either updated and have not been written out, and/or allocated to a read process that has not completed.



Now - you say coded in synch IO? might be semantics, but there is no such thing. Perhaps you mean random access...??



However, seeing List Prefetch, means this is the access type chosen by the optimizer..... which obviates random access.

If all access was random, you could see dynamic prefetch, but not list prefetch.



If you can provide some detailed information from whatever monitor you are using, I'm sure there are many people here that will try to help you.



Very high synch IO counts come from - either very large objects and very random access, and/or improper pool sizing and tuning.



Regards,

Joel







Joel Goldstein
Responsive Systems
Buffer Pool Tool for DB2, the worldwide industry standard
Performance software that works......
Predicts Group Buffer Pool performance too!
www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416

----- Original Message -----

From: Ray

Newsgroups: bit.listserv.db2-l

To: [login to unmask email]

Sent: Wednesday, December 19, 2007 2:01 PM

Subject: [DB2-L] What can cause lot of Sync I/O in spite of buffer utilization shows over 80%



Dear Listers,



One of our programs is running for quite sometime and yes it is due to sync I/O which is "coded in" and a design issue and the programmer will break the join between the two tables that he had coded. However, what I seek clarification is the following:



Buffer usage for the program statement shows over 80 % utlization and yet it has a high synch I/O? How is that possible? The statement has processed just about 100 SQL calls and has issued over 3 million getpages with an avg get page less than optimal. It has been running for over 2 hours instead of the average completion time of 10 minutes. I also notice that it has invoked List Prefetch. I checked on the Buffer pools and we have enough space there!



Any pointers?





Cheers, Ray

---------------------------------

Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services



Cheers, Ray


---------------------------------

Never miss a thing. Make Yahoo your homepage.




Cheers, Ray

---------------------------------
Never miss a thing. Make Yahoo your homepage.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms