High Getpage Count DB2 z/OS V7

Tami Van Dreese

High Getpage Count DB2 z/OS V7
I'm trying to figure out why an sql statement is showing a much higher
getpage count than there are pages of data. The sql is pretty simple - one
table, no order by, no summing or other functions. The table has about 10-
11 million data pages in it and another 4.3 million pages for the
clustering index. When the sql runs, it shows a getpage count of over 13
million for the data and 4.6 million for the index. Access is using the
clustering index and 16 degrees of parallelism. Does the high getpage
count indicate that the pages are being reread? Or could this be a factor
of merging all the parallel threads?

The data and index are in separate pools and there wasn't much other
activity at the time. The data bufferpool is sized at 170,000 with vpseqt
at 90% and vppseqt at 100%, uses dataspaces and hit the Immediate Write
threshold twice. The bufferpool for DSNDB07 showed no activity.
Asynchronous page reads are about 11 million, 366,000 sequential prefetch
requests, 407,000 synchronous read I/Os.

Thanks for any insight you can give.
Tami Van Dreese

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Bell

Re: High Getpage Count DB2 z/OS V7
(in response to Tami Van Dreese)
DB2 counts getpages every time it hits a new page. For example
read row 1 from page 3 - 1 GP
read row 2 from page 3 same page so doesn't count
read row 3 from page 4 - 1 more GP
read row 4 from page 3 - 1 more GP
read row 5 from page 3 same page so doesn't count
read row 6 from page 4 - 1 more GP
read row 7 from page 4 same page so doesn't count

The only conclusion you can make is that your table and index aren't 100%
clustered. As long as you don't have to issue sync reads to find the page,
it shouldn't hurt your run time.

Mike
HLS Technologies

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Tami Van Dreese
Sent: Wednesday, January 18, 2006 2:03 PM
To: [login to unmask email]
Subject: [DB2-L] High Getpage Count DB2 z/OS V7

I'm trying to figure out why an sql statement is showing a much higher
getpage count than there are pages of data. The sql is pretty simple - one
table, no order by, no summing or other functions. The table has about 10-
11 million data pages in it and another 4.3 million pages for the
clustering index. When the sql runs, it shows a getpage count of over 13
million for the data and 4.6 million for the index. Access is using the
clustering index and 16 degrees of parallelism. Does the high getpage
count indicate that the pages are being reread? Or could this be a factor
of merging all the parallel threads?

The data and index are in separate pools and there wasn't much other
activity at the time. The data bufferpool is sized at 170,000 with vpseqt
at 90% and vppseqt at 100%, uses dataspaces and hit the Immediate Write
threshold twice. The bufferpool for DSNDB07 showed no activity.
Asynchronous page reads are about 11 million, 366,000 sequential prefetch
requests, 407,000 synchronous read I/Os.

Thanks for any insight you can give.
Tami Van Dreese

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Tami Van Dreese

Re: High Getpage Count DB2 z/OS V7
(in response to Mike Bell)
The cluster ratio is 99% so I'm still puzzled how that would translate into a getpage count 20-30% higher than my page count.

Tami Van Dreese

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Mike Bell
Sent: Wednesday, January 18, 2006 4:04 PM
To: [login to unmask email]
Subject: Re: [DB2-L] High Getpage Count DB2 z/OS V7


DB2 counts getpages every time it hits a new page. For example
read row 1 from page 3 - 1 GP
read row 2 from page 3 same page so doesn't count
read row 3 from page 4 - 1 more GP
read row 4 from page 3 - 1 more GP
read row 5 from page 3 same page so doesn't count
read row 6 from page 4 - 1 more GP
read row 7 from page 4 same page so doesn't count

The only conclusion you can make is that your table and index aren't 100%
clustered. As long as you don't have to issue sync reads to find the page,
it shouldn't hurt your run time.

Mike
HLS Technologies

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Tami Van Dreese
Sent: Wednesday, January 18, 2006 2:03 PM
To: [login to unmask email]
Subject: [DB2-L] High Getpage Count DB2 z/OS V7

I'm trying to figure out why an sql statement is showing a much higher
getpage count than there are pages of data. The sql is pretty simple - one
table, no order by, no summing or other functions. The table has about 10-
11 million data pages in it and another 4.3 million pages for the
clustering index. When the sql runs, it shows a getpage count of over 13
million for the data and 4.6 million for the index. Access is using the
clustering index and 16 degrees of parallelism. Does the high getpage
count indicate that the pages are being reread? Or could this be a factor
of merging all the parallel threads?

The data and index are in separate pools and there wasn't much other
activity at the time. The data bufferpool is sized at 170,000 with vpseqt
at 90% and vppseqt at 100%, uses dataspaces and hit the Immediate Write
threshold twice. The bufferpool for DSNDB07 showed no activity.
Asynchronous page reads are about 11 million, 366,000 sequential prefetch
requests, 407,000 synchronous read I/Os.

Thanks for any insight you can give.
Tami Van Dreese

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Steen Rasmussen

Re: High Getpage Count DB2 z/OS V7
(in response to Tami Van Dreese)
Hello Tami,



Even though you have a high Clusterratio - when the sequential prefetch
kicks in and you suddenly need a page which is not prefetched - DB2 will
have to leave the prefetched pages and come back.

So leaving the "optimal scenario" of hitting prefetched pages might cost
you some additional getp since you later on need to RE-read the same
page.

So you might read the same page a number of times. Do you have VARCHAR
columns which have been updated so rows are relocated ?

After a fresh REORG you should get your preferred result.





Kind regards

Steen Rasmussen

CA

Senior Consultant





-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Van Dreese, Tami S
Sent: Wednesday, January 18, 2006 4:25 PM
To: [login to unmask email]
Subject: Re: [DB2-L] High Getpage Count DB2 z/OS V7



The cluster ratio is 99% so I'm still puzzled how that would translate
into a getpage count 20-30% higher than my page count.



Tami Van Dreese


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm