Buffer pool report and trace

The bufferpool activity report and trace shows information about buffer pool activity including hit ratio, getpages, and prefetch requests.

Information is provided for each active buffer pool. When there is more than one active buffer pool, information is provided for each aggregation and shows the total buffer pool activity (all buffer pools, all 4 KB buffer pools, all 32 KB buffer pools).

Figure 1. Buffer pool report and trace
Report:                                        Trace:

BP1    BPOOL ACTIVITY   AVERAGE     TOTAL      BP0    BPOOL ACTIVITY     TOTAL
---------------------  --------  --------      ---------------------  --------
BPOOL HIT RATIO (%)        4.74       N/A      BPOOL HIT RATIO (%)           5
GETPAGES               10485.8K  10485761      GETPAGES               10485761
BUFFER UPDATES             0.00         0      BUFFER UPDATES                0
SYNCHRONOUS WRITE          0.00         0      SYNCHRONOUS WRITE             0
SYNCHRONOUS READ        9989.1K   9989081      SYNCHRONOUS READ        9989081
SEQ. PREFETCH REQS         0.00         0      SEQ. PREFETCH REQS            0
LIST PREFETCH REQS         0.00         0      LIST PREFETCH REQS            0
DYN. PREFETCH REQS         0.00         0      DYN. PREFETCH REQS            0
PAGES READ ASYNCHR.        0.00         0      PAGES READ ASYNCHR.           0
ZHL SYNC. READS         9989.0K   9989026      ZHL SYNC. READS         9989026
ZHL READ CPU USED      3:47.166  3:47.166      ZHL READ CPU USED      3:47.166
BPOOL HIT RATIO (%)
The percentage of getpage operations that were satisfied by a page already in the buffer pool. The value is the ratio of the number of successful getpage operations minus the number of pages read from DASD (both synchronously and using prefetch), to the number of successful getpage operations, expressed as a percentage.

The highest possible hit ratio (100%) indicates that every page that is requested is in the buffer pool. If a requested page is not in the buffer pool, the hit ratio is 0% or less. If the hit ratio is negative, then prefetch brought pages into the buffer pool that are not subsequently referenced, either because the query stops before it reaches the end of the table space, or because the prefetched pages are stolen by Db2 for reuse before the query can access them.

A low buffer pool hit ratio is not necessarily bad. The hit ratio is a relative value, based on the type of application. For example, an application that browses large data might have a buffer pool hit ratio of 0. Watch for those cases where the hit ratio drops significantly for the same application. Here are some suggestions to increase the buffer hit ratio:

  • Run the REORG utility for indexes or table spaces associated with the virtual buffer pool.
  • Reserve more pages for random I/O by setting the SEQUENTIAL STEAL THRESHOLD (VPSEQT) to a lower value.
  • Increase the buffer pool as long as the cost of paging does not outweigh the benefit of I/O avoidance.
  • Establish more separate buffer pools, perhaps to isolate different applications.
  • Place the objects that are only accessed sequentially in a separate buffer pool.

The hit ratio measurement becomes less meaningful if the buffer pool is used by additional processes, such as utilities or work files.

Field Name: ABUFFRAT

GETPAGES

The number of Getpage requests. This counter is incremented by successful Getpage requests for queries processed in parallel for each thread and for all successful and unsuccessful Getpage requests for queries that are not processed in parallel.

Background and Tuning Information

Reducing the number of Getpages can improve DB2 performance by reducing the number of synchronous page reads. With fewer Getpages, the requested page is more likely to be returned from the buffer pool. CPU usage is also reduced.

Check the ratio of Getpages to SQL DML statements, as a rule of thumb, try and keep this ratio below six for a typical online transaction SQL.

You might need to modify the database and query design, for example:

  • Add indexes to tables to reduce the number of pages scanned.
  • Reassess the number of tables used and denormalize them, if necessary.

    As an example, a large table with many columns can result in several pages being fetched to satisfy a simple query requesting just a few columns. Splitting such a table into several tables with fewer columns, tailored to queries, will result in fewer pages returned for each query.

  • Use correlated rather than non-correlated queries to force the use of an index.

Field Name: QBACGET

This is an exception field.

BUFFER UPDATES

The number of times a buffer update occurs. This is incremented every time a page is updated and is ready to be written to DASD. If the same page is updated twice, for example, the number is incremented by 2.

This number is kept for all types of pages including data pages and work-file pages.

Background and Tuning Information

A nonzero value indicates any of the following activities:
  • SQL INSERT, UPDATE, or DELETE
  • Merge scan join
  • Internal sort activity on the work files
Check the access path to determine whether sort activity can be minimized or avoided.

Field Name: QBACSWS

This is an exception field.

SYNCHRONOUS WRITE

The number of immediate (synchronous) write I/O operations.

Background and Tuning Information

Although an immediate write is rare, a small nonzero value is acceptable. A large value indicates that the system needs tuning.

Field Name: QBACIMW

This is an exception field.

SYNCHRONOUS READ

The number of synchronous read I/O operations. DB2 increments this counter for each media manager synchronous physical read. Asynchronous I/O requests are not counted.

Field Name: QBACRIO

This is an exception field.

SEQ. PREFETCH REQS

The number of SEQUENTIAL PREFETCH requests. This is incremented for each PREFETCH request. Each request can result in an I/O read. If it does, up to 64 pages can be read for SQL and up to 128 pages for utilities. For SQL, depending on the buffer pool size, a request does not result in an I/O if all the requested pages are already in the buffer pool.

DB2 can use sequential prefetch if the data is accessed in sequential order even though sequential prefetch was not requested at bind time. This is known as sequential detection and is not included in the sequential prefetch count. Sequential detection is included in dynamic prefetch requests field.

Background and Tuning Information

Table space scans and nonmatching index scans generally use sequential prefetch.

Field Name: QBACSEQ

This is an exception field.

LIST PREFETCH REQS

The number of LIST PREFETCH requests.

Special Considerations:
  1. List prefetch allows DB2 to access data pages efficiently even if the needed data pages are not contiguous. It can be used with single index access and is always used with multiple index access.
  2. List prefetch is always used to access data from the inner table during a hybrid join.
  3. Data pages are read in quantities equal to the sequential prefetch quantity, which depends on the buffer pool size and is usually 64 pages.
  4. During bind time DB2 does not use list prefetch if the estimated number of RIDs to be processed would take more than 50% of the RID pool. During execution time, list prefetch processing terminates if DB2 detects that more than 25% of the rows in the table need to be accessed. If list prefetch is terminated, it is indicated in IFCID 125.

Field Name: QBACLPF

This is an exception field.

DYN. PREFETCH REQS

The number of (dynamic) PREFETCH requests. This is triggered by sequential detection. This includes prefetches for segmented table spaces.

Background and Tuning Information

Dynamic prefetch is typically used for a SELECT or UPDATE that is run repeatedly, accessing the index for each access.

If sequential prefetch, list prefetch, and dynamic prefetch reads have large values, check whether the access path can be improved.

Field Name: QBACDPF

This is an exception field.

PAGES READ ASYNCHR.

The number of asynchronous pages read by prefetch that the agent triggered.

Background and Tuning Information

This is used to determine the buffer pool hit ratio: (Getpage requests - Synchronous reads - Asynchronous pages read) / Getpage requests.

Field Name: QBACSIO

This is an exception field.

ZHL SYNC READS
The number of sync reads with zHyperLink.

Field name: QBACSYI

ZHL READ CPU USED
The accumulated and consumed class 2 time used for successful zHyperLink I/O. A thread consumes CPU time for the entire duration of DASD I/O that is done using zHyperLink.
Format: HH:MM:SS.mmmm
Note: If the value is less then 1 min it will be printed in seconds (for example, 0.037187).

Field name: QBACSYIT