IT Management

The DB2 Buffer Pool Hit Ratio Is Dead!

4 Pages

Ok, so perhaps the title of this article is not completely accurate.

However, for the most part, it’s true. Buffer pools have been used in all database management systems for more than three decades. The purpose of a buffer pool is to keep data in memory to avoid the processing cost and lengthy delay times of physical I/O to disk.

Types of Access/ Processing

In all systems, there are two general types of processing: random and sequential. Random access provides the greatest opportunity to avoid I/O by finding the desired data in the pool. Sequential processing, or scanning, rarely provides an opportunity for finding the data in the pool, unless the objects accessed are smaller than the number of buffers for the pool.

In its simplest terms, sequential processing means processing data from the beginning to the end. Within this processing type, there can be two methods of processing: The first method starts at the first record and just processes the next record in sequence, and the second method accesses all/most/many records in an ascending sequence, but not necessarily all the data records.

Prior to DB2, using hierarchical or network database structures, each record or block processed required a physical I/O when it was not in a pool. With the advent of relational database structures, part of the design objective was to enhance the processing of large sets of data. This brings us to the sequential processing abilities of DB2. In order to reduce the number of physical I/Os, a sequential prefetch can be issued to read up to 32 pages for a single I/O. The reason I say “up to,” is because the physical read is issued only for the pages within the block of 32 that are not already in the pool. Therefore, a prefetch I/O may read less than 32 physical pages.

There are three types of sequential processing within DB2, and the Optimizer determines which method is used:

  • Sequential Prefetch — Processes or scans all data from beginning to end because the Optimizer has determined that this is the most effective way to reduce the elapsed time and processing cost for the SQL request.
  • List Prefetch — Uses multiple indexes from one or more tablespaces, combines (using and/or) the Row IDs (RIDs) in the RidPool, and then issues prefetch reads to bring the required pages into a pool. The pages actually read may, or may not be contiguous.
  • Dynamic Prefetch — This type of processing is used when the access path determined by the Optimizer is random. However, as processing actually takes place, the Buffer Manager determines that data is being processed in an ascending sequential manner. When the number of buffers in a pool is 1,000 or greater, the prefetch quantity is 32. The Optimizer uses this as the basis for determining sequential processing. Within a block of 32 pages, if eight pages within a range of 16 are accessed in an ascending sequential manner, a prefetch request is issued for the next 32 pages. Also, once prefetch has been turned on, the Buffer Manager tracks it at the cursor level. If the next eight pages with a range of 16 are not accessed sequentially, prefetch is turned off. Dynamic Prefetch can be turned on and off many times during processing.

Hit Ratio Approaches

Based on the processing that takes place within the system, and depending on what you want to measure, there are two different hit ratio calculations.

Application Hit Ratio

4 Pages