IBM DB2 11 for z/OS Buffer Pool Monitoring and Tuning

Readers' comments

Readers' comments (24) 

lockedThis discussion is now locked


Posted by Don Bryan on 21 March 2014 at 9:34

The sentence "Keep in mind that a range scan in this context is a range scan based on the cluster key is an
SQL statement that does not use the index to filter the rows." is confusing. Is the range scan based on the cluster key or a SQL statement? or both? The "is a" and "is an" creates the confusion.

Posted by Hari Shanmugadhasan on 12 April 2014 at 13:06

Page 15 of the March 23 draft says, "For example, if a query needs to read every 10th page, all of the pages are page-sequential, and Data Manager prefetches every
page." But with progressive prefetch, isn't the first prefetch only 8 pages (32/4), so does that mean two prefetches of 8 pages are done in parallel to get the x+10th page and x+20 page?

Posted by Hari Shanmugadhasan on 12 April 2014 at 13:23

Page 15 of the March 23 draft says,"triggering page reads pages N+P through N+2P-1" and "P=32" and "DB2 triggers prefetch for 16 pages, numbered 23-38." So the formula shouldn't use P=32 but reflect progressive prefetch amounts according to the example scenario since 15+8 = 23 and (15+ 8)+16 - 1 = 38. Only once the prefetch amount reaches 32 is the formula correct as stated.

Posted by Hari Shanmugadhasan on 12 April 2014 at 13:27

Page 15 of the March 23 draft says,"if the skip distance is greater than P/2 pages, the pages are read via synchronous I/O." This implies that a skip distance of 16 4K pages is fine, yet the introduction of progressive prefetch suggests that dynamic prefetch will fail initially and that the needed pages will all be obtained via synch I/O.

Posted by Hari Shanmugadhasan on 12 April 2014 at 13:44

Page 15 says "Sequential detection works well for skip sequential access to the data when the cluster ratio
of the index is high and there is a screening predicate."
Yet page 15 says "making list prefetch vastly superior to dynamic prefetch" in the context of every 10th page which sounds like the situation that index screening predicates might produce. Does LPO with screening predicates resulting in 50% or less of pages needed mean List Prefetch is better even if CR=100? Or if 25%? Or if 15%?

Posted by Hari Shanmugadhasan on 12 April 2014 at 13:57

In my previous scenario, I am assuming that the page 17-18 sorted RID list issues vs. dynamic prefetch are relatively unimportant because the RID list is relatively small and so fits in a single RID block and I want all the rows that qualify.

Posted by Hari Shanmugadhasan on 12 April 2014 at 14:25

Page 16 says, "Nor does this access path prefetch the pages if a screening predicate is such that the data pages to be read are sparse." Shouldn't "sparse" be defined? Or qualified by "too sparse" since 1 row every 16 4K pages seems sparse, yet it's fine for dynamic prefetch, at least once it has gotten started and past the progressive prefetch stage? And isn't the real factor the combined filtering of ALL the index screening predicates? Not just "a screening predicate," which implies one?

Posted by Hari Shanmugadhasan on 12 April 2014 at 14:59

I made a page reference error 3 comments previously.
I should have written
"Yet page 16 says "making list prefetch vastly superior to dynamic prefetch" . . . "
instead of referring to page 15.

Posted by Hari Shanmugadhasan on 12 April 2014 at 16:07

Page 16 says "the page-sequential count for a range scan
never drops below 7 once it reaches 8." Can it also be said for this scenario that the count "never drops below 5 once it reaches 6"? And thus dynamic prefetch is continued? As long as there are not two consecutive unclustered rows on two different pages? With indirect row access of the overflow record having no impact on the count?

Posted by Hari Shanmugadhasan on 12 April 2014 at 16:23

Page 15, says "if the skip distance is greater than P/2 pages, the pages are read via synchronous I/O." The first is not page-sequential, but if prefetched as part of 32 4K pages then the pages 17-32 are in the buffer pool and will not need synch i/o.

Posted by Hari Shanmugadhasan on 12 April 2014 at 16:37

The p15 quote in my previous comment is probably referring to sufficiently frequent not page-sequential getpages so that dynamic prefetch never starts or breaks down. On pages 15 and 16 it might be simpler to replace "unclustered" and "sparse" with "not page-sequential" or some other consistent terminology.

And it might be worth noting that as long as the not page-sequential page is in the BP, whether prefetched previously or not, no synch i/o is done, but the counter is still decremented.

Posted by Hari Shanmugadhasan on 12 April 2014 at 16:53

Page 17 says: "If a query requires that the result set be ordered, a sorted RID list scan requires that the result set be sorted back into index key order." But this is only true if the query requires the answer in "index key order."

A query that does ORDER BY SALARY when there is no index on SALARY has to do the sort for all access paths. Further, even if there was index on SALARY the alternative access paths might not use it so still a sort. DEPTNO = :hv might use the DEPTNO index.

Posted by Hari Shanmugadhasan on 22 April 2014 at 14:41

The April 19 draft shifted page 15 to page 17 and added:

"To illustrate, let's suppose that the progressive prefetch quantity has reached its maximum value of P."

So the use of P in that paragraph looks OK. So it addresses my 2nd post.

Posted by Hari Shanmugadhasan on 22 April 2014 at 15:11

Page 17 says:

"the last getpage to trigger prefetch was pages [sic] N-P: the next page to trigger a prefetch I/O is page N. That page would have triggered a prefetch I/O for pages N through N+P-1."

This doesn't seem quite right. If page N-P was the last in the window then it drives a prefetch of N-P+1 to N. So N is NOT page-sequential. IF N was last in the window then prefetch of pages N+1 to N+P? Not:

"The getpage for page N will then trigger a prefetch for pages N+P through N+2P-1."

Posted by Hari Shanmugadhasan on 22 April 2014 at 15:13

Page 17 says:

"The first prefetch I/O will read 8 pages (say 1 through 8), but the first getpage within the range of pages 4 through 7 will trigger a prefetch for 16 pages (9 through 24)."

Isn't the window in this case pages 5 through 8? And NOT:

"pages 4 through 7"

Posted by Hari Shanmugadhasan on 22 April 2014 at 15:27

Page 17 says:

"The above paragraph is also true when the prefetch quantity has not reached its maximum value, except that having previously prefetched P pages, the next prefetch I/O will read 2P pages. Therefore, the getpage for page N will read pages N+P through N+3P-1."

Isn't the use of P in this context confusing since the initial prefetch is actually P/4? So:

"prefetch for 16 pages (9 through 24)"

N + P/4 to N+3*(P/4) -1 where N=1 and P=32. A variable name other than P is better.

Posted by Hari Shanmugadhasan on 22 April 2014 at 15:41

Page 18 of the April 19 draft has added:

"(that is not page-sequential.)"

Which largely addresses the "sparse" part of my 6th comment. But how does it get past progressive prefetch stage? And my concern that it is the combination of ALL the screening predicates that matters, remains unaddressed.

Posted by Hari Shanmugadhasan on 22 April 2014 at 15:45

Page 20 of the April 19th draft has added:

"by the index key"

This addresses my 11th comment.

Posted by Hari Shanmugadhasan on 22 April 2014 at 15:55

The April 19 draft has moved what were pages 15-16 in my April 12 comments to pages 17-18. It has not addressed my comments 1, 3, 4, 5, 7, 8 , 9 or 10.

Posted by Hari Shanmugadhasan on 22 April 2014 at 16:11

Page 8 says:

"Usually the getpages that are associated with DB2 prefetch are classified as sequential, but this is not always the case. It is true for disorganized indexes, RID list scans and prefetch of LOBs. It is true for sequential prefetch and dynamic prefetch as well."

Parts of this are contradicted for V11 by SG24-8180 and SG24-8182. See some of my comments on the SG24-8222 draft for details. It sounds like V9 and V10, see page 12 of 2012 SHARE Session 11297 by Jeffrey Berger.

Posted by Hari Shanmugadhasan on 22 April 2014 at 16:21

Page 9 has a page residency formula:

"VPSIZE * (1-VPSEQT/100) / random synchronous IO/sec"

But it doesn't seem to factor in the flushing of random pages by "list prefetch when it is used for Incremental Copy, Fast Log Apply, or Runstats table sampling" (page 8) or by sequential insert READ(NO) activity (page 8 warns about hit ratio impact).

The first page 9 residency formula doesn't appear to take into account the reduced impact of MRU prefetch.

Posted by Hari Shanmugadhasan on 23 April 2014 at 13:16

My middle paragraph in my comment on Page 17 dated "22 April 2014 at 13:11" is incorrect. I should have written:

"This doesn't seem quite right. If page N-P was the first in RUN 1 then RUN 1 was N-P to N-1. But N is NOT page-sequential so its getpage (assuming it's next) does NOT trigger a prefetch request, even though it leads RUN 2 (N to N+P-1) prefetched due to the N-P getpage. Not:."

Posted by Hari Shanmugadhasan on 23 April 2014 at 13:19

Page 17 says:

"the next page to trigger a prefetch I/O is page N. That page would have triggered a prefetch I/O"

The use of "prefetch I/O" seems incorrect in both places since it is a prefetch REQUEST that is triggered. If the pages are in the BP then there is no I/O triggered.

Posted by Ms. Heloisa Soares on 24 March 2017 at 12:40

On page 34: "QISTWFP3/4= #times DGTT had to be allocated from 0 secqty 4K/32K work file table
space
QISTWFP5/6= #times non DGTT had to be allocated from >0 secqty 4k/32K work file
table space".
I could not find any of those fields. SMF record type 100 has QISTWFP1 and QISTWFP2 only. I checked IBMLink and the only results for QISTWFP3, QISTWFP4, QISTWFP5 and QISTWFP6 were pointing to redbooks. I also searched SDSNMACS and MXG. They do not seem to exist.


Profile

Publish Date
10 July 2014

Last Update
31 July 2014


Rating:
(based on 3 reviews)


Author(s)

ISBN-10
0738453749

IBM Form Number
REDP-5092-00