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
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
ACCOUNT_BALANCE = ACCOUNT_BALANCE - 5.00
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
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.
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
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
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.