Buffer pool activity

BPOOL HIT RATIO(%)

Buffer pool hit ratio with prefetch, calculated as (total Pages - total I/O)/total Pages x 100.0

Total Pages is the number of getpage requests (QBACGET). Total I/O is the sum of

Synchronous read I/Os (QBACRIO)

Asynchronous pages read by sequential prefetch (QBACSIO)

GETPAGES

Number of getpage requests (QBACGET)

In the DB2 Accounting Overview Report and the DB2 Accounting Buffer Pool Report, this value is the average number of getpage requests per plan execution. This is a count of the number of requests for a data page (successful and unsuccessful) from Db2’s data manager to the buffer manager for normal Db2 processing. Db2 looks first in a central storage virtual buffer pool, then to an expanded storage hiperpool, if present. If not present, Db2 reads the page from DASD. In parallel query processing, the field counts only the number of successful requests.

Tuning Tip: The general guideline for tuning any query is to attempt to find the page in memory if possible, either by maintaining large pools to improve the hit ratio for random reads or by use of sequential prefetch, dynamic detection, or list prefetch to improve sequential reads. The higher the ratio of pages found in memory, the less I/O the query costs and the faster the query will run.

(BACCTLT only)

GETPAGES - FAILED

Number of unsuccessful GETPAGE operations (QBACNGT)

BUFFER UPDATES

Number of page updates (QBACSWS)

In the DB2 Accounting Overview Report, this value is the average number of pages updated per plan execution. In the DB2 Accounting Buffer Pool Report, it is the average number of times a buffer update occurs for the agent (QBACSWS/#OCCUR). This counter is incremented every time a page is updated and ready to be externalized to DASD. If the same page is updated twice, for example, the count is incremented by 2.

This count includes not only updates to data pages but also workfile pages, so if a sort is used, this number can be higher than expected. Db2 keeps committed pages in a buffer pool until a system checkpoint occurs or the deferred write threshold is encountered. Db2 attempts to write data to the page set asynchronously. The number of pages used in prefetch is governed by the size of the buffer pool. Prefetch can be 8, 16, or 32 pages.

SYNCHRONOUS WRITE

Number of immediate write I/Os (QBACIMW)

In the DB2 Accounting Buffer Pool Report, this value is the average number of immediate write I/Os. This counter is the number of synchronous write I/Os. This situation only occurs when the immediate write threshold of 97.5% of pages in use in a buffer pool is reached. This situation is extremely resource intensive and highly undesirable. To avoid reaching this threshold, the VPSIZE (virtual pool size) should be tuned so this never occurs.

SYNCHRONOUS READ

Number of synchronous read I/Os (QBACRIO)

In the DB2 Accounting Overview Report and the DB2 Accounting Buffer Pool Report, this value is the average number of synchronous read I/Os issued per plan execution. This counter is incremented every time Db2 cannot find a page in memory and must issue a random I/O to DASD to retrieve it.

Tuning Tip: The overall goal in Db2 tuning is to reduce the number of synchronous I/Os by having needed pages in memory, either by caching or use of prefetch. The thread waits for synchronous I/O activity to be completed before proceeding. This wait time is measured with accounting class 3 and is reflected in the SYNC I/O field for threads and packages.

SEQUENTIAL PREFETCH REQS

LIST PREFETCH REQS

Number of list prefetch requests (QBACLPF)

In the DB2 Accounting Buffer Pool Report, this value is the average number of list prefetch requests. This counter is incremented each time Db2 reads index RIDs (Row IDs) in non-matching index scans, multiple index access path selections, or several types of join access paths. List prefetch allows Db2 to sort the index RIDs into data row order, thereby enabling prefetch in data sequence, a process which eliminates data page re-reads and allows read-ahead buffering. The RIDs are sorted in the RID pool.

Tuning Tip: List prefetch is not chosen at BIND if Db2 determines that the resultant size of the RID list would exceed 50% of the RID pool. At execution time, Db2 disables list prefetch if a single index occupies more than 25% of the RID pool or the RID pool storage is exhausted. When list prefetch is disabled, the query becomes a table space scan, which is highly undesirable. If it occurs frequently, change the SQL to reduce the number of RIDs or increase the size of the RID pool.

DYNAMIC PREFETCH REQS

Number of dynamic prefetch requests (QBACDPF)

PAGES READ ASYNCHR

Number of asynchronous pages read by prefetch operations (QBACSIO)

In the DB2 Accounting Buffer Pool Report, this value is the average number of asynchronous pages read by prefetch under the control of the agent.

Tuning Tip: Generally, unless the application is totally random, the higher the prefetch number, the more likely it is that the data will be in memory when needed. The more successful that read-ahead buffering is, the faster the application should perform.