The Buffer Pool Statistics section
The buffer pool statistics values are retrieved from IFCID 2.
1 OMEGAMON XE FOR DB2 PE (V5R4M0) - BUFFER POOL ACTIVITY REPORT PAGE: 1-6
ORDER: BPID-QPAGESET
SORTBY: BPID,ASYNCPAGE TOP: 17 LEVEL: SUMMARY
GROUP: N/P LOCATION: OMPDC61 DB2 VERSION: V12
MEMBER: N/P REQUESTED FROM: NOT SPECIFIED TO: NOT SPECIFIED
SUBSYSTEM: DC61 INTERVAL FROM: 04/05/16 13:40:56 TO: 04/05/16 14:47:00
======= Buffer Pool Statistics =======
BUFFER POOL ID BP32K BP32K4 BP8K0 BP8K4 BP16K0
------------------------- ---------- ---------- ---------- ---------- ----------
Buffers allocated 1000 250 2000 1000 2000
Reached threshold
Deferred write 0 0 0 0 0
Vertical deferred write 16 14 0 12 0
Data manager 0 0 0 0 0
SLRU length equ. VPSEQT 0 0 0 0 0
Current active buffer 126 202 2 947 0
Buffer pool full 0 0 0 0 0
Data set opens 0 0 0 0 0
Migrated data set 0 0 0 0 0
Recall timeout 0 0 0 0 0
Expansion or contraction 0 0 0 0 0
Expansion failure 0 0 0 0 0
Concurrent prefetch I/O 0 0 20 0 0
Prefetch I/O reduction 0 0 0 0 0
Parallel query request 0 0 0 0 0
Reduced 0 0 0 0 0
Pref quantity reduced
Reduced to 1/2 0 0 0 0 0
Reduced to 1/4 0 0 0 0 0
Min buffers on SLRU 89 48 1579 53 10
Max buffers on SLRU 89 48 1579 53 10
Random getpage SLRU hits 1 0 0 0 0
Pages added to LPL 0 0 0 0 0
System hit ratio 99.28 70.83 100.00 75.76 n/c
Application hit ratio 99.52 99.57 100.00 99.32 n/c
Getpage request 5661 696 935 2780 0
Sequential 2277 514 826 2076 0
Overflow sequential 0 212 0 746 0
Random 3384 182 109 704 0
Overflow random 0 172 0 594 0
Unsuccessful getpages 0 0 0 0 0
Unsucc seq getpages 0 0 0 0 0
Read
Synchronous read 27 3 0 19 0
Sequential 1 2 0 18 0
Overflow sequential 0 2 0 18 0
Random 26 1 0 1 0
Overflow random 0 1 0 1 0
Sequential prefetch
Request 0 52 0 46 0
Read 0 52 0 43 0
Pages read 0 200 0 655 0
Pages read/read n/c 3.85 n/c 15.23 n/c
List prefetch
Request 0 0 0 0 0
Read 0 0 0 0 0
Pages read 0 0 0 0 0
Pages read/read n/c n/c n/c n/c n/c
1 OMEGAMON XE FOR DB2 PE (V5R4M0) - BUFFER POOL ACTIVITY REPORT PAGE: 1-7
ORDER: BPID-QPAGESET
SORTBY: BPID,ASYNCPAGE TOP: 17 LEVEL: SUMMARY
GROUP: N/P LOCATION: OMPDC61 DB2 VERSION: V12
MEMBER: N/P REQUESTED FROM: NOT SPECIFIED TO: NOT SPECIFIED
SUBSYSTEM: DC61 INTERVAL FROM: 04/05/16 13:40:56 TO: 04/05/16 14:47:00
BUFFER POOL ID BP32K BP32K4 BP8K0 BP8K4 BP16K0
(continue)
------------------------- ---------- ---------- ---------- ---------- ----------
Dynamic prefetch
Request 7 0 5 0 0
Read 6 0 0 0 0
Pages read 14 0 0 0 0
Pages read/read 2.33 n/c n/c n/c n/c
Prefetch disabled
No buffer 0 0 0 1 0
No read engine 0 0 0 0 0
Page-ins required 41 0 0 0 0
Write
Buffer updates 5973 25948 67 27010 0
Page write 1027 192 9 1153 0
Updates/page write 5.82 135.15 7.44 23.43 n/c
Synchronous write 0 0 3 410 0
Asynchronous write 271 61 6 72 0
Pages/write req
Page-ins required 0 0 0 0 0
Sort/merge
Merge
Pass requested 0 0 0 0 0
Pass degraded low buffer 0 0 0 0 0
Workfile
Max concurrent used 2 0 0 0 0
Req rejected low buffer 0 0 0 0 0
Req all merge passes 0 0 0 0 0
Not created no buffer 0 0 0 0 0
Prefetch not scheduled 0 0 0 0 0
Pages to destruct 0 0 0 0 0
Pages not written 0 0 0 0 0
Unlock castout
I/O operations 0 0 0 0 0
Pages written 0 0 0 0 0
Simulated BP Activity
Current pages in use n/p n/p n/p n/p n/p
Max pages in use n/p n/p n/p n/p n/p
Current seq pages in use n/p n/p n/p n/p n/p
Max seq pages in use n/p n/p n/p n/p n/p
Avoidable read I/O
Sync read I/O (R) n/p n/p n/p n/p n/p
Sync read I/O (S) n/p n/p n/p n/p n/p
Async read I/O n/p n/p n/p n/p n/p
Sync GBP reads (R) n/p n/p n/p n/p n/p
Sync GBP reads (S) n/p n/p n/p n/p n/p
Async GBP reads n/p n/p n/p n/p n/p
- Buffers allocated *
- The number of buffers that are allocated to a virtual buffer pool.
The number of buffers within each pool is always less than or equal to the corresponding value specified at installation time or when using the ALTER BUFFERPOOL command.
- Reached threshold — Deferred write *
- The number of times the deferred write threshold (DWTH) was reached.
This threshold is a percentage of the virtual buffer pool that unavailable pages might occupy, including both updated pages and pages in use. Db2® checks this threshold when an update to a page is completed. If the percentage of unavailable pages in the virtual buffer pool exceeds the threshold, write operations are scheduled for enough data sets (at up to 128 pages per data set) to decrease the number of unavailable buffers to 10% below the threshold.
- Reached threshold — Vertical deferred write *
- The number of times the vertical deferred write threshold was reached. This threshold is expressed as a percentage of the virtual buffer pool that may be occupied by updated pages from one single data set. This threshold is checked whenever an update to a page is completed. If the percentage of updated pages for the data set exceeds the threshold, writes are scheduled for that data set.
- Reached threshold — Data manager *
- The number of times the data manager critical threshold (DMTH-95%)
was reached.
This field shows how many times a page was immediately released because the data management threshold was reached. If the threshold is constantly reached, you need to identify the objects that are monopolizing the buffer pool.
The threshold is checked before a page is read or updated. If the threshold has not been exceeded, Db2 accesses the page in the virtual buffer pool once for each page, no matter how many rows are retrieved or updated in that page. If the threshold has been exceeded, Getpage and Release requests apply to rows instead of pages. That is, when more than one row is retrieved or updated in a page, more than one Getpage and Release request is performed on that page.
The data manager threshold (DMTH) is fixed threshold, set to 95% of the virtual pool size. Reaching this threshold has a significant impact on a system's performance. Reaching this threshold for one pool can cause Db2 not to release pages in other pools as well.
- Reached threshold — SLRU length equ. VPSEQT
- The total number of times when length of SLRU = VPSEQT.
- Current active buffer *
- 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.
- Buffer pool full *
- The number of times a usable buffer cannot be located in the virtual buffer pool because the virtual buffer pool was full.
- Data set opens
- The number of data sets physically opened successfully. This value is cumulative from the start of the Db2 statistics interval.
- Migrated data set
- The number of times migrated data sets were encountered.
- Recall timeout *
- The number of recall timeouts.
- Expansion or contraction
- 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.
- Expansion failure
- The total number of virtual buffer pool expansion failures due to the lack of virtual storage space.
- Concurrent prefetch I/O
- 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-work-file page sets. This number only applies to query I/O and CP parallelism.
- Prefetch I/O 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.
- Parallel query request
- The total number of requests made for parallel query support in this buffer pool. This field only applies to non-work-file page sets in query I/O and CP parallelism.
- Parallel query request — Reduced *
- The number of times that Db2 cannot allocate the requested number of buffer pages to allow a parallel group to run as planned. This field only applies to non-work-file page sets in query I/O and CP parallelism.
- Pref quantity reduced — 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.
- Pref quantity reduced — 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.
- Min buffers on SLRU
- The minimum number of buffers on SLRU, low water mark within an interval.
- Max buffers on SLRU
- The maximum number of buffers on SLRU, high water mark within an interval.
- Random getpage SLRU hits
- The total number of times that the random Getpage request has a buffer hit and the buffer is on the least-recently-used (SLRU) chain.
- Pages added to LPL
- The number of times that one or more pages were added to the logical page list (LPL). The field name is QBSTLPL.
- System hit ratio *
- The number of Getpage requests by Db2 and
satisfied by the buffer pool, expressed as a percentage of all Getpage
requests.
This shows the percentage of pages that are found in the buffer pool without doing any type of I/O.
The system hit ratio is affected by prefetch I/O. The value is usually lower in an application that causes mostly sequential accesses, respectively higher if a series of similar operations are performed on the same data.
A negative system hit ratio indicates that the number of prefetched pages is greater than the number of Getpages. This happens if prefetch operations are bringing in pages that are not subsequently referenced. The reason for this is that the query stops before it reaches the end of the prefetched pages, or that the prefetched pages are stolen by Db2 for reuse before the query can access them. Consider increasing the sequential steal threshold (VPSEQT), increasing the buffer pool size, or revising the assignments of page sets to buffer pools.
Compare the value in this field with the application hit ratio to determine the efficiency of prefetch operations.
- Application hit ratio *
- The number of Getpage requests issued by applications and satisfied
by the buffer pool, expressed as a percentage of all Getpage requests
issued by applications.
A low hit ratio indicates the level of synchronous I/O because prefetched pages that are already in the buffer pool count as hits. The value is a relative value depending on the type of application. For example, an application that browses large amounts of noncontinuous data might have a buffer pool hit ratio of 0. Check those cases in which the hit ratio drops significantly for the same application.
- Getpage request *
- This counter is incremented for:
- Each successful or unsuccessful page request, where the query is not processed in parallel.
- Each successful page request, where the query is processed in parallel.
Unsuccessful page requests for queries processed in parallel are reported in the Unsuccessful Page Requests field.
- Getpage request — Sequential
- The number of Getpage requests issued by sequential access requesters.
- Getpage request — Overflow sequential
- The number of sequential GETPAGE requests using overflowed buffers (QBSTASGE).
- Getpage request — Random
- The number of random Getpage requests.
- Getpage request — Overflow random
- The number of non-sequential GETPAGE requests using overflowed buffers (QBSTAGET).
- Getpage request — Unsuccessful
- The number of times a conditional GETPAGE request could not be satisfied for this buffer pool during the specified time interval. This counter is used only when queries are processed in parallel. If the value is close to zero, most pages are already prefetched into the buffer pool and wait time for synchronous I/O is small. The field name is QBSTNGT.
- Getpage request — Unsucc seq getpages
- The total number of sequential getpage requests, which failed because the page was not in the buffer pool.
- Read — Synchronous read
- The number of synchronous read I/O operations performed by Db2 for applications and utilities.
- Read — Synchronous read — Sequential *
- The number of synchronous read I/O requests issued by sequential access requesters.
- Read — Synchronous read — Overflow sequential
- The number of synchronous read I/O operations for sequential GETPAGE requests using overflowed buffers (QBSTASSE).
- Read — Synchronous read — Random
- The number of random synchronous read I/O requests.
- Read — Synchronous read — Overflow random
- The number of synchronous read I/O operations for non-sequential GETPAGE requests using overflowed buffers (QBSTASYN).
- Read — Sequential prefetch — Request
- The number of sequential prefetch requests. This counter is incremented
for each prefetch request (which can result in an I/O read). If it
results in an I/O read, up to 32 pages may be read for SQL, and up
to 64 pages for utilities. A request does not result in an I/O read
if all pages to be prefetched are already in the buffer pool.
Sequential detection is not included in this counter but is separately recorded in the Dynamic Prefetch - Requested field.
- Read — Sequential prefetch — Read
- The number of asynchronous read I/O operations due to normal sequential prefetch (applications and utilities).
- Read — Sequential prefetch — Pages read
- The total number of pages read due to a normal sequential prefetch. A sequential prefetch request does not result in a read I/O if all the requiredd pages are found in the buffer pool.
- Read — Sequential prefetch — Pages read/read
- The number of sequential prefetch pages read per sequential prefetch read I/O operation.
- Read — List Prefetch — Request
- The number of list sequential prefetch requests.
List sequential prefetch allows Db2 to access data pages efficiently even when the required data pages are not contiguous. It allows CP and I/O operations to be overlapped.
- Read — List Prefetch — Read
- The number of asynchronous read I/O operations caused by the list
sequential prefetch.
The number of pages read is recorded in the List Prefetch Pages Read field.
- Read — List Prefetch — Pages read
- The number of pages read due to a list prefetch. A list sequential prefetch request does not result in a read I/O if all the requiredd pages are found in the buffer pool.
- Read — List Prefetch — Pages read/read
- The number of list prefetch pages read per list prefetch read I/O.
- Read — Dynamic Prefetch — Request
- The number of dynamic prefetch requests. Dynamic prefetch is the process that is triggered because of sequential detection. If the prefetch request results in an I/O read, up to 32 advancing pages may be read at a time.
- Read — Dynamic Prefetch — Read
- The number of asynchronous read I/Os because of dynamic prefetch. The number of pages read is recorded in the Dynamic Prefetch Pages Read field.
- Read — Dynamic Prefetch — Pages read
- The number of pages read because of dynamic prefetch. Dynamic prefetch is the process that is triggered because of sequential detection.
- Read — Dynamic Prefetch — Pages read/read
- The number of dynamic prefetch pages read per dynamic prefetch read I/O.
- Read — Prefetch disabled — No buffer *
- The total number of times sequential prefetch was disabled or
canceled because buffers were not available. This is the number of
times the sequential prefetch threshold (SPTH) is reached. Ideally,
this value should be 0. If the threshold is constantly reached, you
need to identify the objects that are monopolizing the buffer pool.
The sequential prefetch threshold (SPTH) is a fixed threshold, set to 90% of the virtual pool size, that is compared prior to a sequential prefetch. If the threshold is reached, the prefetch is disabled.
- Read — Prefetch disabled — No read engine *
- The total number of times a sequential prefetch is disabled because of an unavailable read engine.
- Read — Page-ins required *
- The number of page-ins required for a read I/O.
If the number of Page-ins required is roughly approximately 5% of the total number of Getpage requests, the paging activity is at an acceptable rate. A rate near zero might indicate that the buffer pool is oversized.
- Write — Buffer updates
- The number of times buffer updates were requested against pages in the buffer pool.
- Write — Page write
- The number of pages in the buffer pool written to a hard disk drive.
- Write — Updates/page write
- The number of buffer update requests, divided by the number of
pages written from the buffer pool to a hard disk drive.
The ratio of BUFFER UPDATES to PAGES WRITTEN suggests a high level of efficiency as the ratio increases, because more updates are being externalized per physical write. For example, if there are 10 updates on the same page before it is externalized, then the ratio is 10:1 or 10. If all 10 updates are on 10 distinct pages, then the ratio is 10:10 or 1.
- Write — Synchronous write
- The total number of immediate writes.
Immediate writes occur when:
- An immediate write threshold is reached
- No deferred write engines are available
- More than two checkpoints pass without a page being written
Immediate writes are a type of synchronous write, but not the only one. Sometimes Db2 uses synchronous writes even when the immediate write threshold (IWTH) is not exceeded, for example when more than two checkpoints pass without a page being written. This type of situation does not indicate a buffer shortage.
The immediate write threshold (IWTH) is a fixed threshold, set to 97.5% of the virtual pool size, that is checked whenever a page needs to be updated. If the threshold is reached, writes are synchronous. Then, the application cannot proceed until the write operation has completed.
- Write — Asynchronous write
- The number of asynchronous write I/O operations performed by media manager to a direct access storage device.
- Write — Pages/write req
- The number of pages written from the buffer pool to a hard disk drive per synchronous or asynchronous write I/O. This count does not include preformatting I/O, such as I/O needed to prepare a data set for use.
- Write — Page-ins required *
- The number of page-ins required for a write I/O.
This counter is incremented each time the media manager does not find a page in central storage. This counter does not differentiate between expanded storage and page data sets.
- Merge — Pass requested
- The total number of merge passes for Db2 sort activities. This value reflects how many merge passes were requested for Db2 to determine the number of work files permitted to support each merge pass.
- Merge — Pass degraded low buffer *
- The number of times that a merge pass was not efficiently performed due to a shortage of space in the buffer pool. The number in this field is incremented for each merge pass where the maximum number of work files allowed is less than the number of work files requested.
- Workfile — Max concurrent used
- The maximum number of work files concurrently used during merge
processing within this statistics period.
Ideally, each work file needs 16 buffers to allow Db2 to perform a sequential prefetch for work files.
- Workfile — Req rejected low buffer *
- The total number of work files that were rejected during all merge passes because of insufficient buffer resources.
- Workfile — Req all merge passes
- The total number of work files requested for all merge passes.
This field and the merge passes requested field can be used to determine the average number of work files requested in one single merge pass.
For Db2 to perform an efficient prefetch for work files, each work file should have at least 16 dedicated buffers. Work files used during sort phase processing or other non-sort-related processing are not included in this number.
- Workfile — Not created no buffer *
- Only applicable if Db2 is running under MVS/XA. The number of times a work file cannot be created due to insufficient buffer resources. It indicates that a sort is in progress and limited in regard to the number of work files it can use.
- Workfile — Prefetch not scheduled *
- The number of times a sequential prefetch was not scheduled for a work file because the dynamic prefetch quantity is zero.
- Workfile — Pages to destruct
- The number of pages for which a destructive read was requested.
- Workfile — Pages not written
- The number of pages removed from the data set deferred write queue for destructive Read requests.
- Unlock castout
- The number of times DB2 issued an unlock request to the coupling facility for completed castout I/Os.
- Unlock castout — I/O operations
- Unlock castout — Pages written
- Simulated BP Activity — Current pages in use
- The total number of pages currently in the simulated buffer pool.
- Simulated BP Activity — Max pages in use
- The highest number of pages in the simulated buffer pool.
- Simulated BP Activity — Current seq pages in use
- The total number of sequential pages in the simulated buffer pool.
- Simulated BP Activity — Max seq pages in use
- The highest number of sequential pages in the simulated buffer pool.
- Simulated BP Activity — Avoidable read I/O
- Sync read I/O (R)
- The total number of pages found in the simulated buffer pool for a random request that resulted in a synchronous read I/O.
- Sync read I/O (S)
- The total number of pages found in the simulated buffer pool for a sequential request that resulted in a synchronous read I/O.
- Async read I/O
- The total number of pages found in the simulated buffer pool for a prefetch request that resulted in an asynchronous read I/O.
- Sync GBP reads (R)
- The total number of pages found in the simulated buffer pool for a random request that resulted in a synchronous GBP read.
- Sync GBP reads (S)
- The total number of pages found in the simulated buffer pool for a sequential request that resulted in a synchronous GBP read.
- ASync GBP reads
- The total number of pages found in the simulated buffer pool for a prefetch request that resulted in an asynchronous GBP read.
- Pages moved into sim BP
- The total number of pages logically moved into the simulated buffer pool.
- Total avoidable - sync I/O delay (msec)
- The total time in milliseconds waiting for synchronous read I/O for pages found in the simulated buffer pool.