Buffer Pool General

This topic shows detailed information about Statistics - Buffer Pool General.

This block is only printed when the buffer pool is active. If more than one 4 KB or 32 KB buffer pool block is present, a summary block showing buffer pool totals is also printed. If the report contains both 4 KB and 32 KB buffer pool blocks, a block showing the totals for all buffer pools is printed.

Statistics - Buffer Pool General

The field labels shown in the following sample layout of Statistics - Buffer Pool General are described in the following section.


BP0    GENERAL               QUANTITY  /SECOND  /THREAD  /COMMIT
---------------------------  --------  -------  -------  -------
CURRENT ACTIVE BUFFERS         164.00      N/A      N/A      N/A
UNAVAIL.BUFFER-VPOOL FULL        0.00     0.00     0.00     0.00
                                                                
NUMBER OF DATASET OPENS          0.00     0.00     0.00     0.00
                                                                
BUFFERS ALLOCATED - VPOOL     5000.00      N/A      N/A      N/A
                                                                
DFHSM MIGRATED DATASET           0.00     0.00     0.00     0.00
DFHSM RECALL TIMEOUTS            0.00     0.00     0.00     0.00
                                                                
VPOOL EXPANS. OR CONTRACT.       0.00     0.00     0.00     0.00
VPOOL EXPANS. FAILURES           0.00     0.00     0.00     0.00
                                                                
CONCUR.PREF.I/O STREAMS-HWM      0.00      N/A      N/A      N/A
PREF.I/O STREAMS REDUCTION       0.00     0.00     0.00     0.00
PARALLEL QUERY REQUESTS          0.00     0.00     0.00     0.00
PARALL.QUERY REQ.REDUCTION       0.00     0.00     0.00     0.00
PREF.QUANT.REDUCED TO 1/2        0.00     0.00     0.00     0.00
PREF.QUANT.REDUCED TO 1/4        0.00     0.00     0.00     0.00
                                                                
NUMBER OF LPL INSERTS            0.00     0.00     0.00     0.00

MIN BUFFERS ON SLRU              0.00      N/A      N/A      N/A
MAX BUFFERS ON SLRU              0.00      N/A      N/A      N/A
SLRU LENGTH EQUALS VPSEQT        0.00     0.00     0.00     0.00
GETPAGE REQU RANDOM ON SLRU      0.00     0.00     0.00     0.00

CURRENT ACTIVE BUFFERS

The total number of currently active (nonstealable) buffers. This field is an instantaneous sample of the number of buffers in the buffer pool that were updated or in use at the time this monitor data was requested. Because this field gives a snapshot value at statistics collection time, it only shows a problem if it happens at this time.

Background and Tuning Information

The buffer pool might be too small if the percentage of active pages in the buffer pool is beyond the deferred write threshold (DWQT).

Field Name: QBSTCBA

UNAVAIL.BUFFER-VPOOL FULL

The number of times a usable buffer could not be located in the virtual buffer pool because the virtual buffer pool was full.

Background and Tuning Information

Ideally, this value should be 0. Any other value indicates that the buffer pool is underallocated. In this case, use the ALTER BUFFERPOOL command to increase the virtual buffer pool size until this value remains at 0.

Field Name: QBSTXFL

NUMBER OF DATASET OPENS

The number of data sets physically opened successfully. This value is cumulative from the start of the Db2 statistics interval.

Field Name: QBSTDSO

BUFFERS ALLOCATED - VPOOL

The number of buffers allocated for a virtual buffer pool.

Note: In Db2 10, the buffer pool size can increase continuously by up to 25% for each Db2 restart. In Db2 11, the AUTOSIZE option of the ALTER BUFFERPOOL command can limit the range within VPSIZEMIN and VPSIZEMAX.

Background and Tuning Information

You should monitor the buffer pool hit ratio field to find the optimum size of the buffer pool. Usually the buffer pool hit ratio is improved by increasing the size of the buffer pool. However, paging the buffer pool storage impacts Db2 performance if the virtual buffer pool is too large.

Page-ins Required for Read I/O (QBSTRPI) and Page-ins Required for Write I/O (QBSTWPI) are useful when determining whether paging affects the performance of a certain buffer pool. The Resource Measurement Facility (RMF) also provides reports on MVS paging activity:

Storage Paging: When the virtual buffer pool is extended into expanded storage, MVS storage paging activity occurs. If a large buffer pool size results in excessive storage paging, consider allocating more real storage to the LPAR.
Paging to Auxiliary Storage: If the virtual buffer pool size requirements exceed the central storage and expanded storage available, the oldest buffer pool pages migrate to auxiliary paging storage. When these pages are accessed subsequently, I/O must bring them back into real storage. This should be avoided. You could have a smaller buffer pool and let Db2 do the I/O rather than use MVS paging with its I/O CPU overhead. This is a situation that you (as the system programmer) should monitor.

You can use the ALTER BUFFERPOOL command to alter the size of the virtual buffer pool.

Changing the size of the virtual buffer pool implicitly changes the buffer pool thresholds. See the Deferred Write Threshold Reached field (QBSTDWT).

Field Name: QBSTVPL

DFHSM MIGRATED DATASET

The number of times migrated data sets were encountered.

Field Name: QBSTMIG

DFHSM RECALL TIMEOUTS

The number of recall timeouts.

Field Name: QBSTRTO

VPOOL EXPANS. OR CONTRACT.

The number of successful virtual buffer pool expansions or contractions due to the ALTER BUFFERPOOL command. An increase in this counter indicates that buffer-pool-related system parameters have been changed.

Field Name: QBSTVPA

This is an exception field.

VPOOL EXPANS. FAILURES

The total number of virtual buffer pool expansion failures due to the lack of virtual storage space.

Background and Tuning Information

Ideally, this value should be 0. If it is not, check the virtual storage allocation of the Db2 database address space for areas that can be reduced. For example, you can reduce the size of other buffer pools.

Field Name: QBSTXFV

This is an exception field.

CONCUR.PREF.I/O STREAMS-HWM

The highest number of concurrent prefetch I/O streams allocated to support a parallel I/O or CP query in this buffer pool. It reflects prefetch activities for non-workfile page sets.

This number only applies to query I/O and CP parallelism.

Field Name: QBSTXIS

This is an exception field.

PREF.I/O STREAMS REDUCTION

The total number of requested prefetch I/O streams that were denied because of a lack of buffer pool storage space.

It only applies to query I/O and CP parallelism.

For example, if 100 prefetch I/O streams are requested and only 80 are granted, then 20 is added to the number in this field.

Background and Tuning Information

Consider increasing the size of the buffer pool if this value is not 0.

The ratio of this field and the Reduced parallel query requests field gives the average degree of parallel query processing that was reduced because of insufficient buffer pool space. The Prefetch I/O streams - Concurrent streams - high-water mark field gives the highest degree of parallel query processing that was reduced for one or more queries processed in parallel.

The number in this field reflects the prefetch activities for non-workfile page sets.

Field Name: QBSTJIS

This is an exception field.

PARALLEL QUERY REQUESTS

The total number of requests made for parallel query support in this buffer pool. This field only applies to non-workfile page sets in query I/O and CP parallelism.

Field Name: QBSTPQO

PARALL.QUERY REQ.REDUCTION

The number of times that Db2 could not allocate the requested number of buffer pages to allow a parallel group to run as planned.

This field only applies to non-workfile page sets in query I/O and CP parallelism.

Background and Tuning Information

This is caused by a shortage of storage in the buffer pool. A nonzero value could suggest that the buffer pool is too small. You can increase it using the ALTER BUFFERPOOL command.

Field Name: QBSTPQF

This is an exception field.

PREF.QUANT.REDUCED TO 1/2

The total number of times prefetch quantity is reduced from normal to 50% of normal. The normal size depends on the page size of the buffer pool.

This field only applies to query I/O and CP parallelism.

Background and Tuning Information

The number in this field indicates when Db2 had to reduce the sequential prefetch quantity to continue executing concurrently with parallel queries in the system. If the number is small, it may be tolerable.

Field Name: QBSTPL1

This is an exception field.

PREF.QUANT.REDUCED TO 1/4

The total number of times prefetch quantity is reduced from 50% to 25% of normal. The normal size depends on the page size of the buffer pool.

This field only applies to query I/O and CP parallelism.

Background and Tuning Information

The query response for parallel queries can be significantly degraded if the value in this field is not 0.

Field Name: QBSTPL2

This is an exception field.

NUMBER OF LPL INSERTS

The number of times that one or more pages were added to the logical page list (LPL).

Field Name: QBSTLPL

MIN BUFFERS ON SLRU

The minimum number of buffers on the sequential least-recently-used (SLRU) chain in the last statistical period. This is the low-water mark (LWM) within an interval.

Field Name: QBSTSMIN

MAX BUFFERS ON SLRU

The maximum number of buffers on the sequential least-recently-used (SLRU) chain in the last statistical period. This is the high-water mark (HWM) within an interval.

Field Name: QBSTSMAX

SLRU LENGTH EQUALS VPSEQT

The number of times when the length of the sequential least-recently-used (SLRU) chain equals the sequential steal threshold VPSEQT.

Field Name: QBSTHST

GETPAGE REQU RANDOM ON SLRU

The number of times that the random Getpage request has a buffer hit and the buffer is on the least-recently-used (SLRU) chain.

Field Name: QBSTRHS