Using reports to analyze trace data
The following steps show how summary reports and detail reports are used to identify objects of interest.
The reports are shown partially; certain issues are highlighted.
- Creating host reports and a buffer pool data file with batch job
BPOQBTCH (described in Specifying a JCL command stream):
Data is collected over 30 minutes, with short record format. The trace data file is used for activity reports and the bpd file generation. The trace data file and the bpd file are used in later steps for object placements and simulations on the client.
- Analyzing the activity reports:
- The summary report, ordered by
BPID-QPAGESETand sorted byASYNCPAGE, shows the buffer pool configuration (in the Buffer Pool Characteristics report section) and high-level activity (in the Buffer Pool Statistics report section):========= Buffer Pool Characteristics ========= BPID BP0 BP1 BP2 BP3 BP10 BP32K -------------------------- -------- -------- -------- -------- -------- -------- General Virtual pool size 1000 2000 297525 297525 3000 100 ======= Buffer Pool Statistics ======= BUFFER POOL ID BP0 BP1 BP2 BP3 BP10 ------------------------- ---------- ---------- ---------- ---------- ---------- Reached threshold Deferred write 0 0 0 0 0 Vertical deferred write 0 0 0 0 0 Data manager 0 0 0 0 0 System hit ratio 39.73 97.15 75.03 99.24 100.00 Application hit ratio 83.15 100.00 97.77 99.68 100.00 Getpage request 813 561 321601 665989 1018 Sequential 6 435 6026 41 56 Random 807 126 315575 665948 962 Read Sequential prefetch Pages read 49 16 2857 59 0 Dynamic prefetch Pages read 304 0 70262 2876 0 Write Page write 3 0 10785 1222 0The report shows:- The system hit ratio is low for buffer pool BP2. The number of pages that are written to disk is moderate.
- The applications are doing a lot of random Getpage operations in buffer pools BP2 and BP3.
- Many Getpage operations in buffer pool BP2 are converted to Dynamic prefetch operations.
- Dynamic prefetches are significant higher than Sequential prefetches, which implies that the application is causing unneeded prefetches. In this example, it would be advantageous if the applications could be modified to move away from the current random scanning of tables or indexes to obvious scanning. This would change the prefetch behavior from dynamic to sequential and allow the optimizer to plan prefetches in a much more efficient way.
- The detail report, ordered by
BPID-QPAGESETand sorted byBPIDandGETPAGE, shows the most active objects in the Detail Activity report section:======= Detail Activity ======= BPID BP2 BP2 BP2 BP2 BP2 BP2 QPAGESET WTNTEST WTNTEST WTNTEST WTNTEST WTNTEST WTNTEST WTNSEC WTNACT WTNSEC WTNSE2 WTNSE3 WTNSE1 -------------- ---------- ---------- ---------- ---------- ---------- ---------- BP Hit ratio(%) System 100.0 44.0 65.5 98.4 99.1 72.3 Application 100.0 98.4 95.0 99.8 99.9 90.8 Getpage 2037600 790940 761760 552562 496460 230257 Sequential 1358310 0 0 0 0 0 Random 679288 790940 761760 552562 496460 230257 Miss random 0 12462 37987 1085 552 21277 … BPID BP2 BP2 BP2 BP2 BP2 BP2 QPAGESET WTNTEST WTNTEST WTNTEST WTNTEST WTNTEST WTNTEST WTNMMMB WTNFRD WTNHLD WTNACT WTNBND WTCSE4 -------------- ---------- ---------- ---------- ---------- ---------- ---------- BP Hit ratio(%) System 97.7 38.1 37.8 49.5 64.4 65.1 Application 100.0 92.6 53.0 90.1 99.2 93.6 Getpage 152187 151998 76591 56897 48059 48048 Sequential 0 85459 0 0 0 0 Random 152187 66539 76591 56897 48059 48048 Miss random 34 11227 35964 5620 367 3085 ********** TOTAL ********** BPID BP0 BP1 BP2 BP3 BP10 | GRAND | TOTAL -------------- ---------- ---------- ---------- ---------- ---------- ---------- Getpage 6311 125821 5782260 5476585 14488 | 11405465 Sequential 56 63134 1490275 883 2670 | 1557018 Random 6255 62687 4291981 5475702 11818 | 9848443 Miss random 1810 0 175602 59389 0 | 236801The report shows:- A very high proportion of the total system activity is concentrated in buffer pool BP2 in only a few table spaces.
- An even larger proportion of the random misses is concentrated in a subset of these table spaces.
- The detail report, ordered by
BPID-QPAGESETand sorted byBPIDandREADSYNC, shows the mostexpensive
objects in terms of I/O:BPID BP2 BP2 BP2 BP2 BP2 BP2 QPAGESET WTNTEST WTNTEST WTNTEST WTNTEST WTNTEST WTNTEST WTNSEC WTNHLD WTNSE1 WTNRCK WTNACT WTNFRD -------------- ---------- ---------- ---------- ---------- ---------- ---------- Getpage 761760 76591 230257 31457 790940 151998 Sequential 0 0 0 0 0 85459 Random 761760 76591 230257 31457 790940 66539 Miss random 37987 35964 21277 14439 12462 11227 Read request 47920 36408 24113 14610 43143 14103 Synchronous 37984 35961 21274 14439 12458 11296 Dyn prefetch 9936 447 2839 171 30685 137 Delay(msec) Synchronous 10.8 34.9 25.8 4.2 148.7 6.5 Dyn pref 12.9 21.3 17.3 25.7 19.2 12.9 Read page 262868 47611 63832 18897 442639 94019 Synchronous 37984 35961 21274 14439 12458 11296 Dyn prefetch 224884 11650 42558 4458 430181 2691The report shows:- Again, a very large proportion of all disk accesses is concentrated in a relatively small number of objects.
- One object stands out by a different average delay time on synchronous read operations.
The conclusion so far is: It is worth simulating the effect of moving such objects into a buffer pool of their own.
The recommendation so far is: Objects with frequent misses should be moved to a faster disk, if possible.
- The summary report, ordered by