DB2 Dynamic Prefetch basics.

  This article aims at explaining in simple terms how dynamic prefetch or sequential detection works.  Before I go into sequential detection or dynamic prefetching we need to understand the reason behind it.

For any plan or package the access strategy is determined at bind time. Most of the times this access strategy is the best. How ever there are instances when DB2 determines that there is an even better way to access the data. DB2 comes to this conclusion after analyzing the way in which data is accessed during run time. Sequential Detection or Dynamic Prefetching is one such method DB2 uses to enhance the access strategy during run time.

The aim of sequential detection is to guess the page numbers the UR (Unit of Recovery) is going to ask for, and "pre-fetch" it (in the true sense of the word) and keep it ready in the buffer even before the UR actually asks for it. This "pre-fetching" takes place not because of a lack of index to access the data properly(as in case of a table space scan). Dynamic Prefetching takes place inspite of having an excellent indexed access path.

Sequential Detection and Dynamic Prefetching works for both tablespaces and  indexes.

To explain Sequential Detection I am going to take an example of a batch program that reads a flat file that contains specific phone numbers and updates the customer_phone_bill table by giving these customers a five dollar credit.  The customer_phone_bill table has an unique index on  phone_number.

 The program is going to look  some what  like this:

  do i = 1 to End-Of-Input-File
   Read (Phone-Number-From-File)
          WHERE PHONE_NUMBER = :Phone-Number-From-File

The access path is through the index on PHONE_NUMBER.

When ever an UR is running  the  sequential detection mechanism is at work and is keeping an eye on the page access patterns. If  the sequential detection mechanisim determines that the UR is accessing pages in almost a sequential fashion, it starts the Dynamic prefetching and brings in the next 32 pages to the bufferpool so that the pages that DB2 thinks you might ask for are already in the bufferpool.

 It needs to be understood that there is a chance that the UR might not even need to probe any rows in any of these 32 pages (unlike a Sequential access). DB2 just takes a chance and brings these 32 pages ahead of time hoping that the next Get page request can be fulfilled from the bufferpool.

Now, Let us see the basis upon which DB2 determines that the pages are accesed in a near sequential fashion.

Let P denote the prefetch quantity for the table space (or bufferpool to which the table space is assigned). Usually this number is 32.

Let Page X denote the most recent Page that was retrieved by a Get page. Let Page Y  denote the Page requested by the next Get page. Then PageY is said to be page sequential to Page X if  Page Y lies within Page X + P/2 pages or Page X + 16 pages. Page Y is page sequential to Page X if  Page Y is within 16 pages of Page X in the forward direction.

The Sequential Detection mechanisim always keeps track of the last 8 pages accessed by the UR. If  more than 4  out of the last 8 pages accessed by the UR were page sequential to one an other then near sequential access is signaled and Dynamic Prefetch is turned on.

For initial data access sequential prefetch is requested starting at the next page  that triggered the prefetch for P (32) pages.

For subsequent Get pages assuming that both the page is Page Sequential and Dynamic Prefetch is still in effect, then
(a) If the Next Get page is in the first P/2 pages then no prefetch is triggered.  The first P/2 pages is called RUN1
(b) If the Next Get page is in the second P/2 pages (from P/2+1 to P), also called RUN2. Also an other P pages are brought into the bufferpool, called RUN3. Also the next time around a range is checked for a page RUN2 is treated as RUN1 and RUN3 is treated as RUN2.

The pages that are read are constantly monitored and if they fall out of page sequential and Dynamic prefetch is turned off.  It is important to remember that a UR can make DB2 fall in and out of  dynamic prefetch. If  more than 4 pages out of the last 8 pages read are not page sequential then DB2 just stops the dynamic prefetch and goes to Synchronous I/O.

If  after a while DB2 detectcs more than 4 pages out of 8 pages are page sequential then DB2 signals "initial data access sequential" and the process starts all over again.

In our example let us assume that the first 8 records are from pages 01, 02, 05, 07, 11, 14,15 and 17. All the 8  pages accessed are page sequential as page 02 is within 16 pages of  page01. Page 05 is within 16 pages of page 02. Page 07 is within 16 pages of page 05. etc..

DB2 requires a minimum of 8 page reads to determine whether dynamic prefetch needs to be turned on or not.

Since 5 (more than 4)  pages out of the last 8 pages read are page sequential , dynamic prefetch is enabled. As soon as a Get page is issued for page 17, a prefetch is issued  for the next 32 pages(pages 18-49) (One prefetch quantity) and is brought into the bufferpool even though no request has been made by the UR for any of the  pages in the range (pages 18-49). DB2 hopes based on previous Get page behaviour  that the next Get page will be for a page in this range.


 Dynamic prefetch has been done and 32 pages have been read in advance. So what happens next?  The 32 pages brought in are divided into two sets of 16 pages each (light green 18-33 RUN1 and dark green 34-49 RUN2).  If the next Get page request can be satisfied within the first 16 pagesRUN1 then no prefetch is requested. If the next get page is satisfied in the range 34-49 RUN2 then an other 32 pages are brought into the bufferpool (light blue  50-81  RUN3).  In our case Page 38 is in RUN2 and therefore it triggers a prefetch of 32 pages.

For the next Get page request Pages 34-49 is treated as RUN1 and Pages 50-81 is treated as RUN2.

The next page 57 is in RUN2 and hence an other prefetch is triggered . The newly prefetched range becomes RUN2 and Pages 50-81 becomes RUN1.

This continues on till More than 4 out of the last 8 get pages are not page sequential.  This happens in our case when the Get page 75  is issued.

When you look at the last 8 pages , Page 7, Page 11, Page 14, Page 15, Page 17, Page 38, Page 57, Page 75 , it is obvious that more than 4 out of 8 pages are NOT page sequential. At this point Dynamic prefetch is turned off.

Subsequent Get pages are satisfied by Synchronous I/O., till Page 246 when again "initial data access sequential" is declared and the process starts all over again.