Group buffer pool size is too small

Pages in the group buffer pool need to be refreshed from disk more often because they are not in the group buffer pool.

When the group buffer pool is too small, the following problems can occur:

The thresholds for changed pages are reached more frequently, causing data to be cast out to disk more often.
If castout cannot keep up with the writes to the group buffer pool, a more serious problem occurs: pages are instead written to the logical page list and are unavailable until they are recovered.
Many cross-invalidations caused by reusing existing directory entries, which might require refreshing a page from disk later when the page is referenced again.

You can use the GDETAIL option of the DISPLAY GROUPBUFFERPOOL command to gather detailed statistical information about how often data is returned on a read request to the group buffer pool:

Begin general-use programming interface information.

-DB1A DISPLAY GROUPBUFFERPOOL(GBP0) GDETAIL(*)

The following figure shows output similar to the detail portion of the report output.

Figure 1. Example output of group detail statistics

DSNB783I -DB1A CUMULATIVE GROUP DETAIL STATISTICS SINCE 15:35:23 May 17, 2002
DSNB784I -DB1A GROUP DETAIL STATISTICS
                 READS
                   DATA RETURNED A                    = 3845
DSNB785I -DB1A     DATA NOT RETURNED
                     DIRECTORY ENTRY EXISTED B        = 27
                     DIRECTORY ENTRY CREATED C        = 28336
                     DIRECTORY ENTRY NOT CREATED D    = 332, 0
 
DSNB786I -DB1A   WRITES
                   CHANGED PAGES                      = 20909
                   CLEAN PAGES                        = 0
                   FAILED DUE TO LACK OF STORAGE E    = 8
                 CHANGED PAGES SNAPSHOT VALUE         = 974
DSNB787I -DB1A   RECLAIMS
                   FOR DIRECTORY ENTRIES F            = 18281
                   FOR DATA ENTRIES                   = 47
                 CASTOUTS                             = 16073
DSNB788I -DB1A   CROSS INVALIDATIONS
                   DUE TO DIRECTORY RECLAIMS G        = 4489
                   DUE TO WRITES                      = 3624
                   EXPLICIT                           = 0
DSNB762I -DB1A   DUPLEXING STATISTICS FOR GBP0-SEC
                   WRITES
                     CHANGED PAGES                    = 20909
                     FAILED DUE TO LACK OF STORAGE    = 8
                   CHANGED PAGES SNAPSHOT VALUE       = 974
DSNB790I -DB1A DISPLAY FOR GROUP BUFFER POOL GBP0 IS COMPLETE
DSN9022I -DB1A DSNB1CMD 'DISPLAY GROUPBUFFERPOOL' NORMAL COMPLETION

End general-use programming interface information.

What you need to determine is the read-hit percentage. To calculate this value, you need to determine how many of the total number of reads were successful in returning data. Use the following formula:

( A  / ( A  +  B  +  C  +  D (first number)))× 100

In this example, the calculation is:

(3845 / 32540) × 100 = 11.81%

Data was returned in approximately 12% of the read requests to the group buffer pool. This low percentage of read hits might indicate that the average residency time for a cached page in group buffer pool is too short. You might benefit from altering the group buffer pool to increase the total size.

However, a low percentage of read hits could be caused by other factors:

A high read-to-write ratio.
If you are caching only changed pages, not many pages you need will be resident in the group buffer pool.
Random reference patterns.
Pages that are frequently referenced are most likely to be resident in the group buffer pool. If the application keeps requesting new pages, any given page is unlikely to be found in the group buffer pool.

To determine whether the low read-hit percentage is a problem, see the field indicated by B in the statistics report, shown in the following figure. (The same counter also exists in the accounting report.) Ideally, that field contains 0. A non-zero value in the field, in conjunction with a low read hit percentage, can indicate that your group buffer pool is too small.

Figure 2. Portion of IBM® OMEGAMON® for Db2 Performance Expert on z/OS® statistics detail report showing GBP activity

GROUP BP30                      QUANTITY  /SECOND  /THREAD  /COMMIT
-----------------------------   --------  -------  -------  -------
GROUP BP R/W RATIO (%)             27.26      N/A      N/A      N/A
GBP-DEPENDENT GETPAGES            651.4M    45.4K   233.60   102.02
SYN.READ(XI)-DATA RETURNED   A   1091.6K    76.12     0.39     0.17
SYN.READ(XI)-NO DATA RETURN  B    133.0K     9.28     0.05     0.02
SYN.READ(NF)-DATA RETURNED   C   1062.1K    74.07     0.38     0.17
SYN.READ(NF)-NO DATA RETURN     14515.1K  1012.21     5.21     2.27
UNREGISTER PAGE              D   4323.7K   301.52     1.55     0.68

CLEAN PAGES SYNC.WRITTEN            0.00     0.00     0.00     0.00
CLEAN PAGES ASYNC.WRTN              0.00     0.00     0.00     0.00
REG.PAGE LIST (RPL) REQUEST      4025.4K   280.71     1.44     0.63
NUMBER OF PAGES RETR.FROM GBP     750.0K    52.30     0.27     0.12
PAGES CASTOUT                E   3033.5K   211.54     1.09     0.48
UNLOCK CASTOUT               F    115.7K     8.07     0.04     0.02

READ CASTOUT CLASS           G    105.4K     7.35     0.04     0.02
READ DIRECTORY INFO          H  69375.00     4.84     0.02     0.01
READ STORAGE STATISTICS      I   9237.00     0.64     0.00     0.00
REGISTER PAGE                J    307.2K    21.43     0.11     0.05
DELETE NAME                  K  24191.00     1.69     0.01     0.00
ASYNCH GBP REQUESTS          L   7532.5K   525.28     2.70     1.18

EXPLICIT X-INVALIDATIONS     M      0.00     0.00     0.00     0.00
CASTOUT CLASS THRESHOLD      N     78.00     0.01     0.00     0.00
GROUP BP CASTOUT THRESHOLD   O     24.00     0.00     0.00     0.00
GBP CHECKPOINTS TRIGGERED    P    100.00     0.01     0.00     0.00
WRITE FAILED-NO STORAGE      Q      0.00     0.00     0.00     0.00

WRITE TO SEC-GBP FAILED             0.00     0.00     0.00     0.00
DELETE NAME LIST SEC-GBP          115.6K     8.06     0.04     0.02
DELETE NAME FROM SEC-GBP         5718.00     0.40     0.00     0.00
UNLOCK CASTOUT STATS SEC-GBP R      0.00     0.00     0.00     0.00
ASYNCH SEC-GBP REQUESTS           599.00     0.04     0.00     0.00

WRITE AND REGISTER               4220.8K   294.34     1.51     0.66
WRITE AND REGISTER MULT          1350.3K    94.17     0.48     0.21
CHANGED PGS SYNC.WRTN        S   7901.0K   550.98     2.83     1.24
CHANGED PGS ASYNC.WRTN       T   2752.1K   191.92     0.99     0.43
PAGES WRITE & REG MULT           6432.4K   448.57     2.31     1.01
READ FOR CASTOUT                29352.00     2.05     0.01     0.00
READ FOR CASTOUT MULT             407.8K    28.44     0.15     0.06

PAGE P-LOCK LOCK REQ             2604.3K   181.61     0.93     0.41
 SPACE MAP PAGES                  844.2K    58.87     0.30     0.13
 DATA PAGES                      1760.1K   122.74     0.63     0.28
 INDEX LEAF PAGES                   0.00     0.00     0.00     0.00

PAGE P-LOCK UNLOCK REQ           2478.9K   172.87     0.89     0.39

PAGE P-LOCK LOCK SUSP           98270.00     6.85     0.04     0.02
 SPACE MAP PAGES                48419.00     3.38     0.02     0.01
 DATA PAGES                     49851.00     3.48     0.02     0.01
 INDEX LEAF PAGES                   0.00     0.00     0.00     0.00

PAGE P-LOCK LOCK NEG            63760.00     4.45     0.02     0.01
 SPACE MAP PAGES           U    45651.00     3.18     0.02     0.01
 DATA PAGES                V    18109.00     1.26     0.01     0.00
 INDEX LEAF PAGES          W        0.00     0.00     0.00     0.00