The Detail Activity section
The detail activity counter values about buffer pool operations are retrieved from IFCIDs 6, 7, 8, 9, 10, and 198.
1 DB2 BUFFER POOL ANALYZER (V5R4M0) - BUFFER POOL ACTIVITY REPORT PAGE: 1-3
ORDER: BPID-QPAGESET
TOP: 11 LEVEL: DETAIL
GROUP: N/P LOCATION: PMO2D721 DB2 VERSION: V11
MEMBER: N/P REQUESTED FROM: NOT SPECIFIED TO: NOT SPECIFIED
SUBSYSTEM: D721 INTERVAL FROM: 12/06/13 16:08:30 TO: 12/06/13 16:09:22
======= Detail Activity =======
BPID BP0 BP0 BP0 BP0 BP0 BP0
QPAGESET DAADB01 DAADB01 DAADB01 DAADB06 DAADB06 DAADB06
DAALLX01 DAALLX02 SYSLGRNX DAAAUH01 DAASSX01 SYSUSER
-------------- ---------- ---------- ---------- ---------- ---------- ----------
BP Hit ratio(%)
System 88.9 77.8 100.0 100.0 100.0 100.0
Application 88.9 77.8 100.0 100.0 100.0 100.0
Read I/O 80.0 77.8 100.0 100.0 100.0 100.0
Getpage 9 9 9 32 44 4
Sequential 0 0 0 0 0 0
Random 9 9 9 32 44 4
Ridlist 0 0 0 0 0 0
Hit 8 7 9 32 44 4
Miss random 1 2 0 0 0 0
Miss asynch 0 0 0 0 0 0
Noread 0 0 0 0 0 0
Read request 1 2 0 0 0 0
Synchronous 1 2 0 0 0 0
Seq prefetch 0 0 0 0 0 0
List pref 0 0 0 0 0 0
Dyn prefetch 0 0 0 0 0 0
Delay(msec)
Synchronous 1.6 2.0 n/c n/c n/c n/c
Seq pref n/c n/c n/c n/c n/c n/c
List pref n/c n/c n/c n/c n/c n/c
Dyn pref n/c n/c n/c n/c n/c n/c
Read page 1 2 0 0 0 0
Synchronous 1 2 0 0 0 0
Seq prefetch 0 0 0 0 0 0
List pref 0 0 0 0 0 0
Dyn prefetch 0 0 0 0 0 0
Upd/wrt page 0.7 0.7 0.5 n/c n/c n/c
Page/wrt req 1.5 1.5 1.0 n/c n/c n/c
Buf Update 2 2 1 0 0 0
Write request 2 2 2 0 0 0
Synchronous 1 1 1 0 0 0
Asynchr 1 1 1 0 0 0
Delay(msec)
Synchr 1.9 2.2 2.6 n/c n/c n/c
Asynchr 199.5 93.2 129.3 n/c n/c n/c
Write page 3 3 2 0 0 0
Synchronous 1 1 1 0 0 0
Asynchr 2 2 1 0 0 0
============== ========== ========== ========== ========== ========== ==========
1 DB2 BUFFER POOL ANALYZER (V5R4M0) - BUFFER POOL ACTIVITY REPORT PAGE: 1-4
ORDER: BPID-QPAGESET
TOP: 11 LEVEL: DETAIL
GROUP: N/P LOCATION: PMO2D721 DB2 VERSION: V11
MEMBER: N/P REQUESTED FROM: NOT SPECIFIED TO: NOT SPECIFIED
SUBSYSTEM: D721 INTERVAL FROM: 12/06/13 16:08:30 TO: 12/06/13 16:09:22
======= Detail Activity =======
BPID BP1 BP1 BP1 BP1 BP1 BP1
QPAGESET FIJ1DB01 FIJ1DB01 FIJ1DB01 FIJ1DB01 FIJ1DB01 FIJ1DB01
FIJCACIT FIJCAMD1 FIJCAMHD FIJCAMSG FIJCAMTX FIJCCONT
-------------- ---------- ---------- ---------- ---------- ---------- ----------
BP Hit ratio(%)
System 98.9 66.7 93.3 95.1 93.0 90.9
Application 98.9 66.7 93.3 95.1 93.0 90.9
Read I/O 50.0 67.8 100.0 100.0 100.0 100.0
Getpage 350 6 30 61 43 22
Sequential 0 0 0 0 0 0
Random 350 6 30 61 43 22
Ridlist 0 0 0 0 0 0
Hit 346 4 28 58 40 20
Miss random 4 2 2 3 3 2
Miss asynch 0 0 0 0 0 0
Noread 0 0 0 0 0 0
Read request 4 2 2 3 3 2
Synchronous 4 2 2 3 3 2
Seq prefetch 0 0 0 0 0 0
List pref 0 0 0 0 0 0
Dyn prefetch 0 0 0 0 0 0
Delay(msec)
Synchronous 5.6 8.3 8.7 5.3 20.6 9.6
Seq pref n/c n/c n/c n/c n/c n/c
List pref n/c n/c n/c n/c n/c n/c
Dyn pref n/c n/c n/c n/c n/c n/c
Read page 4 2 2 3 3 2
Synchronous 4 2 2 3 3 2
Seq prefetch 0 0 0 0 0 0
List pref 0 0 0 0 0 0
Dyn prefetch 0 0 0 0 0 0
Upd/wrt page 22.3 n/c n/c 5.0 9.5 n/c
Page/wrt req 2.0 n/c n/c 1.0 1.0 n/c
Buf Update 89 0 0 10 19 0
Write request 2 0 0 2 2 0
Synchronous 1 0 0 1 1 0
Asynchr 1 0 0 1 1 0
Delay(msec)
Synchr 2.0 n/c n/c 1.9 2.0 n/c
Asynchr 146.3 n/c n/c 146.8 165.1 n/c
Write page 4 0 0 2 2 0
Synchronous 1 0 0 1 1 0
Asynchr 3 0 0 1 1 0
============== ========== ========== ========== ========== ========== ==========
1 DB2 BUFFER POOL ANALYZER (V5R4M0) - BUFFER POOL ACTIVITY REPORT PAGE: 1-5
ORDER: BPID-QPAGESET
TOP: 11 LEVEL: DETAIL
GROUP: N/P LOCATION: PMO2D721 DB2 VERSION: V11
MEMBER: N/P REQUESTED FROM: NOT SPECIFIED TO: NOT SPECIFIED
SUBSYSTEM: D721 INTERVAL FROM: 12/06/13 16:08:30 TO: 12/06/13 16:09:22
======= Detail Activity =======
BPID BP1 BP1 BP1 BP1 BP1 BP1
QPAGESET FIJ1DB01 FIJ1DB01 FIJ1DB01 FIJ1DB01 FIJ1DB01 Others
FIJCENGC FIJCGROS FIJCGRPG FIJCINPG FIJCINVD 29
-------------- ---------- ---------- ---------- ---------- ---------- ----------
BP Hit ratio(%)
System 0.0 99.1 99.9 83.3 83.0 88.2
Application 0.0 99.2 99.9 83.3 90.6 96.6
Read I/O 90.0 97.8 100.0 100.0 100.0 100.0
Getpage 2 6445 3662 12 53 38605
Sequential 0 0 0 0 0 4860
Random 2 6445 3662 12 53 33493
Ridlist 0 0 0 0 0 252
Hit 0 6396 3660 10 48 37286
Miss random 2 49 2 2 5 261
Miss asynch 0 0 0 0 0 1040
Noread 0 0 0 0 0 18
Read request 2 52 2 2 6 1426
Synchronous 2 49 2 2 5 1299
Seq prefetch 0 0 0 0 0 97
List pref 0 0 0 0 0 8
Dyn prefetch 0 3 0 0 1 22
Delay(msec)
Synchronous 9.2 3.7 11.0 23.5 5.3 11.7
Seq pref n/c n/c n/c n/c n/c 38.1
List pref n/c n/c n/c n/c n/c 21.1
Dyn pref n/c 11.9 n/c n/c 3.9 22.1
Read page 2 59 2 2 9 4572
Synchronous 2 49 2 2 5 1299
Seq prefetch 0 0 0 0 0 2857
List pref 0 0 0 0 0 83
Dyn prefetch 0 10 0 0 4 333
Upd/wrt page n/c 37.7 n/c n/c n/c 25.1
Page/wrt req n/c 16.3 n/c n/c n/c 6.4
Buf Update 0 1848 0 0 0 9097
Write request 0 3 0 0 0 57
Synchronous 0 1 0 0 0 31
Asynchr 0 2 0 0 0 26
Delay(msec)
Synchr n/c 3.4 n/c n/c n/c 7.2
Asynchr n/c 121.0 n/c n/c n/c 99.1
Write page 0 49 0 0 0 363
Synchronous 0 1 0 0 0 31
Asynchr 0 48 0 0 0 332
============== ========== ========== ========== ========== ========== ==========
1 DB2 BUFFER POOL ANALYZER (V5R4M0) - BUFFER POOL ACTIVITY REPORT PAGE: 1-6
ORDER: BPID-QPAGESET
TOP: 11 LEVEL: DETAIL
GROUP: N/P LOCATION: PMO2D721 DB2 VERSION: V11
MEMBER: N/P REQUESTED FROM: NOT SPECIFIED TO: NOT SPECIFIED
SUBSYSTEM: D721 INTERVAL FROM: 12/06/13 16:08:30 TO: 12/06/13 16:09:22
======= Detail Activity =======
BPID BP2 BP2 BP2
QPAGESET WTNTEST WTNTEST WTNTEST
WTNMTS2 WTNMUS4 WTNMUS5
-------------- ---------- ---------- ----------
BP Hit ratio(%)
System 0.0 -8.2 -2.2
Application 0.0 99.6 100.0
Read I/O 30.0 77.8 100.0
Getpage 1 502 2002
Sequential 0 501 2001
Random 1 1 1
Ridlist 0 0 0
Hit 0 500 2001
Miss random 1 1 0
Miss asynch 0 1 1
Noread 0 0 0
Read request 1 19 65
Synchronous 1 2 1
Seq prefetch 0 17 64
List pref 0 0 0
Dyn prefetch 0 0 0
Delay(msec)
Synchronous 1.8 10.7 45.0
Seq pref n/c 26.4 25.6
List pref n/c n/c n/c
Dyn pref n/c n/c n/c
Read page 1 543 2046
Synchronous 1 2 1
Seq prefetch 0 541 2045
List pref 0 0 0
Dyn prefetch 0 0 0
Upd/wrt page 0.3 n/c n/c
Page/wrt req 1.5 n/c n/c
Buf Update 1 0 0
Write request 2 0 0
Synchronous 1 0 0
Asynchr 1 0 0
Delay(msec)
Synchr 2.1 n/c n/c
Asynchr 104.4 n/c n/c
Write page 3 0 0
Synchronous 1 0 0
Asynchr 2 0 0
============== ========== ========== ==========
1 DB2 BUFFER POOL ANALYZER (V5R4M0) - BUFFER POOL ACTIVITY REPORT PAGE: 1-7
ORDER: BPID-QPAGESET
TOP: 11 LEVEL: DETAIL
GROUP: N/P LOCATION: PMO2D721 DB2 VERSION: V11
MEMBER: N/P REQUESTED FROM: NOT SPECIFIED TO: NOT SPECIFIED
SUBSYSTEM: D721 INTERVAL FROM: 12/06/13 16:08:30 TO: 12/06/13 16:09:22
======= Detail Activity =======
BPID BP5
QPAGESET PARLDABA
TAB1TS
-------------- ----------
BP Hit ratio(%)
System 1.9
Application 100.0
Read I/O 80.0
Getpage 23812
Sequential 23812
Random 0
Ridlist 0
Hit 23808
Miss random 0
Miss asynch 4
Noread 0
Read request 738
Synchronous 4
Seq prefetch 734
List pref 0
Dyn prefetch 0
Delay(msec)
Synchronous 28.7
Seq pref 38.6
List pref n/c
Dyn pref n/c
Read page 23360
Synchronous 4
Seq prefetch 23356
List pref 0
Dyn prefetch 0
Upd/wrt page n/c
Page/wrt req n/c
Buf Update 0
Write request 0
Synchronous 0
Asynchr 0
Delay(msec)
Synchr n/c
Asynchr n/c
Write page 0
Synchronous 0
Asynchr 0
============== ==========
1 DB2 BUFFER POOL ANALYZER (V5R4M0) - BUFFER POOL ACTIVITY REPORT PAGE: 1-8
ORDER: BPID-QPAGESET
TOP: 11 LEVEL: DETAIL
GROUP: N/P LOCATION: PMO2D721 DB2 VERSION: V11
MEMBER: N/P REQUESTED FROM: NOT SPECIFIED TO: NOT SPECIFIED
SUBSYSTEM: D721 INTERVAL FROM: 12/06/13 16:08:30 TO: 12/06/13 16:09:22
======= Detail Activity =======
BPID BP7
QPAGESET DAADB07
DAA4K01
-------------- ----------
BP Hit ratio(%)
System 79.5
Application 99.9
Read I/O 80.0
Getpage 2873
Sequential 2838
Random 35
Ridlist 0
Hit 640
Miss random 0
Miss asynch 3
Noread 2230
Read request 152
Synchronous 3
Seq prefetch 149
List pref 0
Dyn prefetch 0
Delay(msec)
Synchronous 35.8
Seq pref 12.4
List pref n/c
Dyn pref n/c
Read page 590
Synchronous 3
Seq prefetch 587
List pref 0
Dyn prefetch 0
Upd/wrt page 2.1
Page/wrt req 20.4
Buf Update 4573
Write request 108
Synchronous 0
Asynchr 108
Delay(msec)
Synchr n/c
Asynchr 39.5
Write page 2200
Synchronous 0
Asynchr 2200
============== ==========
1 DB2 BUFFER POOL ANALYZER (V5R4M0) - BUFFER POOL ACTIVITY REPORT PAGE: 1-9
ORDER: BPID-QPAGESET
TOP: 11 LEVEL: DETAIL
GROUP: N/P LOCATION: PMO2D721 DB2 VERSION: V11
MEMBER: N/P REQUESTED FROM: NOT SPECIFIED TO: NOT SPECIFIED
SUBSYSTEM: D721 INTERVAL FROM: 12/06/13 16:08:30 TO: 12/06/13 16:09:22
======= Detail Activity =======
BPID BP32K
QPAGESET FIJ1DB32
FIJS0001
-------------- ----------
BP Hit ratio(%)
System 100.0
Application 100.0
Read I/O 80.0
Getpage 8
Sequential 0
Random 8
Ridlist 0
Hit 8
Miss random 0
Miss asynch 0
Noread 0
Read request 0
Synchronous 0
Seq prefetch 0
List pref 0
Dyn prefetch 0
Delay(msec)
Synchronous n/c
Seq pref n/c
List pref n/c
Dyn pref n/c
Read page 0
Synchronous 0
Seq prefetch 0
List pref 0
Dyn prefetch 0
Upd/wrt page n/c
Page/wrt req n/c
Buf Update 0
Write request 0
Synchronous 0
Asynchr 0
Delay(msec)
Synchr n/c
Asynchr n/c
Write page 0
Synchronous 0
Asynchr 0
============== ==========
1 DB2 BUFFER POOL ANALYZER (V5R4M0) - BUFFER POOL ACTIVITY REPORT PAGE: 1-10
ORDER: BPID-QPAGESET
TOP: 11 LEVEL: DETAIL
GROUP: N/P LOCATION: PMO2D721 DB2 VERSION: V11
MEMBER: N/P REQUESTED FROM: NOT SPECIFIED TO: NOT SPECIFIED
SUBSYSTEM: D721 INTERVAL FROM: 12/06/13 16:08:30 TO: 12/06/13 16:09:22
======= Detail Activity =======
********** TOTAL **********
BPID BP0 BP1 BP2 BP5 BP7 BP32K
-------------- ---------- ---------- ---------- ---------- ---------- ----------
BP Hit ratio(%)
System 97.2 90.5 -3.4 1.9 79.5 100.0
Application 97.2 97.2 99.8 100.0 99.9 100.0
Read I/O 80.0 77.8 100.0 100.0 100.0 100.0
Getpage 107 49291 2505 23812 2873 8
Sequential 0 4860 2502 23812 2838 0
Random 107 44179 3 0 35 8
Ridlist 0 252 0 0 0 0
Hit 104 47896 2501 23808 640 8
Miss random 3 337 2 0 0 0
Miss asynch 0 1040 2 4 3 0
Noread 0 18 0 0 2230 0
Read request 3 1506 85 738 152 0
Synchronous 3 1375 4 4 3 0
Seq prefetch 0 97 81 734 149 0
List pref 0 8 0 0 0 0
Dyn prefetch 0 26 0 0 0 0
Delay(msec)
Synchronous 1.8 11.4 17.1 28.7 35.8 n/c
Seq pref n/c 38.1 25.8 38.6 12.4 n/c
List pref n/c 21.1 n/c n/c n/c n/c
Dyn pref n/c 20.2 n/c n/c n/c n/c
Read page 3 4662 2590 23360 590 0
Synchronous 3 1375 4 4 3 0
Seq prefetch 0 2857 2586 23356 587 0
List pref 0 83 0 0 0 0
Dyn prefetch 0 347 0 0 0 0
Upd/wrt page 0.6 26.3 0.3 n/c 2.1 n/c
Page/wrt req 1.3 6.4 1.5 n/c 20.4 n/c
Buf Update 5 11063 1 0 4573 0
Write request 6 66 2 0 108 0
Synchronous 3 35 1 0 0 0
Asynchr 3 31 1 0 108 0
Delay(msec)
Synchr 2.2 6.7 2.1 n/c n/c n/c
Asynchr 140.7 105.7 104.4 n/c 39.5 n/c
Write page 8 420 3 0 2200 0
Synchronous 3 35 1 0 0 0
Asynchr 5 385 2 0 2200 0
============== ========== ========== ========== ========== ========== ==========
1 DB2 BUFFER POOL ANALYZER (V5R4M0) - BUFFER POOL ACTIVITY REPORT PAGE: 1-11
ORDER: BPID-QPAGESET
TOP: 11 LEVEL: DETAIL
GROUP: N/P LOCATION: PMO2D721 DB2 VERSION: V11
MEMBER: N/P REQUESTED FROM: NOT SPECIFIED TO: NOT SPECIFIED
SUBSYSTEM: D721 INTERVAL FROM: 12/06/13 16:08:30 TO: 12/06/13 16:09:22
======= Detail Activity =======
********** TOTAL **********
BPID | GRAND
| TOTAL
-------------- ----------
BP Hit ratio(%)
System | 60.3
Application | 98.2
Read I/O 80.0
Getpage | 78596
Sequential | 34012
Random | 44332
Ridlist | 252
Hit | 74957
Miss random | 342
Miss asynch | 1049
Noread | 2248
Read request | 2484
Synchronous | 1389
Seq prefetch | 1061
List pref | 8
Dyn prefetch | 26
Delay(msec)
Synchronous | 11.5
Seq pref | 33.9
List pref | 21.1
Dyn pref | 20.2
Read page | 31205
Synchronous | 1389
Seq prefetch | 29386
List pref | 83
Dyn prefetch | 347
Upd/wrt page | 5.9
Page/wrt req | 14.5
Buf Update | 15642
Write request | 182
Synchronous | 39
Asynchr | 143
Delay(msec)
Synchr | 6.2
Asynchr | 56.4
Write page | 2631
Synchronous | 39
Asynchr | 2592
============== ==========
0Buffer pool activity report complete- Buffer pool hit ratios
- Primary indicator of buffer pool efficiency showing the number
of pages contained in the buffer pool compared to the number of pages
requested.
The highest possible hit ratio is 100%. This means that every page requested is always in the buffer pool. A low ratio indicates high read I/O.
To increase the buffer pool hit ratio, you can do one of the following:
- Run the REORG utility for table spaces or index spaces associated with the virtual buffer pool.
- Decrease the virtual pool sequential steal threshold (VPSEQT) to reserve more pages for random I/O.
- Increase the buffer pool size but be aware that the cost of paging can outweigh the benefit of I/O avoidance.
- Establish more separate buffer pools, for example, to isolate table spaces and index spaces according to their access characteristics.
- Buffer pool hit ratio (%) — System
- 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 in a prefetch Getpage request that are found in the buffer pool.
Usually, this value is low when prefetch is used. A high value indicates that applications perform a series of similar operations on the same data.
A negative value indicates that prefetched pages 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.
Compare the value in this field with the application hit ratio to determine the efficiency of prefetch operations.
- Buffer pool hit ratio (%) — Application
- 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.
The 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.
- Buffer pool hit ratio (%) — Read I/O
- The number of read I/O requests without physical I/O activity
(satisfied by the buffer pool), expressed as a percentage of all read
I/O requests (with and without physical I/O activity).
The hit ratio indicates the percentage of read I/O requests that were satisfied by the buffer pool without requiring I/O activities to a hard disk drive.
- Getpage
- The total number of Getpage requests. Getpage requests are divided
as follows:
- Getpage — Sequential
- The number of sequential Getpage requests because of prefetch operations.
- Getpage — Random
- The number of random Getpage requests, usually issued by applications.
- Getpage — Ridlist
- The number of record identifier (RID) list pages referenced.
- Getpage — Hit
- The number of Getpage requests for which pages are found in the buffer pool.
- Getpage — Miss random
- The number of random Getpage requests for which the page is not found in the buffer pool.
- Getpage — Miss asynch
- The number of Getpage Asynchronous requests for which the page is not found in the buffer pool.
- Getpage — Noread
- The number of Getpage requests for which the page is not found in the buffer pool and for which the request did not result in a read I/O operation. The page is a new page for INSERT.
- Read request
- The total number of read I/O requests (synchronous, sequential
prefetch, list sequential prefetch, dynamic prefetch) with at least
one page read from hard disk drive per request (content of Db2 field
QW0007NP - number of pages read - greater zero). If no page is read
from hard disk drive (all pages are found in the buffer pool), the
counter is not incremented. The total number of read I/O requests
is the result of the following types of read I/O requests:
- Read request — Synchronous
- The number of random, synchronous read I/O requests.
- Read request — Seq prefetch
- The number of sequential prefetch read I/O requests with at least
one page read from hard disk drive per request (content of Db2 field
QW0007NP - number of pages read - greater zero). If the prefetch results
in an I/O read, up to 32 pages can be read for SQL, up to 64 pages
can be read for utilities. If all pages to be prefetched are already
in the buffer pool, a request does not result in an I/O read.
Sequential prefetch reads a sequential set of pages. It allows CP and I/O operations to overlap. Db2 determines at BIND time whether sequential prefetch is used.
Sequential prefetch is generally used for a table space scan. It can also be used to read index pages in an index scan. For an index scan that accesses eight or more consecutive data pages, Db2 requests sequential prefetch at bind time. The index must have a cluster ratio of 80% or higher. You can use REORG and RUNSTATS, and rebind relevant SQL if you do not know whether the target was met previously.
- Read request — List pref
- The number of list sequential prefetch read I/O requests with at least one page read from hard
disk drive per request (content of Db2 field QW0007NP - number of pages read - greater zero).
List prefetch allows Db2 to access data pages efficiently even if the required data pages are not contiguous. It allows CP and I/O operations to overlap.
Db2 uses list prefetch to do the following tasks:- Always to access data by multiple index access.
- Always to access data from the inner table during a hybrid join.
- Usually with one single index that has a cluster ratio lower than 80%.
- Sometimes on one single index with a high cluster ratio. This increases the efficiency of sequential prefetch if the estimated amount of data to be accessed is too small.
- Never when the estimated number of RIDs to be processed would take more than 50% of the RID pool.
During execution time, list prefetch processing stops if more than 25% of the rows with a minimum of 4075 in the table must be accessed.
- Read request — Dyn prefetch
-
The number of dynamic prefetch requests with at least one page read from hard disk drive per request (content of Db2 field QW0007NP - number of pages read - greater zero). If the prefetch request results in an I/O read, up to 32 advancing pages can be read at a time.
Dynamic prefetch reads a sequential set of pages. It allows CP and I/O operations to overlap.
If Db2 does not choose prefetch at bind time, it can sometimes use it at execution time through sequential detection.
For information on when sequential detection is used and when dynamic prefetch is triggered, see the DB2 9 Administration Guide.
- Read request — Delay (msec)
- The average elapsed time between start and completion of:
- Read request — Delay (msec) — Synchronous
- A synchronous read I/O
- Read request — Delay (msec) — Sequential pref
- A sequential prefetch read request
- Read request — Delay — List pref
- A list prefetch read request
- Read request — Delay — Dynamic prefetch
- A dynamic prefetch read request
- Read page
- The total number of pages read from a hard disk drive.
- Read page — Synchronous
- The number of pages read from a hard disk drive for applications and utilities.
- Read page — Sequential prefetch
- The number of pages read from a hard disk drive for sequential prefetch requests.
- Read page — List pref
- The number of pages read from a hard disk drive for list prefetch requests.
- Read page — Dyn prefetch
- The number of pages read from a hard disk drive for dynamic prefetch requests.
- Upd/wrt page
- The number of buffer updates per page written from the buffer
pool to a hard disk drive.
A high value indicates a high level of efficiency because more updates are externalized per physical write cycle.
Buffer updates per pages written depends on the type of application. For example, a batch program that processes a table in skip sequential mode with a high row update frequency in a dedicated environment can achieve a high update efficiency. Usually, update efficiency is lower for transaction processing applications because these applications use more random page access.
The following factors can influence the number of updates per page:- The number of rows per page: A small PCTFREE value gathers more rows on the same page. This might, however, impact concurrency.
- The buffer pool size and the deferred write thresholds: Increase the size of the buffer pool or the deferred write thresholds DWQT and VDWQT. This lets Db2 accumulate page updates in the buffer pool. Db2 might thus capture more updates per page. The effect depends on the type of transaction. It is less significant if the buffer pool is used concurrently by multiple transactions that access random pages.
- Page/wrt 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 that is required
to prepare a data set for use. Use this field and the Upd/wrt page field to determine the efficiency of the buffer pool for write operations. The following factors impact the ratio of pages written per write I/O:
- The checkpoint frequency: At checkpoint time, I/Os write all updated pages on the deferred write queue to a hard disk drive. If this occurs too often, the deferred write queue does not grow large enough to achieve a high ratio of pages written per write I/O. The checkpoint frequency depends on the number of logs that are written between two consecutive checkpoints. This number is set at installation time.
- The frequency of active log switches: Db2 takes a system checkpoint when the active log is switched. Frequent active log switches cause a higher checkpoint frequency. This prevents the deferred write queue to grow to an optimum size.
- The buffer pool size and deferred write thresholds: The deferred write thresholds (VDWQT and DWQT) are a function of buffer pool size. If the buffer pool size decreases, these thresholds are reached more frequently and cause I/Os to write some of the pages on the deferred write queue to a hard disk drive more often. This prevents the deferred write queue from growing large enough to achieve a high ratio of pages written per write I/O.
- The number of data sets, and the spread of updated pages across them: The efficiency of write I/O also depends on the number of data sets associated with the buffer pool and spread of updated pages across them. Due to the way batch processing works, the ratio of pages written to write I/Os is expected to be higher than for transaction type workloads.
- Buf Update
- The number of times updates are requested against pages in the buffer pool.
- Write request
- The total number of write I/O operations that are made to a hard
disk drive. The total number of write I/O requests is the result of
the following types of write I/O requests:
- Write request — Synchronous
- The number of synchronous write I/O operations that are made to
a hard disk drive. Synchronous or immediate writes occur if one of
the following conditions apply:
- An immediate write threshold is reached.
- No deferred write engines are available.
- More than two checkpoints pass without a page being written. Note that this does not indicate a buffer shortage.
You should keep this value as small as possible. Synchronous writes occur if there are too many checkpoints, if the buffer pool is too small, or both.
- Write request — Asynchr
- The number of asynchronous write I/O operations made to a hard disk drive.
- Write request — Delay (msec)
- The average elapsed time between start and completion of:
- Write request — Delay (msec) — Synchronous
- a synchronous write I/O request.
- Write request — Delay (msec) — Asynchr
- an asynchronous write I/O request.
- Write page
- The total number of pages written to a hard disk drive.
- Write page — Synchronous
- The number of pages written synchronously to a hard disk drive.
- Write page — Asynchr
- The number of pages written to a hard disk drive by asynchronous write requests.