Package buffer pool activity - Class 10

Package buffer pool activity - Class 10 report block shows buffer pool information at package level. It is repeated for each package present in the requested report. The block is headed by the package name.

The following example shows both layouts, the report on the left, and the trace layout on the right.

Accounting - Package Buffer Pool Activity - Class 10 


Report:                                       Trace:

DSNESM68               AVERAGE     TOTAL      DSNESM68                  TOTAL
-------------------    -------   --------     -------------------    --------
BPOOL HIT RATIO (%)       87.78       N/A     BPOOL HIT RATIO (%)          27
GETPAGES               20306.43    426435     GETPAGES                   1704
BUFFER UPDATES         27044.33    567931     BUFFER UPDATES              189
SYNCHRONOUS WRITE         17.38       365     SYNCHRONOUS WRITE             2
SYNCHRONOUS READ        1653.90     34732     SYNCHRONOUS READ           1239
SEQ. PREFETCH REQS       156.10      3278     SEQ. PREFETCH REQS           33
LIST PREFETCH REQS         0.24         5     LIST PREFETCH REQS            4
DYN. PREFETCH REQS         0.33         7     DYN. PREFETCH REQS            7
PAGES READ ASYNCHR.      827.86     17385     PAGES READ ASYNCHR.           0
ZHL SYNC. READ          1581.10     33203     ZHL SYNC. READ                2
ZHL READ ELPSD TIME    0.037187  0.780934     ZHL READ ELPSD TIME    0.000096
BPOOL HIT RATIO (%)

The percentage of Getpage operations that were satisfied by a page already in the buffer pool.

The value is calculated as the ratio of 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.

Background and Tuning Information

The highest possible hit ratio is 100%, that is, when every page requested is always in the buffer pool. If the requested page is not in the buffer pool, the hit ratio is 0% or less. If the hit ratio is negative, this means that 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: ABUFFRAP

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: QBACGETP

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: QBACSWSP

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: QBACIMWP

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: QBACRIOP

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: QBACSEQP

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: QBACLPFP

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: QBACDPFP

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: QBACSIOP

ZHL SYNC. READ

The number of DASD reads done using zHyperLink.

Field Name: QBACSYIP

ZHL READ ELPSD TIME
The amount of CPU time used for successful zHyperLink reads. zHyperLink I/O is synchronous with respect to the CPU, thus CPU time accumulates from the beginning of the I/O until it completes.
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: ABACSYIP