We know before executing a query ,DB2 determines its access path during bind time and writes it on to the plan table.However there can be cases when DB2 decides that there is even a better way to access the data during run time, after analyzing the way the data is accessed. Prefetching is one such way to enhance the access strategy dynamically during Run time. (Note: Its run time, not bind time!!!)
The concept is to read a set of pages,generally 32 pages into buffer pool with only one asynchronous I/O operation with the expectation that they will be required by an application in future. In this way,it can save CPU and IO cost in a huge amount. Elimination or reduction of time waits in DB2 from the application perspective is what prefetch is about. There are several ways in which prefetching can be done.
Types of prefetching :
The PREFETCH column in PLAN_TABLE will contains one of these values: S, D, L or blank.
1. Sequential Prefetch: (Value of 'S' in PLAN_TABLE) Sequential prefetch brings consecutive data pages in chunks into Buffer pool instead of one page at a time. At Bind time DB2 sets PREFETCH=S in PLAN table whenever sequential prefetch is chosen for selected access path.
Sequential prefetch is generally chosen when the only way to access the data is by doing a Table Space Scan & Index Scans.
Sequential prefetch is activated if a number of data or index pages are accessed in sequence.One simple technique to enable a sequential prefetch is " not to use any predicate in query". However this is not recommended. Check the below query :
Query like 'SELECT CITY FROM TEST.EMPLOYEE' invokes sequential prefetch
Obviously without a predicate, table space scan will be selected by optimizer and will most definitely use Sequential prefetch.Use of ORDER BY, Range predicates like 'between', '< 'than, '> ' than encourages the use of sequential prefetch.We can define the number of pages the database manager will prefetch, using the PREFETCHSIZE clause while creating the Table space or altering the table space.
Thus reading several pages in one shot will reduce the IO overhead for our application and improve its performance.
2. Dynamic Prefetch: (value of 'D' in PLAN_TABLE) DB2 v9 uses Dynamic prefetch in almost all situation except table space scans when it uses sequential prefetch. We can say DB2 perform the sequential prefetch dynamically at execution time. It reduces paging and improve performance especially for access to data that might be arranged sequentially for some set of pages but scattered randomly on other pages. At run time, dynamic prefetch might or might not be used.
It uses Sequential detection algorithm to detect the future reading of pages.The most recent eight pages are tracked, and data access is determined to be sequential if more than four of the last eight pages are page-sequential.
With dynamic prefetch, DB2 can automatically adjust between multi page prefetch and single page .
This information is stored till commit point,ie if we had used RELEASE(COMMIT) in bind card. To store this information beyond commit point we need to use RELEASE(DEALLOCATE).
3. List Prefetch: (value of 'L' in PLAN_TABLE). This is the way to access the data pages effectively even when data pages are not consecutive.
This is used when the optimizer sees ,at bind time; that skip sequential processing would be efficient. The fetched data pages need not be contiguous.
How List prefetch works?: We all know Index contains key values from data pages and RID(record id) which contains the page number where the record is physically located. Rather than accessing the rows in the table directly from index, DB2 at times decides to build a RID in its own memory list. This list will be sorted and merged with another RID list
Example:
Select Fname, Lname from EMP
where City = 'AR' ;
Now when the optimizer decides that RID list should be used with this query,it would gather the RIDs of the matching rows into its memory:
4,34
1,17
2,11.
This RID list would be sorted into page number and slot number order.
1,17
2,11
4,34
When DB2 access physical pages, it will sequentially start with page 1, the sequentially access page2.
It will skip page 3, and again sequentially access and read Page 4 and so on.
Note: List Prefetch may fail when DB2 finds that the RID list is large. In that case, the access path will be changed to table scan.
4. Prefetch = BLANK: When DB2 optimizer does not anticipate ever needing to use prefetch operations.However at run time,sequential detection is still employed.
No comments:
Post a Comment