Buffer pool read operations (BSTATDR)

GETPAGE REQUESTS

Number of getpage requests (QBSTGET)

In the DB2 Statistics Overview Report--averages, this value is the average number of GETPAGE requests per CREATE THREAD during this statistics interval. This counter is incremented when the Data Manager component of DB2 requests a page (hence getpage) from DB2’s Buffer Manager. The Buffer Manager first looks into one of its virtual buffer pools, next to hiperpools (if any), and then does a read from DASD.

Tuning Tip: The goal in DB2 performance tuning is to keep the ratio of getpages to real I/O very high. This number indicates either good sequential performance or a re-reference of random data, depending on the application.

GETPAGE REQUEST—SEQUENTIAL

Number of getpage requests for sequential access (QBSTSGT)

This number is a count of getpages for all sequential access requesters including sequential prefetch, dynamic sequential prefetch, and list prefetch operations.

Tuning Tip: This number is useful in determining what the sequential steal threshold should be for that buffer pool. This number compared with the total number of getpages indicates the environment for random and sequential processes. Those two numbers can help to set the VPSEQT parameter.

GETPAGE REQUEST—RANDOM

Number of getpage requests for random access (QBSTGET - QBSTSGT)

This number is a count of getpages for all random access requesters.

SYNCHRONOUS READS

Number of synchronous read I/Os (QBSTRIO)

In the DB2 Statistics Overview Report--averages, this value is the average number of read I/Os issued per CREATE THREAD during this statistics interval. This counter is incremented every time the DB2 Buffer Manager could not find a page in global, central, or expanded storage. DB2 must perform a physical read of DASD to obtain the necessary data. The application waits for DB2 to perform the operation.

Tuning Tip: Unnecessary read I/Os are one of the principal culprits in poorly tuned applications. While random I/O cannot be avoided, critical re-referenced indexes and tables can be kept in memory. At other times, the strategy is to get the data into memory before the application needs it, typically when processing the data pages sequentially. This is done with prefetch and possibly with parallel processing.

SYNCHRON READS—SEQUENTIAL

Number of synchronous I/Os for sequential access (QBSTSIO)

This count is the number of non-sequential pages found while trying to process data sequentially. DB2 must then do random reads.

Tuning Tip: This number can indicate data or index fragmentation. It may also indicate buffer pool thrashing. If the pool is too small, sequentially read pages may be stolen before being used. They must then be read in again.

SYNCHRON READS-RANDOM

Difference between total read I/O and sequential read I/O during this statistics interval (QBSTRIO - QBSTSIO)

GETPAGE PER SYNCH READS

Number of random getpage requests for each random synchronous read I/O request

SEQUENT’L PREFETCH REQUEST

Number of sequential prefetch requests (QBSTSEQ)

This counter is incremented every time a DB2 plan calls for a sequential prefetch operation, which normally attempts to bring in up to 32 pages per read I/O. Sequential prefetch in this context includes only plans which have indicated a use of sequential prefetch in their EXPLAINs. Dynamic sequential prefetch (where DB2 decides the program is traversing the data sequentially and dynamically brings in the next 32 pages requested) is not included in this count.

SEQUENTIAL PREFETCH READS

Number of asynchronous read I/O operations due to normal sequential prefetch for applications and utilities (QBSTPIO)

This value is usually less than the number of sequential prefetch requests because the prefetch read I/O is not activated if all pages in the prefetch range are already in the buffer pool or if the prefetch is canceled.

PAGES READ VIA SEQ PREF

Number of asynchronous pages read by sequential prefetch (QBSTSPP)

This counter is incremented with the number of pages read using normal (not dynamic) sequential prefetch.

SEQ PREFETCH PAGES READ

S PRF PAGE READ/S PRF READ

Ratio of the number of sequential prefetch pages read per sequential prefetch read I/O (QBSTSPP/QBSTPIO)

LIST PREFETCH REQUESTS

Number of list prefetch requests (QBSTLPF)

This number is incremented each time an access path requires that index keys be sorted into data order using the RID pool. List prefetch can be used with nonmatching index scans and is always used when multiple indexes are used to access tables. It is also used to sort index data during a hybrid join.

Tuning Tip: At BIND time, DB2 computes whether list prefetch should be activated by estimating the number of index RIDS to be scanned and comparing that to the size of the RID pool. If the number exceeds 50%, DB2 does not activate list prefetch. At execution time, if the object would take more than 25% of the RID pool, DB2 disables list prefetch. If this situation occurs frequently, consider enlarging the RID pool by increasing the MAXRBLK parameter of DSNZPARM on installation panel DSNTIPC.

LIST PREFETCH READS

Number of asynchronous read I/O operations caused by the list sequential prefetch (QBSTLIO)

This value is usually less than the number of list sequential prefetch requests because the prefetch read I/O is not activated if all of the pages to be prefetched are already in the buffer pool or if the prefetch is canceled.

PAGES READ VIA LIST PREF

Number of asynchronous pages read by list prefetch (QBSTLPP)

This value is a count of the number of index pages read by list prefetch to satisfy nonmatching index scans, multiple index access support, and certain types of join to be read into the RID pool for RID pool support.

Tuning Tip: DB2, upon sorting the RID list created by list prefetch, will then access the data using sequential prefetch to gain the performance boost of processing the data. This is an asynchronous process not charged to the calling application.

L PRF PAGE READ/L PRF READ

Ratio of the number of list prefetch pages read per list prefetch read I/O (QBSTLPP/QBSTLIO)

DYNAMIC PREFETCH REQUESTED

Number of dynamic prefetch requests (QBSTDPF)

This field is incremented every time DB2 determines sequential prefetch should be dynamically activated. DB2 analyzes the data accessed to determine whether the last five of eight pages accessed are in sequential order and the application would be benefited by prefetch. DB2 then turns on sequential prefetch until the pages being accessed are no longer in sequential order.

Tuning Tip: Normally this situation assists programs by providing read-ahead buffering for processing; GETPAGEs that would have to wait for synchronous I/O now find the page in the buffer pool. This number should be monitored since the more dynamic prefetch requests activated, the more buffer pool resources can be strained. It may be necessary to alter the buffer pool size or sequential steal threshold to handle increased demands of dynamic prefetch.

DYNAMIC PREFETCH READS

Number of asynchronous read I/Os because of dynamic prefetch (QBSTDIO)

This value is usually less than the number of dynamic prefetch requests because prefetch read I/O is not activated if all of the pages to be prefetched are already in the buffer pool or if the prefetch is canceled.

PAGES READ VIA DYN PREFTCH

Number of asynchronous pages read by dynamic prefetch (QBSTDPP)

This number is the total number of pages accessed asynchronously using sequential prefetch because DB2 dynamically determined by sequential detection that the application was processing the pages sequentially.

Tuning Tip: Normally, this process aids performance by having read-ahead buffering of pages in the buffer pool. System tuners and DBAs should be aware of when this happens so the number of pages allocated for sequential processing is adequate to support the workload. The VPSIZE (virtual pool size) and the VPSEQT (virtual pool sequential steal thresholds) are the numbers that should be tuned to support the workload.

D PRF PAGE READ/D PRF READ

Ratio of the number of dynamic prefetch pages read per dynamic prefetch read I/O (QBSTDPP/QBSTDIO)

PREF DISABLED—NO BUFFER

Number of times sequential prefetch was disabled because buffers were not available (QBSTSPD)

PREF DISABLED—NO READ ENG

Number of times sequential prefetch disabled--unavailable read engine (QBSTREE)

This counter is incremented when 300 read engines are activated in a DB2 subsystem and another is needed.

Tuning Tip: This situation should occur rarely and the number should be close to zero. Since the 300 read engine limit is hard coded, the only solution is to spread out the workload over a longer period of time so that the number of read engines never exceeds 300.

PAGE-INS REQUIRED FOR READ

Number of page faults experienced when DB2 references a page before starting a read or write operation during this statistics interval (QBSTRPI+QBSTWPI)

SYNC HPOOL READ

Number of successful synchronous requests to move a page from a hiperpool to a virtual buffer pool ( QBSTHRE)

ASYNC HPOOL READ

Number of pages moved asynchronously from the hiperpool to the virtual buffer pool (QBSTHRA)

HPOOL READ FAILED

Number of pages for which a synchronous or asynchronous read request failed because the backing-expanded storage page was stolen by the system ( QBSTHRF)

ASYN DA MOVR HPOOL READ-S

Number of pages moved successfully from the hiperpool to the virtual buffer pool by using the Asynchronous Data Mover Facility (ADMF) ( QBSTARA)

ASYN DA MOVR HPOOL READ-F

Number of pages for which a read request, using the asynchronous data mover facility (ADMF), failed because the backing-expanded storage was stolen by the system (QBSTARF)

BPOOL HIT RATIO (%)

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

• Total pages is the number of getpage requests (QBSTGET)
• Total I/O is the sum of:
  • Synchronous read I/Os (QBSTRIO)
  • Asynchronous pages read by sequential prefetch (QBSTSPP)
  • Asynchronous pages read by list prefetch (QBSTLPP)
  • Asynchronous pages read by dynamic prefetch (QBSTDPP)