Login (https://www.idug.org/l/li/in/) Request Login (l/pw/rs/)
(http://www.idug.org/emea2018)
(https://www.idug.org/)
ABOUT IDUG (HTTPS://WWW.IDUG.ORG/P/FO/ET/THREAD=25835#) MEMBERSHIP (HTTPS://WWW.IDUG.ORG/P/FO/ET/THREAD=25835#) FORUMS (HTTPS://WWW.IDUG.ORG/P/FO/ET/THREAD=25835#)
EVENTS (HTTPS://WWW.IDUG.ORG/P/FO/ET/THREAD=25835#)
RESOURCES (HTTPS://WWW.IDUG.ORG/P/FO/ET/THREAD=25835#)
ARTICLES & CONTENT (HTTPS://WWW.IDUG.ORG/P/FO/ET/THREAD=25835#) GET INVOLVED (HTTPS://WWW.IDUG.ORG/P/FO/ET/THREAD=25835#)
What can cause lot of Sync I/O in spite of buffer utilization shows over 80% (p/fo/st/thread=25835)
Ray (p/fo/et/topic=24) (p/fo/et/)
What can cause lot of Sync I/O in spite of buffer utilization shows over 80% December 19, 2007 01:01 PM
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 (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 (http://www.idug.org/lsms)
Re: What can cause lot of Sync I/O in spite of buffer utilization shows over 80%
Joel Goldstein
December 19, 2007 01:36 PM (in response to Ray (https://www.idug.org/p/fo/et/thread=25835#p98133))
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 (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 (http://www.idug.org/lsms)
Re: What can cause lot of Sync I/O in spite of buffer utilization shows over 80%
Mike Bell
December 19, 2007 02:08 PM (in response to Joel Goldstein (https://www.idug.org/p/fo/et/thread=25835#p98136))
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 (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 (http://www.idug.org/lsms)
Re: What can cause lot of Sync I/O in spite of buffer utilization shows over 80%
Ray
December 19, 2007 09:18 PM (in response to Mike Bell (https://www.idug.org/p/fo/et/thread=25835#p98139))
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 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 (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 (http://www.idug.org/lsms)
Re: What can cause lot of Sync I/O in spite of buffer utilization shows over 80%
Ray
December 20, 2007 02:48 AM (in response to Ray (https://www.idug.org/p/fo/et/thread=25835#p98153))
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 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 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 (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 (http://www.idug.org/lsms)
International Db2 Users Group 2601 Iron Gate Drive, #101 Wilmington, NC 28412 (https://maps.google.com/? q=2601+Iron+Gate+Drive,+%23101+Wilmington,+NC+28412&entry=gmail&source=g) Phone: (910) 660-8649 (tel:+19106608649) / Fax: (910) 523-5504 (tel:+19105235504) Copyright © 2018 IDUG. All Rights Reserved. All material , files, logos, and trademarks within this site are properties of their respective organizations.
(https://www.facebook.com/IDUGDB2) (https://twitter.com/IDUGDB2) (https://www.linkedin.com/grp/home?gid=46747) (https://www.youtube.com/user/IDUGDB2) Terms of Service (p/cm/ld/fid=2 ) | Privacy Policy (p/cm/ld/fid=3) | Contact (mailto:
[email protected]? Subject=IDUG )