Buffer Pool Sort/Merge

This topic shows detailed information about Statistics - Buffer Pool Sort/Merge.

This block is only printed when the buffer pool is active. If more than one 4 KB or 32 KB buffer pool block is present, a summary block showing buffer pool totals is also printed. If the report contains both 4 KB and 32 KB buffer pool blocks, a block showing the totals for all buffer pools is printed.

BP0    SORT/MERGE            QUANTITY  /SECOND  /THREAD  /COMMIT
---------------------------  --------  -------  -------  -------
MAX WORKFILES CONCURR. USED      0.00      N/A      N/A      N/A
MERGE PASSES REQUESTED           0.00     0.00      N/C      N/C
MERGE PASS DEGRADED-LOW BUF      0.00     0.00      N/C      N/C
WORKFILE REQ.REJCTD-LOW BUF      0.00     0.00      N/C      N/C
WORKFILE REQ-ALL MERGE PASS      0.00     0.00      N/C      N/C
WORKFILE NOT CREATED-NO BUF      0.00     0.00      N/C      N/C
WORKFILE PRF NOT SCHEDULED       0.00     0.00      N/C      N/C

MAX WORKFILES CONCURR. 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.

Field Name: QBSTWFM

This is an exception field.

MERGE PASSES 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.

Field Name: QBSTWFR

MERGE PASS DEGRADED-LOW BUF

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.

Background and Tuning Information

The maximum number of work files allowed is calculated as follows:

• Buffers consumed = 2 * (work files already allocated)
• Buffers available = (sequential steal threshold * buffer pool size - buffers consumed)
• Maximum work files allowed = buffers available / (2 * 8)

The default for the sequential steal threshold is 0.8.

Ideally, the number in this field should be 0. Otherwise, it indicates a shortage of buffer pool space or that there are too many concurrent work files. For example, there could be a number of concurrently open cursors that require sorting. Consider increasing the buffer pool size using the ALTER BUFFERPOOL command.

Field Name: QBSTWFF

This is an exception field.

WORKFILE REQ.REJCTD-LOW BUF

The total number of work files that were rejected during all merge passes because of insufficient buffer resources.

Background and Tuning Information

This field and the degraded low buffers field determine the average number of work files that cannot be honored at each merge pass because of insufficient buffer pool space.

Ideally, the number in this field should be 0. Otherwise, it indicates a shortage of buffer pool space or that there are too many concurrent work files. For example, there could be a number of concurrently open cursors that require sorting. Consider increasing the size of the buffer pool using the ALTER BUFFERPOOL command.

Note that, when there are many concurrent sorts or large sorts, it is a good idea to dedicate a separate buffer pool for sort work files. This will greatly facilitate work-file performance tuning.

Field Name: QBSTWFD

This is an exception field.

WORKFILE REQ-ALL MERGE PASS

The total number of work files requested for all merge passes.

This field and the Merge Passes Requested field determine the average number of work files requested in a single merge pass.

For Db2 to perform an efficient prefetch for work files, each workfile 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.

Field Name: QBSTWFT

WORKFILE NOT CREATED-NO BUF

This field is only applicable if Db2 is running under MVS/XA ™.

The number of times a work file could not 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.

Background and Tuning Information

Ideally, this should be 0. Otherwise, it indicates a shortage of buffer pool space or that there are many concurrent work files. For example, there could be a number of open cursors that require sorting.

Generally, sorts are performed more efficiently with additional work files, but there are internal Db2 limits on the number of work files a transaction can have. It is possible that at run time a transaction cannot use as many work files as it had planned. You can control this by increasing the buffer pool size (ALTER BUFFERPOOL), or changing the transaction so it requires fewer concurrent work files.

Field Name: QBSTMAX

This is an exception field.

WORKFILE PRF NOT SCHEDULED

The number of times a sequential prefetch was not scheduled for a work file because the dynamic prefetch quantity is zero.

Background and Tuning Information

The work-file prefetch checks the dynamic prefetch quantity (normally 1 to 8 pages). When the quantity is zero, the value in this field is incremented. A high number in this field implies that the buffer pool is too small.

Ideally, the number in this field should be 0. Otherwise, it indicates a shortage of buffer pool space or that there are many concurrent work files. For example, there could be a number of concurrently open cursors that require sorting.

Consider increasing the size of the buffer pool or allocating a buffer pool specifically for DSNDB07 usage. This can be especially effective with high-use query systems whose reports make extensive use of sort activity.

Field Name: QBSTWKPD

This is an exception field.